Много цифр. Анализ больших данных при помощи Excel - стр. 12
Мы видим количество калорий, равное 0, как и предполагалось (рис. 1-10).
Другая формула, которая часто встречается в нашем тексте, – это >OFFSET/>СМЕЩ. Назовем же ячейку А20 Offset/Смещ и поиграем с формулой в В20.
С помощью этой формулы вы задаете промежуток, который перемещаете, подобно курсору, по сетке из столбцов и строк (точно так же, как >INDEX/>ИНДЕКС ищет единственную ячейку, если только в нем не упомянут 0). Например, можно задать функции >OFFSET/>СМЕЩ рамки от верхней левой ячейки листа А1 и затем растянуть ее на 3 ячейки вниз, создавая ряд из 3 строк и 0 столбцов:
>=OFFSET(A1,3,0) /
>=СМЕЩ(A1,3,0)
Эта формула возвращает значение третьего элемента списка – «Chocolate Bar» (рис. 1-10).
Последняя формула, о которой я хочу сказать в этом разделе, – >SMALL/>НАИМЕНЬШИЙ (у него есть двойник – >LARGE/>НАИБОЛЬШИЙ, который работает точно так же). Если у вас есть список значений и вы хотите выбрать, скажем, третье наименьшее из них, данная функция делает это за вас. Назовите ячейку А21 Small/Наименьший, а в В21 напишите следующую формулу, содержащую границы поиска и параметр 3:
>=SMALL(B2:B15,3)/
>=НАИМЕНЬШИЙ(B2:B15,3)
Эта формула возвращает значение 150, которое является третьим наименьшим после 0 (бутылка воды) и 120 (газировка), как показано на рис. 1-10.
И, наконец, еще одна формула для поиска значений, похожая на >MATCH/>ПОИСКПОЗ, употребившую стероиды. Это >VLOOKUP/>ВПР (и ее горизонтальный двойник >HLOOKUP/>ГПР). Им я уделю целый раздел, ибо это монстры.
Использование VLOOKUP/ВПР для объединения данных
Перейдем обратно к листу продаж на баскетбольных матчах. При этом мы в любое время можем обратиться предыдущему листу с калориями, просто указав его название и поставив перед номером ячейки «!». Например, >Calories!В2 является отсылкой к количеству калорий в пиве, несмотря на то, что вы в данный момент работаете с другим листом.
Предположим, вы захотите увидеть количество калорий на листе продаж для каждого наименования товара. Вам нужно будет каким-то образом найти содержание калорий в каждом товаре и поместить его в колонку, следующую за прибылью. Что ж, оказывается, и для этого есть отдельная функция под названием >VLOOKUP/ВПР.
Назовем колонку F в нашем листе «Calories / Калории». Ячейка F2 будет содержать количество калорий из таблицы в товаре из первой строки – пиве. Используя эту формулу, можно указать в названии товара из ячейки А2 ссылку на таблицу >Calories!$A$1:$B$15 и номер столбца, из которого следует выбирать значения. В нашем случае он второй по счету:
>=VLOOKUP(A2,Calories!$A$1:$B$15,2,FALSE) /