Darbe.ru

Быт техника Дарби
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

ФУНКЦИЯ ПРОСМОТР В EXCEL НА ПРОСТОМ ПРИМЕРЕ

ФУНКЦИЯ ПРОСМОТР В EXCEL НА ПРОСТОМ ПРИМЕРЕ

В этом уроке мы познакомимся с функцией ПРОСМОТР, которая позволяет извлекать нужную информацию из электронных таблиц Excel. На самом деле, Excel располагает несколькими функциями по поиску информации в книге, и каждая из них имеет свои преимущества и недостатки. Далее Вы узнаете в каких случаях следует применять именно функцию ПРОСМОТР, рассмотрите несколько примеров, а также познакомитесь с ее вариантами записи.

ВАРИАНТЫ ЗАПИСИ ФУНКЦИИ ПРОСМОТР

Начнем с того, что функция ПРОСМОТР имеет две формы записи: векторная и массив. Вводя функцию на рабочий лист, Excel напоминает Вам об этом следующим образом:

ФОРМА МАССИВА

Форма массива очень похожа на функции ВПР и ГПР. Основная разница в том, что ГПР ищет значение в первой строке диапазона, ВПР в первом столбце, а функция ПРОСМОТР либо в первом столбце, либо в первой строке, в зависимости от размерности массива. Есть и другие отличия, но они менее существенны.

Данную форму записи мы подробно разбирать не будем, поскольку она давно устарела и оставлена в Excel только для совместимости с ранними версиями программы. Вместо нее рекомендуется использовать функции ВПР илиГПР.

ВЕКТОРНАЯ ФОРМА

Функция ПРОСМОТР (в векторной форме) просматривает диапазон, который состоит из одной строки или одного столбца. Находит в нем заданное значение и возвращает результат из соответствующей ячейки второго диапазона, который также состоит из одной строки или столбца.

Вот это да! Это ж надо такое понаписать… Чтобы стало понятней, рассмотрим небольшой пример.

ПРИМЕР 1

На рисунке ниже представлена таблица, где указаны номера телефонов и фамилии сотрудников. Наша задача по фамилии сотрудника определить его номер телефона.

В данном примере функцию ВПР не применить, поскольку просматриваемый столбец не является крайним левым. Именно в таких случаях можно использовать функцию ПРОСМОТР. Формула будет выглядеть следующим образом:

Первым аргументом функции ПРОСМОТР является ячейка C1, где мы указываем искомое значение, т.е. фамилию. Диапазон B1:B7 является просматриваемым, его еще называют просматриваемый вектор. Из соответствующей ячейки диапазона A1:A7 функция ПРОСМОТР возвращает результат, такой диапазон также называют вектором результатов. Нажав Enter, убеждаемся, что все верно.

ПРИМЕР 2

Функцию ПРОСМОТР в Excel удобно использовать, когда векторы просмотра и результатов относятся к разным таблицам, располагаются в отдаленных частях листа или же вовсе на разных листах. Самое главное, чтобы оба вектора имели одинаковую размерность.

На рисунке ниже Вы можете увидеть один из таких примеров:

Как видите, диапазоны смещены друг относительно друга, как по вертикали, так и по горизонтали, но формула все равно вернет правильный результат. Главное, чтобы размерность векторов совпадала. Нажав Enter, мы получим требуемый результат:

При использовании функции ПРОСМОТР в Excel значения в просматриваемом векторе должны быть отсортированы в порядке возрастания, иначе она может вернуть неверный результат.

ФУНКЦИИ ИНДЕКС И ПОИСКПОЗ В EXCEL НА ПРОСТЫХ ПРИМЕРАХ

Совместное использование функций ИНДЕКС и ПОИСКПОЗ в Excel – хорошая альтернатива ВПР, ГПР и ПРОСМОТР. Эта связка универсальна и обладает всеми возможностями этих функций. А в некоторых случаях, например, при двумерном поиске данных на листе, окажется просто незаменимой. В данном уроке мы последовательно разберем функции ПОИСКПОЗ и ИНДЕКС, а затем рассмотрим пример их совместного использования в Excel.

Более подробно о функциях ВПР и ПРОСМОТР.

ФУНКЦИЯ ПОИСКПОЗ В EXCEL

Функция ПОИСКПОЗ возвращает относительное расположение ячейки в заданном диапазоне Excel, содержимое которой соответствует искомому значению. Т.е. данная функция возвращает не само содержимое, а его местоположение в массиве данных.

Например, на рисунке ниже формула вернет число 5, поскольку имя "Дарья" находится в пятой строке диапазона A1:A9.

В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.

Из приведенных примеров видно, что первым аргументом функции ПОИСКПОЗ является искомое значение. Вторым аргументом выступает диапазон, который содержит искомое значение. Также функция имеет еще и третий аргумент, который задает тип сопоставления. Он может принимать один из трех вариантов:

· — функция ПОИСКПОЗ ищет первое значение в точности равное заданному. Сортировка не требуется.

· 1 или вовсе опущено — функция ПОИСКПОЗ ищет самое большое значение, которое меньше или равно заданному. Требуется сортировка в порядке возрастания.

· -1 — функция ПОИСКПОЗ ищет самое маленькое значение, которое больше или равно заданному. Требуется сортировка в порядке убывания.

В одиночку функция ПОИСКПОЗ, как правило, не представляет особой ценности, поэтому в Excel ее очень часто используют вместе с функцией ИНДЕКС.

ФУНКЦИЯ ИНДЕКС В EXCEL

Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.

Стоит отметить, что номера строк и столбцов задаются относительно верхней левой ячейки диапазона. Например, если ту же таблицу расположить в другом диапазоне, то формула вернет тот же результат:

Если массив содержит только одну строку или один столбец, т.е. является вектором, то второй аргумент функцииИНДЕКС указывает номер значения в этом векторе. При этом третий аргумент указывать необязательно.

Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):

Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):

СОВМЕСТНОЕ ИСПОЛЬЗОВАНИЕ ПОИСКПОЗ И ИНДЕКС В EXCEL

Если Вы уже работали с функциями ВПР, ГПР и ПРОСМОТР в Excel, то должны знать, что они осуществляют поиск только в одномерном массиве. Но иногда приходится сталкиваться с двумерным поиском, когда соответствия требуется искать сразу по двум параметрам. Именно в таких случаях связка ПОИСКПОЗ и ИНДЕКС в Excel оказывается просто незаменимой.

Читайте так же:
График остатков в excel

На рисунке ниже представлена таблица, которая содержит месячные объемы продаж каждого из четырех видов товара. Наша задача, указав требуемый месяц и тип товара, получить объем продаж.

Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 — тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter:

=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))

Как видите, мы получили верный результат. Если поменять месяц и тип товара, формула снова вернет правильный результат:

В данной формуле функция ИНДЕКС принимает все 3 аргумента:

1. Первый аргумент – это диапазон B2:E13, в котором мы осуществляем поиск.

2. Вторым аргументом функции ИНДЕКС является номер строки. Номер мы получаем с помощью функцииПОИСКПОЗ(C15;A2:A13;0). Для наглядности вычислим, что же возвращает нам данная формула:

3. Третьим аргументом функции ИНДЕКС является номер столбца. Этот номер мы получаем с помощью функцииПОИСКПОЗ(C16;B1:E1;0). Для наглядности вычислим и это значение:

Если подставить в исходную громоздкую формулу вместо функций ПОИСКПОЗ уже вычисленные данные из ячеек D15 и D16, то формула преобразится в более компактный и понятный вид:

=ИНДЕКС(B2:E13;D15;D16)

Дата добавления: 2018-02-15 ; просмотров: 2366 ; Мы поможем в написании вашей работы!

Использование индекс и поискпоз в excel

Excel - просто! Шукаємо значення на перетині

В данной статье я Вам расскажу о трёх способах осуществления этой задачи.

1. ИНДЕКС (INDEX)

Данная функция позволяет извлечь значение из диапазона ячеек по номеру строки и столбца.

Аргументы функции выглядят следующим образом:

Excel – просто. Ищем значение на пересечении

Массив – ссылка на диапазон ячеек, в котором нам нужно осуществить поиск;

Номер_строки – строка, из которой нужно извлечь значения;

Номер_столбца – столбец, из которого нужно извлечь значение.

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

Схематически работу функции можно изобразить так:

Excel – просто. Ищем значение на пересечении

Для автоматического поиска строки/столбца функцию ИНДЕКС, как правило, дополняют двумя функциями ПОИСКПОЗ (MATCH).

В таком случае формула выглядит примерно так:

=ИНДЕКС($C$7:$F$9;ПОИСКПОЗ($I$5;$B$7:$B$9;0);ПОИСКПОЗ($I$6;$C$6:$F$6;0))

Excel – просто. Ищем значение на пересечении

При смене параметров поиска, значение пересекающейся ячейки будет изменяться автоматически.

2. СУММПРОИЗВ (SUMPRODUCT)

Очень интересная функция, которой можно найти много применений. Она перемножает соответствующие элементы заданных массивов и возвращает сумму произведений.

Excel – просто. Ищем значение на пересечении

Для решения нашей задачи формула будет выглядеть так:

=СУММПРОИЗВ((C6:F6=I6)*(B7:B9=I5)*(C7:F9))

Простыми словами синтаксис функции можно представить так:

=СУММПРОИЗВ((условие_1)*(условие_2)*(что_суммировать))

Excel – просто. Ищем значение на пересечении

Сумма умножений 0, 1 и значений таблицы извлечёт нам искомое значение.

3. ДВССЫЛ (INDIRECT)

Третий способ, который лично мне очень нравится своей простотой. Единственное условие для его работы – названия в столбцах и строках должны быть без пробелов (их можно удалить или заменить на нижнее подчеркивание «_»).

Функция ДВССЫЛ возвращает ссылку, заданную текстовой строкой. Ссылки немедленно вычисляются для вывода их содержимого. Функция ДВССЫЛ используется, если требуется изменить ссылку на ячейку в формуле без изменения самой формулы.

Excel – просто. Ищем значение на пересечении

Если на первый взгляд функция выглядит простой и малополезной, то при дальнейшем её изучении Вы узнаете, что с её помощью мы можем перемещаться по листам, транспонировать таблицы, отбирать чётные (нечётные) строки и многое другое. Ведь для нее аргумент Ссылка_на_ячейку – всего лишь текстовая строка, которую можно изменять формулами.

Чтобы найти значение ячейки на пересечении определенных сроки и столбца нам понадобится сразу две функции ДВССЫЛ.

Для начала нам необходимо создать диапазоны для строк и столбцов.

Выделяем столбцы с заголовками и нажимаем комбинацию клавиш Ctrl+Shift+F3

Excel – просто. Ищем значение на пересечении

Аналогичную операцию проделываем и для строк.

Excel – просто. Ищем значение на пересечении

Сама формула будет иметь такой вид:

=ДВССЫЛ(I5) ДВССЫЛ(I6)

Обратите внимание что функции разделены пробелом.

Excel – просто. Ищем значение на пересечении

Excel даёт нам возможность использовать несколько вариантов решения для одной задачи. Там, где невозможно применить один способ, практически всегда есть возможность подобрать другой. Настоятельно рекомендую Вам изучить функции, рассмотренные в данной статье и Ваша работа с данными, станет намного эффективнее.

5 основных функции для работы с массивами

Массив – данные, объединенные в группу. В данном случае группой является массив функций в Excel. Любую таблицу, которую мы составим и заполним в Excel, можно назвать массивом. Пример:

В зависимости от расположения элементов различают массивы:

  • одномерные (данные находятся в ОДНОЙ строке или в ОДНОМ столбце);
  • двумерные (НЕСКОЛЬКО строк и столбцов, матрица).

Одномерные массивы бывают:

  • горизонтальными (данные – в строке);
  • вертикальными (данные – в столбце).

Примечание. Двумерные массивы Excel могут занимать сразу несколько листов (это сотни и тысячи данных).

Формула массива – позволяет обработать данные из этого массива. Она может возвращать одно значение либо давать в результате массив (набор) значений.

С помощью формул массива реально:

  • подсчитать количество знаков в определенном диапазоне;
  • суммировать только те числа, которые соответствуют заданному условию;
  • суммировать все n-ные значения в определенном диапазоне.

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

Классика жанра – товарный чек

Задача: рассчитать общую сумму заказа. Если идти классическим путем, то нужно будет добавить столбец, где перемножить цену и количество, а потом взять сумму по этому столбцу. Если же применить формулу массива, то все будет гораздо красивее:

  1. выделяем ячейку С7
  2. вводим с клавиатуры =СУММ(
  3. выделяем диапазон B2:B5
  4. вводим знак умножения (звездочка)
  5. выделяем диапазон C2:C5 и закрываем скобку функции СУММ – в итоге должно получиться так:
  6. чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter
Читайте так же:
Как восстановить последний сеанс хром

Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

Обратите внимание на фигурные скобки, появившиеся в формуле – отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно – они автоматически появляются при нажатии Ctrl + Shift + Enter.

Разрешите Вас… транспонировать?

При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.

Допустим, имеем двумерный массив ячеек, который хотим транспонировать.

  • Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
  • вводим функцию транспонирования =ТРАНСП(
  • в качестве аргумента функции выделяем наш массив ячеек A1:B8

жмем Ctrl + Shift + Enter и получаем “перевернутый массив” в качестве результата:

Функция СТРОКА

Определяет и возвращает номер строки указанной ссылкой ячейки.

Синтаксис: =СТРОКА([ссылка]), где аргумент «ссылка» не является обязательным. Если он опущен, но возвращается номер текущей строки.

=СТРОКА(D4) – результат 4.
=СТРОКА() – функция вернет номер строки, в которой она расположена.

Функция СТОЛБЕЦ

Возвращает номер столбца ячейки, указанной ссылкой.

Синтаксис: =СТОЛБЕЦ([ссылка]), где «ссылка» не обязательный аргумент. По умолчанию возвращается номер текущего столбца.

=СТОЛБЕЦ(C4) – формула вернет значение 3.
=СТОЛБЕЦ() – функция возвращает номер столбца, в котором расположена.

Функция АДРЕС

Возвращает текст, представляющий адрес ячейки, заданной номерами строки и столбца.

Синтаксис: =АДРЕС(строка; столбец; [тип_закрепления]; [стиль_ссылки]; [имя_листа]), где:

  • Строка – обязательный аргумент. Число, представляющая номер строки, для которой необходимо вернуть адрес;
  • Столбец – обязательный аргумент. Число, представляющее номер столбца целевой ячейки.
  • тип_закрепления – необязательный аргумент. Число от 1 до 4, обозначающее закрепление индексов ссылки:
    • 1 – значение по умолчанию, когда закреплены все индексы;
    • 2 – закрепление индекса строки;
    • 3 – закрепление индекса столбца;
    • 4 – адрес без закреплений.
    • ИСТИНА – формат ссылок «A1»;
    • ЛОЖЬ – формат ссылок «R1C1».

    =АДРЕС =АДРЕС =АДРЕС =АДРЕС

    Функция ДВССЫЛ

    Преобразует адрес ссылки, заданный текстовой строкой, в ссылку на данный адрес.

    Синтаксис: =ДВССЫЛ(адрес_ссылки; [стиль_ссылки]), где

    • адрес_ссылки – обязательный аргумент. Строка, представляющая адрес ссылки на ячейку или диапазон. Например, “C3”, “R3C3” или “D8:D9”.
    • стиль_ссылки – необязательный аргумент. Логическое значение, определяющее стиль ссылки:
      • ИСТИНА – стиль A1. Является значением по умолчанию;
      • ЛОЖЬ – стиль R1C1.

      =ДВССЫЛ(“a3”) – возвращает ссылку на ячейку A3.
      =ДВССЫЛ(“r3c3”) – вернет ошибку #ССЫЛКА!, так как текст для ссылки в формате R1C1, а второй аргумент имеет значение по умолчанию.
      =ДВССЫЛ(“r3c3”; ЛОЖЬ) – возвращает ссылку на ячейку C3.
      =ДВССЫЛ(АДРЕС(СТРОКА(C3);СТОЛБЕЦ(C3))) – функция вернет аналогичный предыдущему примеру результат.
      Вложение функции ДВССЫЛ со ссылкой на диапазон:

      Функция ВЫБОР (CHOOSE)

      Позволит вам выбрать значение из общего списка по указанному номеру позиции:

      =ВЫБОР(2;”Стул”;”Стол”;”Шкаф”;”Диван”)

      Функция ИНДЕКС (INDEX)

      Эта функция возвращает указанное значение из одно- или двумерного диапазона:

      =ИНДЕКС(A1:C6;4;3)

      Как видно с примера, полученное значение 37, в указанном диапазоне стоит на пересечении строки №4 и столбика №3 в диапазоне A1:C6 указанном в формуле. В более простом примере показано как в диапазоне С1:С6, на 2 месте находится значение 15:

      =ИНДЕКС(С1:С6;2)

      Функция ПОИСКПОЗ (MATCH)

      Эта функция вернет позицию значения, которое вы будете искать в указанном диапазоне:

      =ПОИСКПОЗ(B3;B2:B5;0)

      С примера вы можете видеть что слово «Стол» занимает 2 позицию в указанном диапазоне. Замечу, что третий аргумент в функции не является обязательным. При введенном значении 0, функция вернет ту позицию элемента массива, которое точно совпадает со значением, которое мы ищем. В случае, когда точное совпадение отсутствует, функция выдаст ошибку #Н/Д (#N/A) .

      Редактирование формулы массива

      Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.

      Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

      Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)

      Описание категории «Ссылки и массивы»

      Категория функций «Ссылки и массивы», другое название категории «Функции просмотра» используются для поиска значений в списках или таблицах, манипулирования диапазоном данных, а также, для получения дополнительной информации, например, информации о количестве строк или столбцов диапазона.

      Ярким представителем функций данной категории является самая популярная функция ВПР, которая позволяет найти значение из ячейки диапазона, выполнив поиск в первом столбце этого же диапазона

      Принцип работы функции ВПР из категории «Ссылки и массивы»

      Функция ВПР (VLOOKUP) ищет значение в крайнем левом столбце таблицы и возвращает значение ячейки, находящейся в указанном столбце той же строки.

        Вставляем функцию ВПР:

      • Функция ВПР ищет значение ID (104) в крайнем левом столбце диапазона $E$4:$G$7 и возвращает значение из третьего столбца той же строки (так как третий аргумент функции имеет значение 3).
      • Четвёртый аргумент функции равен ЛОЖЬ (FALSE) – это значит, что либо будет найдено точное совпадение, либо будет показано сообщение об ошибке #Н/Д (#N/A).

      Пример применения формулы массива

      Наиболее простой задачей, которой можно проиллюстрировать мощь формулы массива, является простой товарный чек. То есть у нас есть наименование товаров, их количество и цена за единицу. На выходе мы должны получить общую сумму покупки.

      Как бы мы решали данную задачу стандартным образом?

      Во-первых, мы бы получили итоговую сумму по каждому товару, перемножив количество товара на его цену.

      Ну а затем просуммировали бы получение суммы, для получения итоговой. То есть нам бы потребовался дополнительный столбец для промежуточных вычислений.

      Ту же самую задачу можно решить с помощью простейшей формулы массива.

      Нам нужно получить сумму, поэтому воспользуемся соответствующей функцией СУММ . А вот суммировать мы должны произведение цены товара на его количество, что мы и сделаем – выбираем диапазон значений из столица B и умножаем его на аналогичный диапазон значений столбца C.

      Если сейчас нажать Enter , то появится ошибка.

      Так как мы в формуле использовали диапазоны (массивы) данных, то и формула должна быть формулой массива. Для этого нужно нажать сочетание клавиш Ctrl + Shift + Enter и получим результат.

      Что произошло и как работает формула массива? Это важно понять, так как в дальнейшем можно будет применять формулы массива для решения намного более заковыристых задач…

      Итак, при вычислении формулы массива Excel произвел попарное умножение значений диапазонов B2:B5 и C2:C5. В результате получился массив значений который был просуммирован соответствующей функцией. Мы получили только одно значение итоговой суммы без вспомогательных вычислений.

      Обратите внимание на формулу в строке формул. Она заключена в фигурные скобки, которые указывают нам, что мы имеем дело с формулой массива.

      Это не текстовые скобки, то есть нельзя их ввести с клавиатуры, чтобы сделать формулу формулой массива. Они появляются автоматически при нажатии сочетания клавиш Ctrl + Shift + Enter . Если после создания формулы массива вам необходимо ее отредактировать, то в конце необходимо вновь нажать сочетание клавиш, а не просто клавишу Enter .

      Похожим образом работает и функция ГПР (HLOOKUP):

      Функции для работы со ссылками и массивами

      В Excel имеется ряд функций для обработки ссылок и массивов: вычисление номеров строки или столбца таблицы по имени ссылки, определение количества столбцов (строк) ссылки или массива, выбор значения по номеру индекса и т. д.

      Таблица 4.10. Функции для работы со ссылками и массивами.

      АДРЕССоздает адрес ячейки в виде текста, используя номер строки и номер столбца.
      ВПРПросматривает левый столбец массива в поисках определенного значения и возвращает значение из указанной ячейки.
      ВЫБОРИспользует номер индекса, чтобы выбрать и вернуть значение из списка аргументов-значений.
      ГИПЕРССЫЛКАСоздание ссылки, открывающей документ, находящийся на жестком диске, сервере сети или Internet.
      ГПРПросматривает верхнюю строку массива в поисках определенного значения и возвращает значение из указанной ячейки.
      двссылВозвращает ссылку, заданную аргументом ссылка на ячейку.
      ИНДЕКСВыбирает по индексу значение из ссылки или массива.
      ОБЛАСТИВозвращает количество областей в ссылке. Область – это интервал смежных ячеек или отдельная ячейка.
      ПОИСКПОЗВозвращает относительную позицию элемента массива.
      ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫПолучение данных сводной таблицы
      ПРОСМОТРИщет значения в векторе или массиве.
      СМЕЩВозвращает ссылку заданной высоты и ширины, отстоящую от другой ссылки на заданное количество строки и столбцов.
      СТОЛБЕЦВозвращает номер столбца по заданной ссылке.
      СТРОКАВозвращает номер строки, определяемой ссылкой.
      ТРАНШВозвращает транспонированный массив.
      ЧИСЛСТОЛБВозвращает количество столбцов в ссылке или массиве.
      ЧСТРОКВозвращает количество строк в ссылке или массиве.

      Функция ГПР

      Горизонтальное первое равенство. Ищет совпадение по ключу в первой строке определенного диапазона и возвращает значение из указанной строки этого диапазона в совпавшем с ключом столбце.

      Синтаксис: =ГПР(ключ; диапазон; номер_строки; [интервальный_просмотр]).

      Так как функция аналогична функции ВПР, за исключением того, что использует горизонтальные таблицы вместо вертикальных, то описания и примеры использования подходят и для ГПР с учетом упомянутых различий.

      Excel works!

      menu

      Наверное самая популярная функция из продвинутых в Excel — это ВПР . Многие знают и часто пользуются. Но при этом у нее есть два значительных недостатка, например, как сделать «Левый ВПР «. Я использую сам ВПР, только если нужно сделать что-то быстро. В файлах для «регулярного» использования я делаю конструкцию ИНДЕКС и ПОИСКПОЗ. Чем она лучше?

      1. При добавлении столбца в таблицу данных не нужно менять номер столбца в самой формуле (как в ВПР). Столбец подвинется автоматом
      2. Можно сделать ВПР наоборот, т.е. сделать выбор из таблицы справа-налево. В ВПР первый столбец всегда должен быть для поиска.

      Как все это сделать, читаем ниже 🙂

      Вводная информация

      Использовать буду пример из этой статьи . Для того, чтобы лучше понять применение этой конструкции в сравнение с ВПР:

      Индекс и Поискпоз

      Как просто заполнить, если видишь формулу первый раз?

      Сперва, определитесь, где таблица изначальная, а где для получения данных! Поэтапно, что заполнять.

      1. Вставляем формулу в нужную область таблицы
      2. На место $G:$G поставьте те ячейки, в которых должно быть найдено значение, и соответственно, должно появиться в результате. Мы ищем фамилию, значит ищите столбец с фамилиями в начальной таблице.
      3. Замените $J:$J на в зависимости, от которых должны вернуться значения в ячейку. Нам нужны Фамилии в зависимости от ТС — вставляем те ТС, около которых должны появиться значения.
      4. Вместо $H:$H заполняем столбец, в котором нужно найти соответствующее значение. Т.е. мы ищем Фамилию по ТС, значит вставляем столбец с ТС в изначальной таблице.

      Если вам интересно, для чего в формуле знаки $ читайте здесь

      ИНДЕКС и ПОИСКПОЗ. Что это за функции?

      ИНДЕКС и ПОИСКПОЗ — очень сильные функции, которые в комбинации с другими дают отличный результат.

      =ИНДЕКС(массив; номер_строки; номер_столбца)

      Возвращает значение на пересечении указанной строки и столбца определенного диапазона. Т.е. изначально она работает с двумерными массивами.

      ИНДЕКС пример

      Выбрав массив данных J1:K4 и задав номер строки и столбца равным двум, мы получили соответствующее значение.

      Посмотрев на изначальную формулу

      Мы увидим, что вместо второго аргумента (номер строки) у нас формула ПОИСКПОЗ. Что тут она делает?

      ПОИСКПОЗ — это поиск по значению. Функция ищет заданное значение в строке или в столбце и возвращает ее порядковый номер (от начала диапазона). Т.е. во втором аргументе функции ИНДЕКС мы находим нужный нам номер ТС, получаем его номер, например 2.

      И уже в одномерном массиве $G:$G находим ячейку с номером строки = 2. Так это будет работать для каждой ячейки в столбце J.

      Вот так вроде бы не очень сложная конструкция, но, как я писал выше, очень эффективная. Т.к. вам не придется постоянно менять номер искомого значения, как в ВПР и можно делать поиск как направо, так и налево 🙂

      Частичное совпадение текста в excel

      Спустя катастрофически большой промежуток времени с момента публикации моего последнего поста, решил поделиться супер крутой, на мой взгляд, Excel-формулой, узнав о которой, начинаешь удивляться, как же раньше-то я жил без нее. Но, должен сказать, авторство ее создания не мое, а вероятнее всего принадлежит англоязычному ресурсу, о котором я скажу ниже.
      Кто более-менее часто работает с массивами данных в Excel почти наверняка знает про функцию ВПР (см. мою статью) или ИНДЕКС+ПОИСКПОЗ, которые решают достаточно частую задачу по объединению двух наборов данных по каким-либо совпадающим значениям. И действительно, использование этих функций решает задачи по сопоставлению и объединению данных в 90% случаев. Если бы не одно но — данные, по которым производится объединение, действительно должны именно совпадать. Но бывают случаи, когда требуется сопоставление по частичному совпадению. Да, в ВПР есть поиск по приблизительному совпадению, но работает он не совсем прозрачно, а потому предугадать, почему было подобрано одно похожее слово, а не другое, может быть невозможно не просто. Как вы поняли, эту прелюдию я затеял не просто так, а для того, чтобы рассказать, как же решить такую задачу при помощи Excel.
      Предположим, у нас есть список товаров, которые надо как-то сгруппировать:

      5 thoughts on “ «ВПР» по частичному совпадению ”

      На форуме SQL.ru мне подсказали еще одно очень изящное решение этой задачи, посмотреть его можно здесь:
      http://www.sql.ru/forum/actualutils.aspx?action=gotomsg&t > Спасибо большое, Казанский (автор совета)!

      Игорь, спасибо Вам огромное за эту «бронебойную» формулу. Весь интернет «перелопатила» в поиске решения своей задачи и только Вы мне помогли на 100%. Всё работает как часики. Удачи Вам, успешной работы и ещё больше таких гениальных решений.

      Ольга, спасибо большое за Ваш комментарий! Справедливости ради надо сказать, что идея этой формулы не моя, а обнаружил я ее на сайте Exceljet

      Игорь, добрый день!
      Формула прекрасная, но есть ли какая-нибудь ее вариация, которая может находить и подставлять несколько значений сразу?
      Например, в строке указаны два производителя холодильников, LG и Samsung
      Можно ли вывести их в ячейку через запятую?

      Добрый день, Артём!
      Спасибо за ваш комментарий и прошу прощения за медленный ответ. Вопрос интересный, но с ходу у меня на него ответа, увы, нет, а по времени довольно сильно ограничен. Если будет свободное время, попробую поломать голову на эту тему

      Рассмотрим использование функции ЕСЛИ в Excel в том случае, если в ячейке находится текст.

      Будьте особо внимательны в том случае, если для вас важен регистр, в котором записаны ваши текстовые значения. Функция ЕСЛИ не проверяет регистр – это делают функции, которые вы в ней используете. Поясним на примере.

      Проверяем условие для полного совпадения текста.

      Проверку выполнения доставки организуем при помощи обычного оператора сравнения «=».

      =ЕСЛИ(G2="выполнено",ИСТИНА,ЛОЖЬ)

      При этом будет не важно, в каком регистре записаны значения в вашей таблице.

      Если же вас интересует именно точное совпадение текстовых значений с учетом регистра, то можно рекомендовать вместо оператора «=» использовать функцию СОВПАД(). Она проверяет идентичность двух текстовых значений с учетом регистра отдельных букв.

      Вот как это может выглядеть на примере.

      Обратите внимание, что если в качестве аргумента мы используем текст, то он обязательно должен быть заключён в кавычки.

      ЕСЛИ + СОВПАД

      В случае, если нас интересует полное совпадение текста с заданным условием, включая и регистр его символов, то оператор "=" нам не сможет помочь.

      Но мы можем использовать функцию СОВПАД (английский аналог – EXACT).

      Функция СОВПАД сравнивает два текста и возвращает ИСТИНА в случае их полного совпадения, и ЛОЖЬ – если есть хотя бы одно отличие, включая регистр букв. Поясним возможность ее использования на примере.

      Формула проверки выполнения заказа в столбце Н может выглядеть следующим образом:

      Как видите, варианты "ВЫПОЛНЕНО" и "выполнено" не засчитываются как правильные. Засчитываются только полные совпадения. Будет полезно, если важно точное написание текста – например, в артикулах товаров.

      Использование функции ЕСЛИ с частичным совпадением текста.

      Выше мы с вами рассмотрели, как использовать текстовые значения в функции ЕСЛИ. Но часто случается, что необходимо определить не полное, а частичное совпадение текста с каким-то эталоном. К примеру, нас интересует город, но при этом совершенно не важно его название.

      Первое, что приходит на ум – использовать подстановочные знаки «?» и «*» (вопросительный знак и звездочку). Однако, к сожалению, этот простой способ здесь не проходит.

      ЕСЛИ + ПОИСК

      Нам поможет функция ПОИСК (в английском варианте – SEARCH). Она позволяет определить позицию, начиная с которой искомые символы встречаются в тексте. Синтаксис ее таков:

      =ПОИСК(что_ищем, где_ищем, начиная_с_какого_символа_ищем)

      Если третий аргумент не указан, то поиск начинаем с самого начала – с первого символа.

      Функция ПОИСК возвращает либо номер позиции, начиная с которой искомые символы встречаются в тексте, либо ошибку.

      Но нам для использования в функции ЕСЛИ нужны логические значения.

      Здесь нам на помощь приходит еще одна функция EXCEL – ЕЧИСЛО. Если ее аргументом является число, она возвратит логическое значение ИСТИНА. Во всех остальных случаях, в том числе и в случае, если ее аргумент возвращает ошибку, ЕЧИСЛО возвратит ЛОЖЬ.

      В итоге наше выражение в ячейке G2 будет выглядеть следующим образом:

      Еще одно важное уточнение. Функция ПОИСК не различает регистр символов.

      ЕСЛИ + НАЙТИ

      В том случае, если для нас важны строчные и прописные буквы, то придется использовать вместо нее функцию НАЙТИ (в английском варианте – FIND).

      Синтаксис ее совершенно аналогичен функции ПОИСК: что ищем, где ищем, начиная с какой позиции.

      Изменим нашу формулу в ячейке G2

      То есть, если регистр символов для вас важен, просто замените ПОИСК на НАЙТИ.

      Итак, мы с вами убедились, что простая на первый взгляд функция ЕСЛИ дает нам на самом деле много возможностей для операций с текстом.

      Функция ПОИСКПОЗ в Excel используется для поиска точного совпадения или ближайшего (меньшего или большего заданному в зависимости от типа сопоставления, указанного в качестве аргумента) значения заданному в массиве или диапазоне ячеек и возвращает номер позиции найденного элемента.

      Примеры использования функции ПОИСКПОЗ в Excel

      Например, имеем последовательный ряд чисел от 1 до 10, записанных в ячейках B1:B10. Функция =ПОИСКПОЗ(3;B1:B10;0) вернет число 3, поскольку искомое значение находится в ячейке B3, которая является третьей от точки отсчета (ячейки B1).

      Данная функция удобна для использования в случаях, когда требуется вернуть не само значение, содержащееся в искомой ячейке, а ее координату относительно рассматриваемого диапазона. В случае использования для констант массивов, которые могут быть представлены как массивы элементов «ключ» – «значение», функция ПОИСКПОЗ возвращает значение ключа, который явно не указан.

      Например, массив <"виноград";"яблоко";"груша";"слива">содержит элементы, которые можно представить как: 1 – «виноград», 2 – «яблоко», 3 – «груша», 4 – «слива», где 1, 2, 3, 4 – ключи, а названия фруктов – значения. Тогда функция =ПОИСКПОЗ("яблоко";<"виноград";"яблоко";"груша";"слива">;0) вернет значение 2, являющееся ключом второго элемента. Отсчет выполняется не с 0 (нуля), как это реализовано во многих языках программирования при работе с массивами, а с 1.

      Функция ПОИСКПОЗ редко используется самостоятельно. Ее целесообразно применять в связке с другими функциями, например, ИНДЕКС.

      Формула для поиска неточного совпадения текста в Excel

      Пример 1. Найти позицию первого частичного совпадения строки в диапазоне ячеек, хранящих текстовые значения.

      Вид исходной таблицы данных:

      Для нахождения позиции текстовой строки в таблице используем следующую формулу:

      Из полученного значения вычитается единица для совпадения результата с id записи в таблице.

      Сравнение двух таблиц в Excel на наличие несовпадений значений

      Пример 2. В Excel хранятся две таблицы, которые на первый взгляд кажутся одинаковыми. Было решено сравнить по одному однотипному столбцу этих таблиц на наличие несовпадений. Реализовать способ сравнения двух диапазонов ячеек.

      Вид таблицы данных:

      Для сравнения значений, находящихся в столбце B:B со значениями из столбца A:A используем следующую формулу массива (CTRL+SHIFT+ENTER):

      Функция ПОИСКПОЗ выполняет поиск логического значения ИСТИНА в массиве логических значений, возвращаемых функцией СОВПАД (сравнивает каждый элемент диапазона A2:A12 со значением, хранящимся в ячейке B2, и возвращает массив результатов сравнения). Если функция ПОИСКПОЗ нашла значение ИСТИНА, будет возвращена позиция его первого вхождения в массив. Функция ЕНД возвратит значение ЛОЖЬ, если она не принимает значение ошибки #Н/Д в качестве аргумента. В этом случае функция ЕСЛИ вернет текстовую строку «есть», иначе – «нет».

      Чтобы вычислить остальные значения «протянем» формулу из ячейки C2 вниз для использования функции автозаполнения. В результате получим:

      Как видно, третьи элементы списков не совпадают.

      Поиск ближайшего большего знания в диапазоне чисел Excel

      Пример 3. Найти ближайшее меньшее числу 22 в диапазоне чисел, хранящихся в столбце таблицы Excel.

      Вид исходной таблицы данных:

      Для поиска ближайшего большего значения заданному во всем столбце A:A (числовой ряд может пополняться новыми значениями) используем формулу массива (CTRL+SHIFT+ENTER):

      Функция ПОИСКПОЗ возвращает позицию элемента в столбце A:A, имеющего максимальное значение среди чисел, которые больше числа, указанного в ячейке B2. Функция ИНДЕКС возвращает значение, хранящееся в найденной ячейке.

      Для поиска ближайшего меньшего значения достаточно лишь немного изменить данную формулу и ее следует также ввести как массив (CTRL+SHIFT+ENTER):

      голоса
      Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector