1. Все, что вы жаждали знать об электронных таблицах, но боялись спросить

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

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

Отличия в разных версиях Excel

Как я уже упоминал во введении, для этой книги подходят Excel 2007, 2009, 2011 для MacOS и LibreOffice. К сожалению, в каждой новой версии Excel разработчики Microsoft перемещают инструменты и функционал как им угодно.

Например, элементы из вкладки «Разметка» в версии 2011 года находятся во вкладке «Вид» во всех остальных версиях. «Поиск решения» в версиях 2010 и 2013 одинаковый, но реализован он лучше в 2007 и 2011, несмотря на гротескный интерфейс в версии 2007.

Снимки с экрана в этой книге будут делаться с Excel 2011. Если у вас более новая или старая версия, вам придется действовать по-другому, особенно когда дело касается положения элементов управления во вкладках меню. Я очень постараюсь найти и учесть все различия. Если что-то мной упущено, поисковый инструмент Excel и Google всегда к вашим услугам.

А вот то, что, несомненно, должно вас обрадовать: «табличная часть электронной таблицы» всегда неизменна.

Несколько слов о LibreOffice. Если вы решили пользоваться открытыми источниками программного обеспечения, рискну предположить, что вы – человек, склонный до всего доходить самостоятельно. И хотя я не буду напрямую обращаться к интерфейсу LibreOffice, вы этого попросту не заметите. Они с Excel похожи как две капли воды.

Немного данных для примера

Заметка:

Рабочая тетрадь Excel, используемая в этой главе «Concessions.xlsx», доступна для загрузки на сайте книги www.wiley.com/go/datasmart

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

У вас есть электронная таблица о вчерашних продажах, и выглядит она примерно как рис. 1–1.

На рис. 1–1 показана каждая продажа: что именно продано, к какому типу еды или напитков относится проданный товар, цена и процент прибыли с продажи.

Быстрый просмотр с помощью кнопок управления

Если хотите ознакомиться с записями – промотайте список колесиком мышки, пальцем (если у вас сенсорный экран) или стрелками клавиатуры. Пока вы просматриваете записи, приятно иметь строку заголовков в поле зрения – тогда вы точно не забудете, что в какой колонке записано. Для этого выберите «Закрепить области» или «Закрепить верхнюю строку» во вкладке «Вид» в Windows (вкладка «Разметка» в MacOS2011, как показано на рис. 1–2).

Чтобы быстро переместиться в конец документа и посмотреть, сколько всего у вас продаж, выберите значение в одном из заполненных столбцов и нажмите Ctrl+↓ (Command+↓ на Mac). Вас отбросит прямо к последней заполненной ячейке этого столбца. В этой таблице последняя строка – 200. Заметьте также, что кнопка Ctrl/Command со стрелками даст вам возможность точно так же перемещаться по всему документу, в том числе вправо и влево.

Если вы хотите узнать среднюю прибыль за единицу проданного за вечер, то под столбцом с ценами (столбцом С) можно вбить формулу:

=AVERAGE(C2:C200)/

=СРЗНАЧ(С2:С200)

Средняя прибыль получается $2,83, так что отдыхать от трудов праведных вам, увы, еще не время. Подсчет можно произвести и другим способом: переместиться на последнюю ячейку в столбце, С200 и удерживать Shift+Ctrl+↑, чтобы выделить весь столбец доверху, а затем выбрать «Среднее значение» на нижней панели справа (рис. 1–3). В Windows нужно кликнуть на этой панели для того, чтобы выбрать среднее значение, скажем, вместо суммы, стоящей там по умолчанию. В MacOS, если нижняя панель отключена, нужно нажать на меню «Вид» и выбрать «Строку состояния», чтобы включить ее.

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

Пожелав видеть свою прибыль в фактических долларах, а не в процентах, вы можете добавить что-то вроде заголовка в столбец Е, который назовем «Фактическая прибыль». В ячейке Е2 нужно просто перемножить соответствующие значения из столбцов с ценой и прибылью, чтобы получить такую формулу:

=C2*D2

Для строки с пивом результат будет равным $2. Не нужно переписывать формулу для каждой строки. Excel позволяет переносить формулы из ячейки перетаскиванием за правый нижний угол куда вам угодно. Значения в столбцах С и D будут меняться в зависимости от того, куда скопирована формула. Если, как в случае с данными о продажах, столбец слева полностью заполнен, дважды кликните на правом нижнем углу ячейки с формулой, и Excel заполнит значениями весь столбец, как это показано на рис. 1–4. Попробуйте этот двойной клик сами, потому что я буду использовать его во всей книге. Освоив его сейчас, вы избавите себя от огромных неудобств в будущем.

Обязательно ли значение в ячейках, упомянутых в формуле, должно меняться в зависимости от того, куда вы ее перетаскиваете или копируете? Нет, конечно. Хотите оставить что-то неизменным – просто поставьте перед ним $.

К примеру, если вы измените формулу в Е2 таким образом:

=C$2×D$2

В этом случае при копировании формулы на все последующие ячейки в ней ничего не меняется. Формула продолжает обращаться ко 2-й строке.

Если скопировать формулу вправо, то С заменится на D, D на Е и т. д. Если вам не нравится такое «поведение», добавьте $ также перед ссылками на столбцы в формуле. Это называется абсолютной ссылкой, в противоположность относительной ссылке.

Форматирование ячеек

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

В этом меню вы можете выбрать формат содержимого ячеек столбца Е. В нашем случае нужен денежный формат. Также можно указать число знаков после запятой при округлении. Оставьте 2 знака после запятой, как показано на рис. 1–5. Также в меню «Формат ячеек» доступны такие опции, как цвет шрифта, заливка ячейки, выравнивание текста, границы и т. д.

Но есть нюанс. Допустим, нужно отформатировать только те ячейки, которые содержат определенные значения или диапазон значений, и это форматирование должно меняться в зависимости от значений.

Такой вид форматирования называется условным форматированием, и оно повсеместно используется в этой книге.

Закройте меню «Формат ячеек» и переместитесь во вкладку «Главная». В разделе «Стили» («Формат» в MacOS) вы найдете «Условное форматирование» (рис. 1–6). При нажатии на него выпадает меню. Самое используемое условное форматирование в этой книге – цветовые шкалы. Выберите шкалу для столбца Е и посмотрите, как изменился цвет каждой ячейки в зависимости от величины значения в ней.

Чтобы очистить условное форматирование, используйте опцию «Удалить правила» меню условного форматирования.

Специальная вставка

Конечно, гораздо удобнее работать, если формулы не путаются у вас под рукой, как в колонке Е на рис. 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).