Читать книгу Сводная таблица в Excel. Пошаговая инструкция. Как быстро и качественно анализировать любые данные - Ар'лан ис'Дрекхэм - Страница 7

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

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

Что это за преимущества:

1. Созданная таблица автоматически изменяет свой размер при добавлении/удалении данных. В правом нижнем углу таблицы есть маркер границы.

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

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

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

4. При прокрутке таблицы вниз, заголовок всегда остается виден. И для этого не надо дополнительно закреплять его через вкладку Вид => Закрепить области. Это однозначно удобно.

5. При написании формулы в столбце умной таблицы, эта формула протягивается автоматически до конца таблицы. Не приходится самостоятельно продлевать формулу (тянуть черный крестик автозаполнения).

6. При добавлении новых строк, формулы копируются в них автоматически.

Это основные плюсы. Помимо них, есть и другие. Перечисленные выше – наиболее часто используемые.

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

Более подробно об умных таблицах можно узнать в приложении к книге «Умная таблица. Подробнее». Главный же вопрос этой главы – почему в качестве источника для сводной таблицы лучше использовать умную таблицу и когда лучше этого не делать.

Как уже сказано выше, одно из достоинств – это автоматическое изменение размера умной таблицы. Именно это преимущество делает умную таблицу лучшей подругой сводной. Давайте разберемся на примере. И заодно потренируемся размещать новую сводную таблицу на уже на существующем листе.

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

Начинаем с того, что преобразовываем простую таблицу в умную. Ставим курсор в любую ячейку исходной таблицы, нажимаем комбинацию клавиш Ctrl+A (выделится весь диапазон)

После этого нажимаем Ctrl+T, в появившемся диалоговом окне проверяем галочку Таблица с заголовками, нажимаем ОК. В исходной таблице действительно есть заголовок, именно поэтому необходимо оставить галочку. Если заголовка нет, то галочку надо снимать (подробнее в приложении к книге «Оставлять или нет галочку ✔ Таблица с заголовками для умной таблицы?»).

Таблица окрашивается цветом, вверху появляется вкладка Конструктор таблиц. Это верный признак того, что таблица поумнела (стала, собственно, умной). Во вкладке Конструктор таблиц задаем нужное имя в поле Имя таблицы.

Если вам не нужна раскраска таблицы, на вкладке Конструктор таблиц в блоке Стили таблиц выбираем тип форматирования Нет.

Вот такой результат должен получиться

Теперь создадим сводную таблицу. Выделяем любую ячейку умной таблицы. На вкладке Вставка нажимаем Сводная таблица.

В появившемся диалоговом окне отобразится название умной таблицы (ничего не меняем). А вот ниже, там, где написано Укажите, куда следует поместить отчет сводной таблицы выбираем вариант На существующий лист. После этого в поле Диапазон надо поставить курсор и выбрать ячейку, в которую планируем поместить отчет сводной таблицы. Я хочу разместить в ячейке G1. Обратите внимание, как отображает имя выбранной ячейки Excel. Не просто G1, а «Исходник_умная таблица»! $G$1. Кто работает с Excel хотя бы иногда, знают, что в данном случае «Исходник_умная таблица» – это название листа, а символ $ означает закрепление диапазона. Это не я закрепила диапазон вручную. Вместо меня это сделал Excel самостоятельно (Excel вообще достаточно часто старается помочь и некоторые действия делает автоматически, за пользователя. Вопрос только в том – всегда ли принимать эти автоматические изменения, это мы обсудим позже). Нажимаем ОК.

Вот такой промежуточный результат получается

Кладем поле Продано штук в область Значения. А поле Серия перетаскиваем в область Строки. Легко сказать «Кладем», а как это сделать на практике? Первый способ – отметить галочкой нужное на поле в списке полей. Я отметила поля Серия и Продано штук и Excel абсолютно корректно положил поля в нужные области.

Спойлер – так происходит не всегда и не редки случаи, когда Excel перетаскивает поле не в ту область, которую необходимо (например, поле Серия необходимо положить в область Столбцы, а Excel перемещает его в область Строки). Именно поэтому я предпочитаю ручное перетягивание поля в нужную область. И это второй способ. Зажимаем левой кнопкой мыши поле Серия и перетаскиваем его в область Строки. Зажимаем левой кнопкой мыши поле Продано штук и перетаскиваем его в область Значения.

Вот такую таблицу получаем:

Осталось переименовать столбцы созданной сводной таблицы. Для этого достаточно выделить ячейку с заголовком Название строк и вместо него указать Серия. И, казалось бы, можно сделать все тоже самое со вторым заголовком, но!

При попытке переименовать заголовок из Сумма по полю Продано штук в Продано штук Excel выдаст сообщение, что такое поле сводной таблицы уже существует. Что это значит? А это значит, что в списке полей для сводной таблицы есть именно такое название поля (досимвольно, т.е. абсолютно идентичное название с точностью до символа). А я напомню, что поле сводной таблицы – это заголовок исходной таблицы (он же поле сводной таблицы). И его нельзя использовать в качестве заголовка сводной таблицы. Нельзя использовать досимвольно. Но если добавить невидимый глазу пробел до _Продано штук (здесь и далее вместо пробела я буду использовать нижнее подчеркивание, чтобы читателю было удобнее) или после Продано штук_, то сводная абсолютно спокойно даст переименовать заголовок.

Исправляем заголовок Сумма по полю Продано штук в Продано штук_ и вот такой результат получаем. Поздравляю! Ваша первая сводная создана!

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

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

Сок автоматически попадет в сводную.

А теперь сделаем все тоже самое. Но только пропустим этап преобразования простой таблицы в умную. Возвращаемся к исходнику и начинаем работать с ним. Выделяем любую ячейку исходной таблицы (ту самую не умную, а обычную простую таблицу). Нажимаем комбинацию клавиш Ctrl+A (выделится весь диапазон). На вкладке Вставка нажимаем Сводная таблица. В появившемся диалоговом окне отобразится выделенный диапазон «Исходник_простая таблица»! $A$1:$D$12 (мы ничего не меняем). Ниже, там где написано Укажите, куда следует поместить отчет сводной таблицы выбираем вариант На существующий лист. После этого в поле Диапазон надо поставить курсор и выбрать ячейку, в которую планируем поместить отчет сводной таблицы. Размещаем отчет сводной таблицы в ячейке G1. Нажимаем ОК.

Кладем поле Продано штук в область Значения. А поле Серия перетаскиваем в область Строки. Переименуем столбцы созданной сводной таблицы. Сейчас результат никак не отличается от того, который получился, когда мы делали сводную таблицу на основе умной.

Давайте добавим строку с продажей соков в исходную таблицу (точно так же, как мы делали это в прошлом примере).

Теперь обновляем сводную таблицу. На любой ячейке сводной таблицы щелкаем правой кнопкой мыши. В появившемся меню выбираем Обновить. И что мы видим?

Ничего не поменялось. Почему? Давайте вернемся к источнику данных. Чтобы увидеть источник данных, на основе которых построена сводная таблица, необходимо на вкладке Анализ сводной таблицы выбрать Источник данных => Источник данных…

Вот так выглядит источник данных. Это абсолютно конкретный диапазон данных и для того, чтобы в сводную попали новые (добавленные позже) данные, то надо поменять диапазон в Источнике данных. Но насколько это удобно? Вот главный вопрос.

Как только в источнике данных «Исходник_простая таблица»! $A$1:$D$12 исправить на «Исходник_простая таблица»! $A$1:$D$13, то в сводной таблице отобразятся все данные.

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

!Материалы к главе: файлы «Умная таблица-основа сводной_исходник» и «Умная таблица-основа сводной»