АДРЕС - возвращает адрес ячейки в виде текста, на основании указанных номера строки и столбца. Так же можно указать имя листа и стиль ссылок(R1C1 или А1).(ADRESS) ВПР - Ищет значение в первом столбце массива и выдает значение из ячейки в найденной строке и указанном столбце. Читать подробнее про ВПР: Как найти значение в другой таблице или сила ВПР(VLOOKUP) ГПР - Ищет значение в первой строке массива и выдает значение из ячейки в найденном столбце и указанной строке. Более подробно можно ознакомиться в статье про(HLOOKUP) ВПР : Как найти значение в другой таблице или сила ВПР: Как искать значение по строке, а не столбцу?.(VLOOKUP)
Т.к. функции практически одинаковые - все нюансы у них тоже идентичны. Разница между ними лишь в том, что ВПР ищет сверху вниз, а ГПР - слева направо.ОБЛАСТИ - возвращает количество областей в указанном диапазоне. Областью считается прямоугольный непрерывный диапазон ячеек. Например, такая запись выдаст 3:(AREAS)
=ОБЛАСТИ(( A1:A10 ;C5:C13 ;E11:G11 ))
=AREAS((A1:A10,C5:C13,E11:G11))
Обращаю внимание, что если указывается несколько диапазонов, то они должны быть дополнительно заключены в скобки, иначе функция вернет ошибку.ВЫБОР - возвращает значение из заданного списка значений по индексу. Первым аргументом указывается индекс(целое число), а далее идет перечисление значений по порядку. Например, для определения имени месяца по его номеру, записанному в ячейке, можно использовать такую формулу:(CHOOSE)
=ВЫБОР( A1 ;"Январь"; "Февраль"; "Март"; "Апрель"; "Май"; "Июнь"; "Июль"; "Август"; "Сентябрь"; "Октябрь"; "Ноябрь"; "Декабрь")
=CHOOSE(A1,"Январь", "Февраль", "Март", "Апрель", "Май", "Июнь", "Июль", "Август", "Сентябрь", "Октябрь", "Ноябрь", "Декабрь") ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ - Возвращает данные, хранящиеся в сводной таблице на основании ссылки на сводную таблицу и указания полей сводной, из которых это значение необходимо получить:(GETPIVOTDATA)
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Продажа"; $A$3 ;"Группа";"Бакалея")
=GETPIVOTDATA("Продажа",$A$3,"Группа","Бакалея")
Где:"Продажа" - наименование поля из области значений. Именно в этом поле располагается числовое значение для получения$A$3 "Группа" - наименование поля, которое помещено в область строк"Бакалея" - конкретное значение из указанного поля "Группа", сумму для которого необходимо извлечь
Функция применяется исключительно для полей значений(те поля, в которых применяется одна из функций агрегирования: сумма, количество, среднее и т.д.).
Если в текущем представлении сводной таблицы указанное поле отсутствует(поле есть в наборе данных, но не помещено в отчет или скрыто фильтром) - функция вернет ошибку#ССЫЛКА! . Например, применительно к приведенной выше функции, если скрыть фильтром строку со значением "Бакалея" - функция вернет ошибку, т.к. текущее представление сводной таблицы не отображает строку со значением "Бакалея".(#REF!) ГИПЕРССЫЛКА - Создает активную ссылку на файл или папку(в том числе сетевые), а так же на страницу в Интернет(читать подробнее).(HYPERLINK) ИНДЕКС - возвращает значение из диапазона, на пересечении указанных строки и столбца. Чаще всего применяется в связке с функцией(INDEX) ПОИСКПОЗ вместо ВПР, когда искомое значение находится правее столбца возвращаемых значений. Подробнее можно ознакомиться в статье: Как найти значение в другой таблице или сила ВПР: решение при помощи ПОИСКПОЗ)(MATCH) ДВССЫЛ - создает реальную ссылку на ячейку или диапазон ячеек из текстового значения. Может пригодиться, когда на основании значения ячейки надо сделать ссылку. Например, если в столбце А записаны имена листов книги, а в столбце В надо получить ссылки на ячейку F5 каждого указанного листа:(INDIRECT)
=ДВССЫЛ("'"& A1 &"'!F5")
=ДВССЫЛ("'"&A1&"'!F5") ПРОСМОТР - Ищет заданное значение в строке или столбце(LOOKUP) ПОИСКПОЗ - Ищет значение в столбце или в строке. Как правило применяется совместно с другими функциями, вроде(MATCH) ИНДЕКС, ВПР, ГПР . Подробнее можно узнать из статьи про ВПР: Как найти значение в другой таблице или сила ВПР: решение при помощи ПОИСКПОЗ)СМЕЩ - Возвращает ссылку на заданную ячейку или диапазон ячеек, смещенную на указанное количество строк и столбцов. Так же можно задать новый размер возвращаемой ссылки: задать новое количество строк и столбцов. Т.е. из ячейки(OFFSET) A1 C1:D10
=СМЕЩ( A1 ;0;2;10;2)
=OFFSET(A1,0,2,10,2)
Чаще всего применяется в составе других формул или для создания Динамических именованных диапазонов.СТОЛБЕЦ - возвращает номер столбца для указанной ячейки. Если указан диапазон ячеек - функция вернет номер столбца верхней левой ячейки.(COLUMN) СТРОКА - возвращает номер строки для указанной ячейки. Если указан диапазон ячеек - функция вернет номер строки верхней левой ячейки.(ROW) ЧИСЛСТОЛБ - возвращает количество столбцов в указанном диапазоне ячеек. В основном применяется в составе других функций, в которых необходимо подсчитать общее количество столбцов. Например, для получения последнего столбца в функции(COLUMNS) ВПР , если столбцов много:(VLOOKUP)
=ВПР( $A2 ;Лист1!$A$1:$C$4 ;ЧИСЛСТОЛБ($A$1:$C$4 );0)
=VLOOKUP($A2,Лист1!$A$1:$C$4,COLUMNS($A$1:$C$4),0) ЧСТРОК - возвращает количество строк в указанном диапазоне ячеек. Как и(ROWS) ЧИСЛСТОЛБ чаще всего применяется в составе других функций.(COLUMNS) ДРВ - Извлекает данные реального времени из программ, поддерживающих автоматизацию COM.(RTD) ТРАНСП - "переворачивает" указанную таблицу, делая строки столбцами, а столбцы строками. Иначе говоря - транспонирует. На примере диапазона(TRANSPOSE) A1:C10 - выделяем диапазон ячеек(
D1:M3 A1:C10 - вписываем функцию и в качестве аргумента указываем ссылку на исходный диапазон значений:
=ТРАНСП( A1:C10 ) - завершаем ввод функции сочетанием клавиш
Ctrl +Shift +Enter . Это обязательное условие для версий Excel до 2021(и без подписки офис 365)
В диапазоне
D1:M3 A1:C10 D1:M3 A1:C10 #Н/Д .(#N/A) - выделяем диапазон ячеек(
