Where Are The Matching Cells? - Duel 194 MrExcel.com Russian Italian

Where Are The Matching Cells? - Duel 194YouTube↗ IA↗(long shot)
Howto & Style 1.4K views 91 likes 3 dislikes 04 Sep 2020

MrExcel.com 167K subscribers United States YouTube↗

Привет, с возвращением. Пришло время для еще одного подкаста Dueling Excel.

Ehi, bentornato. È tempo per un altro podcast Dueling Excel.

Я Билл Джелен из MrExcel. К нам присоединится Майк Гирвин из Excel is Fun.

Sono Bill Jelen di MrExcel. Saremo affiancati da Mike Girvin di Excel is Fun.

Это наша серия 194. Адрес ячейки совпадающих значений.

Questo è il nostro episodio 194. L'indirizzo della cella dei valori corrispondenti.

Сегодняшний вопрос от DW.

La domanda di oggi di DW.

Я хочу получить ссылку на ячейку, когда элемент в List02 находится в List01.

Voglio ottenere il riferimento di cella quando un elemento in List02 è in List01.

Итак, прямо здесь, когда мы смотрим на этот 59, он фактически находится в ячейке B10.

Quindi proprio qui, quando stiamo guardando questo 59, è effettivamente nella cella B10.

DW ищет, чтобы мы сказали ему, что это B10.

DW ci sta cercando per dirgli che è in B10.

Но ведь многие из них не встречаются.

Ma vedi che molti di loro non sono stati trovati.

Я думаю, что есть два простых способа получить адрес ячейки.

Il mio processo di pensiero su questo è che ci sono due semplici modi per ottenere un indirizzo di cella.

Либо функция ADDRESS, либо функция CELL с аргументом «адрес». Давайте посмотрим на них. АДРЕС.

O la funzione INDIRIZZO o la funzione CELLA con l'argomento "indirizzo". Diamo un'occhiata a quelli. C'è INDIRIZZO.

Это функция, которой я почти никогда не пользуюсь.

Questa è una funzione che non uso quasi mai.

Я не уверен, что я должен туда вставить.

Non sono sicuro di cosa dovrei mettere lì dentro.

Так что отличный трюк, который появился у MVP Набиля Мурада, а также у Деборы Далглиш за последние две недели.

Quindi un grande trucco che è spuntato dall'MVP Nabil Mourad e anche da Deborah 'Dalgleish nelle ultime due settimane.

Не нажимайте Ctrl + A, а нажимайте Ctrl + Shift + A.

Non premere Ctrl + A ma premere Ctrl + Maiusc + A.

Он вставит имена, чтобы мы могли это понять.

Inserirà i nomi in modo che possiamo capirlo.

Так что если бы я просто, например, поместил здесь H7 для номера строки. Затем столбец №1.

Quindi, se, ad esempio, inserissi solo H7 per il numero di riga. Quindi la colonna numero 1.

Тогда для «Абсолюта» нам нужны знаки доллара? Или знаки доллара только на ряду?

Quindi per Absolute, vogliamo i segni del dollaro? O i segni del dollaro solo sulla riga?

Или знаки доллара только на колонке? Или родственник? Я выберу 4 для относительных. Тогда нам нужен стиль А1?

O i segni del dollaro solo sulla colonna? O parente? Sceglierò 4 per Parente. Allora vogliamo lo stile A1?

Конечно, есть.

Certo che lo facciamo.

Так что это один.

Quindi questo è uno.

Для текста листа нам не нужно добавлять к нему префикс имени листа. Это успешно возвращает A1.

Per il testo del foglio, non è necessario aggiungere un prefisso al nome del foglio. Ciò restituisce correttamente A1.

Но тогда возникает большой вопрос: что произойдет, если мы вместо H7 скажем H7 #? Другими словами, весь массив SEQUENCE.

Ma poi, la grande domanda è cosa succederebbe se invece di H7 dicessimo H7 #? Quindi, in altre parole, l'intera gamma di SEQUENZA.

ADDRESS отлично работает с массивом.

ADDRESS funziona alla grande con un array.

Он возвращает все эти значения.

Restituisce tutti questi valori.

Другой способ сделать это - использовать функцию ЯЧЕЙКА.

Un altro modo per farlo è usare la funzione CELL.

В первом аргументе мы говорим, что нам нужен АДРЕС, а затем H7.

Nel primo argomento diciamo di volere l'INDIRIZZO e poi H7.

Но вопрос в том, когда мы введем H7 #, вернет ли он адреса ячеек из всех этих элементов?

Ma la domanda è quando inseriamo H7 # restituirà gli indirizzi delle celle da tutti quegli elementi?

И ответ нет. Таким образом, ADDRESS может работать с массивами.

E la risposta è no. Quindi ADDRESS può funzionare con gli array.

А CELL не может работать с массивами.

E CELL non può funzionare con gli array.

Я просто собираюсь совершить здесь побочное путешествие.

Sto solo andando a fare un viaggio laterale qui.

Когда я впервые начал думать об этом, я подумал: «О, это будет одно из тех замечательных применений, когда мы можем использовать XLOOKUP с двоеточием рядом с ним. XLOOKUP, если бы мы только что выполнили XLOOKUP из D9 в LIST01.

Quando ho iniziato a pensarci, ho pensato, "oh, questo sarà uno di quei grandi usi per quando possiamo usare XLOOKUP con i due punti accanto. XLOOKUP, se abbiamo appena fatto XLOOKUP di D9 in LIST01.

И возвращая List01.

E restituendo List01.

Мы получим 59, которые искали. Есть там D9, и он дает нам 59 там.

Otterremo il 59 che stavamo cercando. C'è il D9 e ci dà il 59 lì.

Но если бы мы поместили XLOOKUP (): XLOOKUP (), то удивительно, что эта формула не вернет 59.

Ma se mettessimo XLOOKUP (): XLOOKUP (), sorprendentemente quella formula non restituisce 59.

Возвращает B10. Мы можем увидеть это, если перейдем к формуле Evaluate Formula.

Restituisce B10. Possiamo vederlo se andiamo in Valuta formula.

Я нажму «Оценить», «Оценить», «Оценить».

Farò clic su Valuta, Valuta, Valuta.

Вот о том, где он собирается выполнить XLOOKUP.

Proprio qui si tratta di dove sta per fare XLOOKUP.

Вместо 59 он возвращает B10. И все это происходит из-за этой прямой кишки.

Invece di darci il 59 restituisce B10. E tutto questo accade a causa di questo colon proprio qui.

Итак, мы знаем, что в XLOOKUP мы можем искать все наши значения сразу.

Ok, quindi sappiamo che in XLOOKUP possiamo cercare tutti i nostri valori contemporaneamente.

Так что ищите все в D14: D15.

Quindi cerca tutto in D14: D15.

Но когда я пытаюсь объединить этот трюк и этот трюк. Говоря, я хочу найти два значения.

Ma quando provo a combinare questo trucco e questo trucco. Dicendo che voglio cercare due valori.

Затем двоеточие, найдите те же два значения. А затем передайте это в ЯЧЕЙКУ ("адрес".

Quindi due punti, cerca gli stessi due valori. E poi passalo al CELL ("address".

Это не работает. Вот почему? Если мы перейдем к формуле Evaluate Formula.

Non funziona. Ecco perché? Se entriamo in Evaluate Formula.

И переходим к тому моменту, когда мы собираемся оценить этот первый XLOOKUP.

E arriva al punto in cui sta per valutare il primo XLOOKUP.

Вот где должно быть написано B13, запятая B12.

Proprio qui, qui è dove dovrebbe dire B13, virgola B12.

Вместо этого написано 88 и 10.

Invece dice 88 e 10.

Это сбило мой первый путь.

Quello ha abbattuto la mia prima strada da percorrere.

Итак, вот реальное решение. Для меня это было три шага.

Quindi ecco la soluzione effettiva Per me, erano tre passaggi.

Чтобы получить это, первое, что я хочу сделать, это использовать XMATCH.

Per ottenerlo, la prima cosa che voglio fare è usare XMATCH.

Это новая функция для определения местоположения элемента в List01. Итак, небольшая простая функция.

È la nuova funzione per individuare dove si trova l'elemento in List01. Quindi una semplice piccola funzione.

Найдите все эти значения здесь, в list01.

Cerca tutti questi valori qui in list01.

И это все.

E questo è tutto.

Нам не нужно говорить, что это точное совпадение, потому что оно автоматическое.

Non è necessario dire che è una corrispondenza esatta perché è automatica.

Итак, первое: семь - это второй пункт в списке. 54 не найдено.

Quindi il primo: sette è il secondo elemento nell'elenco. 54 non è stato trovato.

59 - четвертый пункт в списке. 18 не найдено. Так что хорошо.

59 è il quarto elemento nell'elenco. 18 non è stato trovato. Quindi va bene.

теперь мы знаем, где находятся вещи. Хорошо, тогда функция АДРЕС.

ora sappiamo dove sono le cose. Va bene allora la funzione INDIRIZZO.

Функция АДРЕС говорит, какая строка и какой столбец?

La funzione INDIRIZZO dice Quale riga e Quale colonna?

Я знаю, что он всегда будет во второй колонке.

So che sarà sempre nella seconda colonna.

Так что для колонной части это было действительно просто.

Quindi per la parte della colonna, è stato davvero facile.

Я могу просто попросить КОЛОНКУ List01, и она вернет столбец 2.

Posso semplicemente chiedere la COLONNA di List01 e questo restituirà la colonna 2.

Однако, чтобы получить местоположение в какой строке. Я начал с ROW () списка List01.

Per ottenere la posizione fino a quale riga, però. Ho iniziato con ROW () di List01.

Поскольку это динамический массив, он дает мне все строки.

Poiché questo è un array dinamico, mi fornisce tutte le righe.

Так что я должен был сказать, что хочу ИНДЕКС этого. Запятая первая. Что дало мне всего 5.

Quindi ho dovuto dire che voglio l'INDICE di quello. Virgola uno. Che mi ha dato con successo solo 5.

Я вычитал 1, чтобы попасть в ячейку над ней.

Ho sottratto 1 per arrivare alla cella sopra quella.

А затем добавил в результаты XMATCH.

E poi aggiunto i risultati dell'XMATCH.

Наконец, здесь это 4 говорит о том, что нам нужна относительная ссылка.

Infine qui fuori questo 4 dice che vogliamo un riferimento relativo.

и эта одна формула возвращает все эти ответы, поэтому B6, B8, B6, B11, B10. Если бы мы что-то здесь изменили, мы возьмем 32 и сделаем 12.

e quella formula restituisce tutte queste risposte quindi B6, B8, B6, B11, B10. Se dovessimo cambiare qualcosa qui, prenderemo questo 32 e lo faremo essere 12.

Затем это меняется на B9.

Quindi cambia in B9.

Он работает, за исключением ошибок # N / A.

Funziona ad eccezione degli errori # N / A.

Так что, наконец, просто оберните все это в IFNA и отобразите "".

Quindi, finalmente, avvolgi l'intera cosa in IFNA e mostra "".

Итак, одна формула, использующая АДРЕС, чтобы вернуть все эти ответы.

Quindi un'unica formula che utilizza INDIRIZZO per restituire tutte quelle risposte.

Майк, давай посмотрим, что у тебя есть. [Майк Гирвин] Вау, эта формула разлива потрясающая, MrExcel!

Mike, vediamo cosa hai. [Mike Girvin] Wow, quella formula di fuoriuscita è incredibile, MrExcel!

У вас есть это немного, чтобы найти строку пять.

Hai quel po 'di tempo per cercare la quinta riga.

Затем вы вычитаете единицу, чтобы вернуться к четвертому ряду. А затем вычтите массив, сгенерированный XMATCH.

Quindi sottrai uno per tornare alla riga quattro. E poi sottrai l'array generato da XMATCH.

Все для создания правильных номеров строк для АДРЕСА.

Tutto per creare i numeri di riga versati corretti per ADDRESS.

Так что, думаю, мне лучше использовать формулу старой школы. Вместо АДРЕСА. Я воспользуюсь функцией ЯЧЕЙКА.

Quindi credo che farei meglio a fare una formula vecchia scuola. Invece di ADDRESS. Userò la funzione CELL.

Теперь, когда вы сравниваете два списка, вам подойдет функция ПОИСКПОЗ.

Ora, ogni volta che si confrontano due elenchi, la funzione MATCH è la strada da percorrere.

Мы ищем это в этом списке прямо здесь.

Lo stiamo cercando in questo elenco proprio qui.

Это определенное имя, поэтому оно автоматически блокируется.

È un nome definito, quindi viene bloccato automaticamente.

И мы ищем точное совпадение.

E stiamo cercando la corrispondenza esatta.

Итак, в отличие от XMATCH, мы должны указать третий аргумент и сказать «точное совпадение».

Quindi, a differenza di XMATCH, dobbiamo mettere il terzo argomento e dire fai corrispondenza esatta.

Это сообщит нам относительное положение, когда один из элементов находится в списке.

Questo ci dirà la posizione relativa quando uno degli elementi è nell'elenco.

Есть четвертая позиция. Или # N / A.

C'è la quarta posizione. Oppure # N / A.

Я собираюсь поместить это в ИНДЕКС, потому что я действительно хочу найти ссылку на ячейку.

Lo inserirò all'interno di INDICE perché voglio davvero cercare il riferimento di cella.

Так что ищем там. Запятая. И там номер строки.

Quindi stiamo guardando là. Virgola. E c'è il numero di riga.

Теперь, конечно, если вы введете его в ячейку, он вернет фактическое значение.

Ora, ovviamente, se lo inserisci nella cella, restituisce il valore effettivo.

Но нажмите F2 для редактирования.

Ma premi F2 per modificare.

Если вы поместите функцию ИНДЕКС в контекст ссылки на ячейку с помощью функции ячейки.

Se metti la funzione INDICE nel contesto di un riferimento di cella utilizzando la funzione di cella.

И мы запрашиваем «адрес» в первом аргументе, тогда INDEX будет знать, что нужно доставить адрес ячейки. Закрывающие круглые скобки.

E chiediamo l '"indirizzo" nel primo argomento, quindi INDICE saprà fornire l'indirizzo della cella. Chiudi parentesi.

А чтобы заполнить его выделенными ячейками, я использую Control + Enter. Вот и ссылка на нашу ячейку.

E per popolarlo attraverso le celle che sono evidenziate, uso Control + Invio. Lì abbiamo il nostro riferimento di cella.

Оооо, но это не похоже на АДРЕС, который дает нам этот классный аргумент, где мы можем поставить 4, чтобы получить относительную ссылку на ячейку.

Ohhhh, ma questo non è come l'INDIRIZZO che ci dà quell'argomento interessante in cui possiamo mettere un 4 per ottenere il riferimento di cella relativo.

Он всегда будет доставлен со знаками доллара. Итак, F2 редактировать.

Consegnerà sempre con i segni del dollaro. Quindi F2 da modificare.

Теперь используем ЗАМЕНА. Это текст. Запятая.

Adesso usiamo SUBSTITUTE. Questo è il testo. Virgola.

Старый текст, который мы ищем, - это знак доллара.

Il vecchio testo che stiamo cercando è un segno di dollaro.

И мы заменим его двойными кавычками. Что ничего не значит. Закрывающие круглые скобки.

E lo sostituiremo con virgolette doppie virgolette doppie. Il che non significa niente. Chiudi parentesi.

Control + Enter, чтобы заполнить его полностью вниз.

Control + Invio per popolarlo fino in fondo.

Я думаю, что IFNA появилась в 2013 или 2016 году. Итак, если вы используете более ранние версии.

Ora, penso che IFNA sia arrivato nel 2013 o nel 2016. Quindi, se sei nelle versioni precedenti.

Нам нужно будет запустить формулу ЕСЛИ.

Dovremo eseguire la formula IF.

Вот немного, что сравнивает два списка.

Questa è la piccola parte che confronta i due elenchi.

Я скопирую это и сразу после знака равенства скажу ЕСЛИ, а затем мы воспользуемся ISNA и вставим совпадение. Закрывающие круглые скобки. Есть логический тест.

Quindi lo copierò e subito dopo il segno di uguale, dirò SE e poi useremo ISNA e incolleremo la corrispondenza. Chiudi parentesi. C'è il test logico.

Если это ИСТИНА, то я хочу показать двойные кавычки.

Se è VERO, voglio mostrare virgolette doppie virgolette doppie.

Технически это текстовая строка нулевой длины.

Ora tecnicamente è una stringa di testo di lunghezza zero.

Которая ничего не покажет в ячейке. В противном случае будет выполнена формула.

Che non mostrerà nulla nella cella. Altrimenti eseguirà la formula.

Закрывающие круглые скобки. Ctrl + Enter. Дважды щелкните и отправьте его вниз.

Chiudi parentesi. Ctrl + Invio. Fare doppio clic e inviarlo.

Так что, если вы используете более старую версию, эта формула будет работать.

Quindi, se sei in una versione precedente, quella formula funzionerà.

Теперь посмотрим, сможем ли мы получить функцию LET.

Adesso vediamo se possiamo ottenere la funzione LET.

Это Microsoft 365. Итак, последняя версия.

Questo è Microsoft 365. Quindi l'ultima versione.

И прежде чем использовать LET, я собираюсь использовать XLOOKUP, чтобы попытаться найти ссылку.

E prima di usare LET, userò XLOOKUP per provare a cercare un riferimento.

Вот наша поисковая ценность. Запятая. Поисковый массив.

C'è il nostro valore di ricerca. Virgola. L'array di ricerca.

Comma. А с XLOOKUP я должен перечислить это дважды.

Comma. E con XLOOKUP, devo elencarlo due volte.

Поскольку есть только один столбец, и если я скопирую его, конечно, он предоставит значение.

Poiché c'è solo una colonna e se la copio, ovviamente fornisce il valore.

Но теперь, если я помещаю его в ROW, это контекст, в котором ожидается ссылка на ячейку.

Ma ora, se lo metto all'interno di ROW, questo è un contesto in cui è previsto un riferimento di cella.

И так же, как INDEX, XLOOKUP предоставляет ссылку.

E così come INDEX, XLOOKUP sta fornendo il riferimento.

И ROW может это увидеть и доставить номер строки. А за 59 это именно то, что нам нужно.

E ROW può vederlo e fornire il numero di riga. E per 59 è esattamente ciò di cui abbiamo bisogno.

Нажмите F2.

Premi F2.

Теперь я сделал это, чтобы посмотреть, будет ли работать XLOOKUP. Теперь займемся ДАВАЙ.

Ora l'ho fatto solo per vedere se XLOOKUP avrebbe funzionato. Ora facciamo LET.

Имя переменной, которую мы собираемся использовать, - Get. И тогда это будет XLOOKUP.

Il nome della variabile che useremo è Get. E poi quello sarà XLOOKUP.

И преимущество использования LET в том, что мы, конечно, будем использовать его пару раз.

E il vantaggio nell'usare LET è ovviamente che lo useremo un paio di volte.

LET определяет его как переменную и вычисляет только один раз. Запятая.

LET la definisce come una variabile e la calcola una sola volta. Virgola.

Наша формула будет IFNA (АДРЕС, а затем в первом аргументе ему нужна строка. И это переменная Get. Запятая.

La nostra formula sarà IFNA (ADDRESS e quindi nel primo argomento ha bisogno della riga. E questa è la variabile Get. Virgola.

А для столбца мы будем использовать COLUMN и переменную Get.

E per la colonna useremo COLUMN e la variabile Get.

Запятая и красивая четверка для относительной ссылки на ячейку.

Virgola e c'è quel bellissimo 4 per riferimento di cella relativo.

Запятая. Теперь для Value_If_NA ничего не покажем. Двойные кавычки двойные кавычки.

Virgola. Ora per Value_If_NA non mostreremo nulla. Virgolette doppie virgolette doppie.

Закрывающие круглые скобки. И вот наша формула.

Chiudi parentesi. E c'è la nostra formula.

У нас есть одна переменная. XLOOKUP. И наша формула.

Abbiamo una variabile. XLOOKUP. E la nostra formula.

У него есть этот единственный вход, поэтому он будет выдавать единственный выход.

Ha quel singolo input e quindi fornirà un singolo output.

Но поскольку у меня уже выделен весь столбец, я могу заполнить его с помощью Ctrl + Enter. Итак, супер старая школа и супер новая школа.

Ma poiché ho già evidenziato l'intera colonna, posso popolarla con Ctrl + Invio. Quindi super vecchia scuola e super nuova scuola.

За исключением того, что он не проливается. Ладно, скину обратно в MrExcel.

Tranne per il fatto che non si rovescia. Va bene, lo restituirò a MrExcel.

Ну разве это не круто? Два разных пути, Майк.

Beh, non è fantastico? Due modi diversi, Mike.

Один использует CELL, а другой - ADDRESS. Оба должны быть скопированы.

Uno che utilizza CELL e uno che utilizza ADDRESS. Entrambi devono essere copiati.

Но то, что я узнал там, я знал и забыл.

Ma la cosa che ho imparato lì la conoscevo e l'ho dimenticata.

Помимо двоеточия рядом с XLOOKUP, чтобы превратить его в ссылку, INDEX, ROW и COLUMN - все это инструменты для получения этой ссылки из XLOOKUP.

Oltre ad avere i due punti accanto a XLOOKUP per forzarlo in un riferimento, INDICE, RIGA e COLONNA sono tutti strumenti per estrarre quel riferimento da XLOOKUP.

Очень-очень здорово. Что ж, я хочу поблагодарить всех, что заглянули.

Molto molto bello. Beh, ehi, voglio ringraziare tutti per essere passati.

Увидимся на следующей неделе, чтобы посмотреть еще один подкаст Dueling Excel от MrExcel и ExcelisFun.

Ci vediamo la prossima settimana per un altro podcast Dueling Excel da MrExcel ed ExcelisFun.