Конечно, гораздо удобнее работать, если формулы не путаются у вас под рукой, как в колонке Е на рис. 1–4. А если это еще и формулы вроде RAND()/СЛЧИС(), генерирующей случайные числа, которые меняют свое значение при каждом автопересчете таблицы, то ваше раздражение вполне справедливо. Решение проблемы – в копировании этих ячеек и вставке их обратно в таблицу в виде постоянных величин.
Чтобы перевести формулы в цифры, просто выделите и скопируйте столбец Е, заполненный формулами, и вставьте его обратно с помощью опции «Специальная вставка» (находится во вкладке «Главная» под опцией «Вставить» в Windows и в меню «Редактировать» в MacOS). В окне «Специальная вставка» выберите вставку в качестве значений (рис. 1–7). Замечу, что это меню при вставке позволяет также транспонировать данные из вертикали в горизонталь и наоборот. Это свойство очень пригодится вам в дальнейшем.
Вставка диаграмм
Методичка, посвященная торговле с лотка, включает в себя графу «Калории» с малюсенькой табличкой. В ней указано, сколько калорий содержится в каждом напитке или закуске, которые продаются в киоске. Вы тоже легко можете сделать такую диаграмму в Excel. Во вкладке «Вставка» («Диаграммы» в MacOS) есть раздел, в котором находятся различные варианты отображения, такие как столбчатая гистограмма, линейный график и круговая диаграмма.
Заметка
В этой книге мы в основном будем пользоваться столбчатыми гистограммами, линейными графиками и графиками рассеяния. Никогда не пользуйтесь круговыми диаграммами! И особенно круговыми 3D-диаграммами, которые вам предлагает Excel. Не вздумайте ослушаться, иначе мой призрак будет мучить вас после моей смерти! Круговые диаграммы уродливы, плохо соотносятся с данными, эстетически их 3D-эффект примерно таков же, как у картинок из ракушек на стене кабинета моего стоматолога.
Выделяя столбцы А и В в листе «Calories» вы можете выбрать столбчатую диаграмму с группировкой для отображения данных. Поиграйте с графикой. Нажимайте на разделы правой клавишей мыши, чтобы увидеть меню форматирования. Например, щелчок правой клавиши на столбцах диаграммы вызовет меню, в котором можно выбрать «Формат рядов данных». Под ним вы сможете поменять цвет столбцов с синего по умолчанию на любой оттенок, который вам по вкусу, например черный.
В наличии легенды по умолчанию тоже нет никакого смысла, так что советую выделить ее и нажать «Удалить». Также вам может понадобиться выделить разные текстовые подписи к диаграмме и увеличить размер шрифта (размер шрифта находится под вкладкой «Главная»). Таким образом получается диаграмма, показанная на рис. 1–8.
Расположение меню поиска и замены
В этой книге вам частенько придется пользоваться функциями поиска и замены. В Windows это делается, как обычно, нажатием Ctrl+F для открытия окна поиска (и Ctrl+H для замены) или перемещением во вкладку «Главная», где в разделе «Правка» находится кнопка «Найти». В MacOS строка поиска расположена в верхнем правом углу листа (для замены нажмите либо стрелку вниз, либо Cmd+F для вызова меню поиска и замены).
Чтобы проверить прочитанное на практике, откройте меню замены на листе «Calories». Замените слово «Калории» на слово «Энергия» везде, где оно встречается (рис. 1–9), вбив эти слова в окно поиска и замены и нажав «Заменить все».
Формулы поиска и вывода величины
Если бы я не уточнил, что вам знакомы хотя бы некоторые простые формулы Excel (SUM, MAX, MIN, PERCENTILE / СУММ, МАКС, МИН, ПЕРСЕНТИЛЬ и т. д.), мы бы просидели здесь целый день. А я хочу начать анализировать данные. Вместе с тем я часто использую в этой книге формулы, с которыми вы могли ни разу не столкнуться, если до этого не погружались с головой в волшебный мир электронных таблиц. Эти формулы работают с поиском значения в ряду и выводом его положения или, наоборот, поиском положения в ряду и возвратом значения.
Я покажу это на примере листа «Calories».
Иногда хочется узнать положение элемента в столбце или строке. Первый он, второй или третий? Формула MATCH/ПОИСКПОЗ справляется с этим довольно неплохо. Под вашими данными о калориях назовите А18 Match/Поискпоз. Вы можете применить формулу к ячейке В18, чтобы найти, где в списке выше упоминается слово «Hamburger». Чтобы использовать эту формулу, необходимо указать в ней значение, которое нужно найти, границы поиска и 0, чтобы она вывела позицию самого слова:
=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/ГПР). Им я уделю целый раздел, ибо это монстры.
Использование VLOOKUP/ВПР для объединения данных
Перейдем обратно к листу продаж на баскетбольных матчах. При этом мы в любое время можем обратиться предыдущему листу с калориями, просто указав его название и поставив перед номером ячейки «!». Например, 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.
Если автофильтрация включена, можно кликнуть на выпадающем меню, которое появляется в ячейке В1, и выбрать для показа только определенные категории (в данном случае отобразятся товары из категории «Замороженные продукты»), как на рис. 1-13.
После фильтрации выделение столбцов данных позволяет нижней панели показывать краткую информацию об этих ячейках. Например, отфильтровав только замороженные продукты, можно выделить значения в столбце Е и использовать нижнюю панель, чтобы быстро узнать сумму прибыли только по этой категории товара, как на рис. 1-14.
Автофильтрация позволяет также производить сортировку. К примеру, если вы хотите рассортировать прибыль, просто кликните на меню автофильтрации в ячейке Profit/Прибыль (D1) и выберите сортировку по возрастанию (или убыванию), как на рис. 1-15.
Чтобы убрать все фильтры, которые вы применяли, либо вернитесь в меню фильтрации по категориям и отметьте другие категории, либо отключите кнопку «Фильтр» во вкладке «Данные», нажатую в самом начале. Вы увидите, что, несмотря на возвращение всех ваших данных на свои места, «Замороженные продукты» остаются в том порядке, который был определен фильтром.
Excel также предлагает интерфейс для выполнения более сложных сортировок, чем те, на которые способна автофильтрация. Чтобы использовать его, выделите данные для сортировки (снова выберите A: F) и нажмите «Сортировка» в разделе «Сортировка и фильтр» во вкладке «Данные». На экране появится меню сортировки. В MacOS для вызова этого меню нужно нажать стрелку вниз на кнопке сортировки и выбрать настройку.
В меню сортировки, показанном на рис. 1-16, независимо от наличия заголовка у столбцов с данными, можно выбрать колонки для сортировки по названию.
