Microsoft Excel – тонкости эффективной работы

Существует очень точное определение, приписываемое Д.И. Менделееву (хотя я в этом не уверен): «Всякая наука может считаться таковой, если научится себя измерять». Отчасти это справедливо. Ведь любой научный анализ строится на сравнениях, а объективно сравнивать можно только абстрактные количественные понятия. Абстрактность же определяется возможностью структуризации этих понятий с выделением важнейших из них.

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

На текущий момент существует множество удачных (и не очень) реализаций электронных таблиц, однако непревзойденным эталоном считается программный продукт с названием Microsoft Excel^®. Именно о нём и пойдёт речь в данной заметке. При этом не будем останавливаться на его «азах», их вы всегда сможете найти, хотя бы в Викиучебнике. Постараемся осветить лишь некоторые тонкие моменты в части автоматизации табличных расчетов и анализа, не влезая (по крайней мере, пока) и в «дебри» программирования для реализации нестандартных вычислений. И начнём с понятия «Таблицы», как основы для такой автоматизации.

Таблица как цельный элемент данных

Для краткости наш основной инструмент - Microsoft Excel® - станем именовать как MS Excel, причем за базу возьмём не самую последнюю его версию, а именно – 2010 года издания. Так вот, в MS Excel заложено немало удивительных по своим возможностям компонентов, о которых большинство пользователей либо не знают, либо просто недооценивают. И среди них бросается в глаза такой компонент, как «Таблица».

Скажете, что за чушь? Ведь MS Excel сама по себе уже таблица, для чего «огород городить»? А вот тут позвольте вам не позволить! MS Excel – это табличный процессор (грубо говоря – калькулятор), т.е. программная платформа, на которой могут выполняться даже трёхмерные вычисления. Да, да – именно трёхмерные, поскольку кроме привычного плоского набора ячеек собранных в строки и столбцы, мы имеем ещё и третье измерение в виде взаимосвязанных листов (страниц), причём не только в рамках одного файла (книги).

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

В отличие от обычного диапазона ячеек, табличный объект (Таблица) должен подчиняться определенным требованиям:

Как и всякий обособленный объект, Таблица имеет и ряд ограничений:

Впрочем, это достаточно редкие случаи ограничений, не особо мешающие пользоваться преимуществами Таблиц, таких как:

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

Чтобы превратить этот диапазон в Таблицу надо выбрать любую его ячейку, после чего найти в главном меню MS Excel вкладку «Вставка» (на слайде ниже – показано как действие 1), а в ней нажать на кнопку «Таблица» (это действие 2). Нажатие комбинации клавиш «CTRL+T» заменяет оба этих действия.

Появится всплывающее окошко, где можно уточнить границы ячеек (стрелка 1), которые следует включить в Таблицу. Но если вы изначально выбрали (активизировали) ячейку из требуемого вам диапазона, то уточнения не потребуются. «Крыжик» в радиобоксе «таблица с заголовками» означает, что первую строку диапазона следует считать заголовками (стрелка 2), иначе они будут считаться данными, что и неудобно (см. ранее, в частности, пункт 2 преимуществ Таблиц), и чревато неправильным формированием отчетов.

Подтверждение выполненных действий кликом по кнопке «Ok» сразу же создаст новую Таблицу. Можно приступать к её настройке, но, чтобы наши действия были осознанными, надо уточнить само понятие структуры Таблицы, которая раскрывается на ленте MS Excel непосредственно под вкладкой «Конструктор» (стрелка 1) главного меню.

Прежде всего - это «имя Таблицы». Если вам нужно иметь несколько Таблиц на Листе, то каждой надо присвоить своё уникальное имя (имена, которые присваиваются автоматически, как правило, не несут смысловой нагрузки). Назовём нашу Таблицу, например, «Отчёт». Тут же на ленте (стрелки 2) можно изменить размеры созданной Таблицы, поиграться с параметрами стилей и самими стилями её представления. Можно преобразовать Таблицу обратно в диапазон или обновить данные в ней из внешнего источника, откуда она была сформирована, сформировать строку итогов в нижней части Таблицы или удалить её. По умолчанию итоговая сумма рассчитывается только для последнего Столбца. Но если «встать» в строку итогов того или иного столбца и кликнуть по всплывающей справа стрелке выбора параметров, то можно сформировать и другие виды итогов, например, «среднее», «минимальное» или «максимальное» значение по Столбцу, «количество записей», а также целый ряд более специфических итогов.

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

Но это ещё только малая (видимая) часть «айсберга» из набора тонких приёмов работы в электронных таблицах, о части которых речь пойдёт в следующих главах данной статьи.

Сводные таблицы как мощный инструмент анализа

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

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

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

В версиях MS Excel моложе 2010 года есть добавочная кнопка «Рекомендуемые сводные таблицы», но это уж совсем для «чайников»: программа покажет возможные варианты макетов сводных таблиц и автоматически создаст сводную таблицу выбранного макета – достаточно лишь кликнуть по нему «мышой». Ещё там имеется возможность создания «Сводной диаграммы», но об этом поговорим в следующей главе.

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

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

После клика по кнопке «Ok» мы попадём на новый Лист, где будет показан пустой макет сводной таблицы и набор инструментов для её настройки. Собственно настройка отчётов выполняется из правой инструментальной панели «Список полей сводной таблицы».

В верхнем блоке этой панели приведен полный перечень полей имеющихся в исходной Таблице на первом Листе. Если вы захотите изменить структуру Таблицы (добавив или убрав какие-то столбцы), то на макете сводной таблицы перечень полей поменяется автоматически. Устанавливая «крыжики» возле нужных для сводной таблицы полей, мы заставим программу перенести их на макет. Однако сам MS Excel справляется с такой задачей не совсем изящно. Рекомендуем просто перетаскивать их на макет по одному, начиная с конца списка. Последнее требование необязательно, поскольку на макете всегда можно изменить порядок следования полей в будущей сводной таблице. В нашей исходной Таблице не было столбца с наименованиями строк, поэтому и в макете отчёта мы данный элемент формировать не будем.

Будущие критерии отбора и агрегации данных для требуемой в конкретный момент сводной таблицы формируются в нижней части инструментальной панели, которая состоит из четырёх областей: названий строк и столбцов для заголовков сводной таблицы, собственно значений их полей с выбором критериев агрегирования (расчета итогов) и (или) их фильтрации по необходимости.

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

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

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

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

Область фильтра отчета определяет те поля значений, по которым нужно отфильтровать итоговый отчет. Конкретное значение фильтра выбирается уже на макете (по умолчанию автофильтр, как бы, отключен, поскольку показываются все значения выбранной категории). Чтобы было понятней как работает автофильтр в отчёте - сравните два следующих слайда: в первом критерий фильтрации отсутствует, а во втором он имеет место быть. Обратите внимание, как меняется вид макета.

Для более тонкой настройки вида сводного отчета следует вернуться во вкладку главного меню «Конструктор». Там (на ленте) откроются несколько кнопок, позволяющих настроить:

Такова общая технология работы со сводными отчётами. Как видим, ничего особо мудрёного тут нет. Главное - результат для анализа получается очень быстро, даже на больших массивах информации. Разумеется, не всегда требуются столь простые отчеты. В реальной работе подчас нужно довольно витиеватое агрегирование из нескольких Таблиц, условное форматирование, динамически вычисляемые данные, которых нет в исходной Таблице и т.п. Однако это тема отдельной статьи.

Сводные диаграммы как инструмент визуального анализа

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

Процесс их создания почти аналогичен, меняется только вид отчета. В сводную таблицу MS Excel помещает детальные значения из Таблиц или диапазонов данных, а в сводную диаграмму – итоговые значения в каком-либо графическом представлении. При этом значения из Строк и Столбцов Таблицы формируют координатную сетку графика, обычно называемую «рядами» и «категориями». Ряды представляют собой связанные группы данных (могут показываться разной формой или цветом), а категории – отдельные значения в рядах.

В целом генерация визуально-графического отчета (диаграммы) похожа на создание сводного отчета. Также после нажатия на кнопку «сводная диаграмма» программа обычно создает новый лист, на котором размещает типовой макет диаграммы и справа – панели её настройки. Собственно процесс настройки аналогичен рассмотренному выше: отмечаются нужные поля Таблицы, перетаскиваются «мышой» в те или иные области настройки (или прямо на макет). При этом представление макета диаграммы меняется динамически, но его всегда можно подкорректировать вручную. В частности, если кликнуть правой клавишей «мыша» по зоне графических построений, то откроется меню с возможными видами такого представления. Например, это может быть не типовое линейное представление, в виде гистограмм, круговых, точечных и ещё десятка прочих диаграмм. На слайде ниже показаны два варианта представления: гистограммы и с поверхности с областями.

Если же создавать сводные диаграммы на основе сводных таблиц, то задача существенно упрощается, поскольку все необходимые настройки уже были сделаны при генерации сводной таблицы. Нам остается только выбрать на ленте вкладки «Вставка» вид представления (например, объёмно-круговое) и соответствующий график тут же отобразится прямо на Листе со сводной таблицей. Его можно переместить в другую зону отображения Листа или вообще на другой Лист.

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

Более подробную информацию по визуальному представлению сводных отчетов в MS Excel 2010 вы можете взять из этого учебного пособия.