Джон Форман - Много цифр. Анализ больших данных при помощи Excel

=MATCH("Hamburger", A2:A15,0) /

=ПОИСКПОЗ("Hamburger", A2:A15,0)

Она выдает 6, так как «Hamburger» – шестая позиция в списке (рис. 1-10).

Следующая формула – INDEX/ ИНДЕКС. Назовите ячейку А19 Index/Индекс.

Эта формула находит значение элемента по заданному положению в строке или столбце. Например, подставив в нее из нашей таблицы калорий А1:В15 и задав координаты поиска «3 строка, 2 столбец», мы получим количество калорий в бутылке воды:

=INDEX(A1:B15,3,2) /

=ИНДЕКС(A1:B15,3,2)

Мы видим количество калорий, равное 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/ ГПР). Им я уделю целый раздел, ибо это монстры.

Перейдем обратно к листу продаж на баскетбольных матчах. При этом мы в любое время можем обратиться предыдущему листу с калориями, просто указав его название и поставив перед номером ячейки «!». Например, Calories!В2является отсылкой к количеству калорий в пиве, несмотря на то, что вы в данный момент работаете с другим листом.

Предположим, вы захотите увидеть количество калорий на листе продаж для каждого наименования товара. Вам нужно будет каким-то образом найти содержание калорий в каждом товаре и поместить его в колонку, следующую за прибылью. Что ж, оказывается, и для этого есть отдельная функция под названием VLOOKUP/ВПР.

Назовем колонку F в нашем листе «Calories / Калории». Ячейка F2 будет содержать количество калорий из таблицы в товаре из первой строки – пиве. Используя эту формулу, можно указать в названии товара из ячейки А2 ссылку на таблицу Calories!$A$1:$B$15и номер столбца, из которого следует выбирать значения. В нашем случае он второй по счету:

=VLOOKUP(A2,Calories!$A$1:$B$15,2,FALSE) /

=ВПР(A2,Calories!$A$1:$B$15,2,ЛОЖЬ)

FALSE/ЛОЖЬв конце формулы означает, что вам не подходят приблизительные значения «Beer». Если функция не может найти «Beer» в таблице калорий, она возвращает ошибку.

После ввода формулы вы увидите, что 200 калорий считались из таблицы в листе «Calories». Поставив $ в формуле перед ссылками на таблицу, вы можете скопировать формулу вниз по колонке двойным щелчком на нижнем правом углу ячейки. Оп-ля! У вас есть количество калорий для каждой позиции, как показано на рис. 1-11.

Отразив в листе продаж калорийность ваших товаров, задайтесь целью видеть, например, только товары из категории «Замороженные продукты» – иными словами, отфильтровать ваш лист. Для этого сначала выберите данные в рамках А1:F200. Наведите курсор на А1 и нажмите Shift+Ctrl+↓, а затем →. Есть способ еще проще – кликнуть наверху столбца и, удерживая клавишу мышки нажатой, переместить курсор к столбцу F, чтобы выделить все 6 столбцов.

Затем, чтобы применить автофильтрацию к этим шести колонкам, нажмите кнопку «Фильтр» из вкладки «Данные». Она похожа на серую воронку, как на рис. 1-12.