Инструкция

Откройте документ Excel с данными, из которых вы хотите сделать сводную таблицу . Щелкните указателем мыши по одной из ячеек таблицы, обязательное условие - чтобы в этой ячейке было записано какое-то значение. Самое удобное – присвоить столбцу соответствующее имя, например, «название», «количество» и т.п.

В панели меню в верхней части окна найдите кнопку «Данные» (для Excel 2000, ХР, 2003) или вкладку «Вставка» (для Excel 2007) и нажмите ее. Откроется список, в котором активируйте кнопку «Сводная таблица». Откроется «Мастер создания сводных таблиц», он поможет настроить все необходимые параметры.

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

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

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

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

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

Полезный совет

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

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

Excel – одна из основных программ Windows. Многие пользователи знают о ее существовании, но не подозревает о тех возможностях, которые она содержит. Одной из них можно считать таблицу. Те, кто в первый раз об этом узнали, могут спросить «Как создать таблицу в Excel?»

Инструкция

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

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

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

Лист, показанный на рис. 167.1, отображает тот тип преобразования, о котором я говорю. Диапазон А1:Е4 содержит исходную сводную таблицу: 48 точек данных. Столбцы G:I показывают часть 48-строковой таблицы, полученную из сводной таблицы. Другими словами, каждое значение в исходной сводной таблице преобразуется в строку, которая также содержит соответствующие значению название продукта и месяц. Этот тип списка полезен, поскольку его можно отсортировать и манипулировать им другими способами.

Хитрость создания такого списка заключается в использовании сводной таблицы. Но прежде чем вы сможете применить этот метод, вы должны добавить команду Мастер сводных таблиц на панель быстрого доступа. Excel 2007, Excel 2010 и Excel 2013 все еще поддерживают Мастера сводной таблицы , но он недоступен на ленте. Чтобы получить доступ к мастеру, выполните следующие действия.

1. Щелкните правой кнопкой мыши на панели быстрого доступа и выберите в контекстном меню пункт Настройка панели быстрого доступа .
2. В разделе Панель быстрого доступа диалогового окна Параметры Excel выберите Команды на ленте из раскрывающегося списка слева.
3. Прокрутите список и выберите пункт .
4. Нажмите кнопку Добавить .
5. Нажмите 0К , чтобы закрыть диалоговое окно Параметры Excel .

После выполнения этих шагов ваша панель быстрого доступа будет содержать новый значок.

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

1. Активизируйте любую ячейку в сводной таблице.
2. Щелкните на значке Мастер сводных таблиц и диаграмм , который вы добавили на панель быстрого доступа.
3. В диалоговом окне Мастер сводных таблиц и диаграмм установите первый переключатель в положение в нескольких диапазонах консолидации и нажмите кнопку Далее .
4. В шаге 2а установите переключатель в положите Создать поля страницы и нажмите кнопку Далее .
5. В шаге 2b в поле Диапазон укажите диапазон сводной таблицы (А1:Е4 для выборки из примера) и нажмите кнопку Добавить ; затем нажмите кнопку Далее , чтобы перейти к шагу 3.
6. В шаге 3 выберите место для сводной таблицы и нажмите кнопку Готово . Excel создаст сводную таблицу с данными и покажет область Список полей сводной таблицы .
7. В области Список полей сводной таблицы снимите флажки Строка и Столбец .

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

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

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

Рекомендуемые инструменты повышения производительности для Excel / Office

Преобразование сводной таблицы в список

Чтобы преобразовать сводную таблицу в список, вы должны убедиться, что сводная таблица была настроена как ваша потребность. Например, я преобразую приведенную ниже сводную таблицу в список.

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

2. Нажмите Grand Totals > Выкл. Для строк и столбцов под дизайн Вкладка. Смотрите скриншот:

3. Нажмите Макет отчета > Повторить все метки элементов под дизайн Вкладка. См. Снимок экрана:

4. Нажмите Макет отчета снова и нажмите Показать в табличной форме , Смотрите скриншот:

Теперь сводная таблица показана ниже:

5. Нажмите Опционы вкладку (или Анализировать вкладка) и снимите флажок Кнопки и Заголовки полей , который относится к Показать группа.

Теперь сводная таблица, показанная ниже:

5. Теперь я покажу пустые ячейки как ноль. Щелкните любую ячейку сводной таблицы и щелкните правой кнопкой мыши, чтобы выбрать Параметры сводной таблицы , затем в раскрывающемся диалоговом окне, под Макет и формат вкладка, снимите флажок Для пустых ячеек И нажмите OK закрыть Параметры сводной таблицы Диалог. Смотрите скриншот:

Если вы хотите узнать больше о том, как показывать пустые ячейки как ноль, нажмите .

6. Выберите сводную таблицу и нажмите Ctrl + C в то время, чтобы скопировать его, затем поместите курсор на ячейку, в которую вы хотите вставить сводную таблицу в виде списка, и щелкните правой кнопкой мыши, чтобы выбрать Специальная вставка > Значение (V) , Смотрите скриншот:

Внимание : В Excel 2007 вам нужно щелкнуть Главная > макаронные изделия > Вставить значения для вставки сводной таблицы в виде списка.

Теперь вы можете увидеть список, показанный ниже:

Office Tab

Принесите удобные вкладки в Excel и другое программное обеспечение Office, как Chrome, Firefox и новый Internet Explorer.

Сегодня поговорим про ТАБЛИЦЫ. Не про таблицы, а именно про ТАБЛИЦЫ . Именно так Microsoft предложил называть те замечательные таблицы, о которых пойдёт речь ниже. В зачаточном состоянии они появились в Excel 2003 и назывались там "списками " ("lists"). В Excel 2007 их довели до ума и переименовали в ТАБЛИЦЫ (TABLES), а то что раньше все нормальные люди называли таблицами, теперь предложено называть ДИАПАЗОНОМ (range). В России этот подход не прижился, да и чего ради людям менять задним числом устоявшиеся термины, поэтому TABLES мы будем называть "умными таблицами ", а таблицы в их общеупотребительном понимании оставим в покое.

Умные таблицы

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

Зачем они нужны?

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

1. Всем столбцам давать уникальные названия колонок.
2. Не допускать пустых столбцов и строк в таблице.
3. Не допускать разнородных данных в пределах одной колонки. Если уж решили, что, например, в колонке E должен хранится объем продаж в штуках, то не надо туда же вносить объём продаж, скажем, в деньгах у части строк таблицы.
4. Не объединять ячейки без самой крайней необходимости.
5. Форматировать таблицу, чтобы она выглядела одинаково во всех своих частях. То есть элементарно рисовать сетку, выделять цветом заголовки столбцов.
6. Закреплять области, чтобы заголовок был всегда виден на экране.
7. Ставить фильтр по умолчанию.
8. Вставлять строку подитогов.
9. Грамотно использовать абсолютные и относительные ссылки в формулах, чтобы их можно было протягивать без необходимости внесения изменений.
10. При рабте с таблицей не выделять цветом строки/столбцы за пределами таблицы. Это поветрие, кстати очень сильно распространено, - взять выделить всю строку или весь столбец одним кликом мыши и закрасить. И наплевать, что в таблице 100 строк, а закрасилось помимо них ещё 1 000 000 строк. А потом невинно интересоваться: "Почему мои файлы так много весят?"

Соблюдение этих простых правил поможет вам, если не уходить пораньше с работы домой, так хотя бы работать более продуктивно и осмысленно, осваивая действительно интересные и сложные вещи, а не воюя с последствиями своей неаккуратности на каждом шагу.

Так вот к 13-й версии (Excel 2007) его разработчики пригляделись к типовым действиям квалифицированных пользователей Excel и падарили нам функционал умных таблиц, за что им огромное спасибо. Потому что большую часть того, что я только что перечислил умные таблицы либо делают сами автоматически, либо очень сильно облегчают настройку оного.

Итак, давайте познакомимся, как создаются умные таблицы и какими полезными свойствами обладают.

1.Создание умной таблицы

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

1. Способ 1 - на ленте ГЛАВНАЯ выбираем Форматировать как таблицу , выбираем понравишейся дизайн (при этом вам доступны 60 стандартных способа форматирования)
2. Способ 2 - Нажимаем Ctrl-T
3. Способ 3 - На ленте ВСТАВКА выбрать Таблица

2.Форматирование

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

1. Выделите таблицу целиком - проще всего 2 раза нажать Ctrl-A (латинская "A"!)
2. На ленте ГЛАВНАЯ щёлкните Стили ячеек , далее стиль Обычный

При этом все проблемы с форматированием сразу решаются. Однако вам придётся восстанавливать форматы столбцов ячеек: формат даты, времени, нюансы числового формата (типа количества знаков после точки), но это не очень сложно. В любом случае вам решать - сбрасывать форматирование этим способом, либо каким-то другим, менее "разрушительным", но знать о нём надо.

3.Предпросмотр стиля таблицы

Через меню Форматировать как таблицу вы можете увидеть, как будет выглядеть ваша таблица при приминении любого имеющегося стандартного стиля. Очень удобно и наглядно!

4.Прочие плюшки и полезности...

1. Чередующийся цвет строк или столбцов! Да знаете ли вы, что раньше для этого надо было 10 минут колдовать с условным форматированием с бубном и крысиными костями!. "А теперь? Оглянитесь вокруг, - какие вам корпуса понастроили, какие газоны разбили, водопровод, телевизор, газовая кухня, парники, цветники..."
2. Включение строки итогов одним нажатием!
3. Фильтр по умолчанию
4. Первый и последний столбец могут быть выделены жирным шрифтом
5. При прокрутке таблицы столбцы видны БЕЗ закрепления областей! Чего ж вам боле?!

5.Упрощенное выделение таблицы, столбцов, строк

6.Умная таблица имеет имя и его можно изменять

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

7.Вставка срезов.

В Excel 2010 появилась такая полезная функция как срезы . Это наглядные фильтры, которые можно добавлять к сводным таблицам, а также и к умным таблицам тоже. Посмотрим как это работает:

8.Структурированные формулы.

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

"Умный" способ адрессации	На что ссылается	Формула возвращает	Стандартный диапазон
=СУММ(Результаты)	По умолчанию умная таблица, которая названа "Результаты" ссылается на область своих данных	87	B3:E7
=СУММ(Результаты[#Данные])	Тот же результат вернёт данная формула, где область данных указана в явном виде.	87	B3:E7
=СУММ(Результаты[Продажи])	Суммируем область данных столбца "Продажи". Если надо создать именованный диапазон, который будет ссылаться на столбец умной таблицы, то надо использовать синтаксис Результаты[Продажи].	54	D3:D7
=Результаты[@Прибыль]	Данную формулу мы вводили в строке 3. @ - означает текущую строку, а Прибыль - столбец, из которого возвращаются данные.	6	E3
=СУММ(Результаты[Продажи]:Результаты[Прибыль])	Ссылка на диапазон столбцов: от колонки "Продажи", до колонки "Прибыль" включительно. Обратите внимание на оператор ":", который создаёт диапазон.	87	D3:E7
=СУММ(Результаты[@])	Формулу вводили в троке 3. Она вернула всю строку таблицы.	11	B3:E3
=СЧЁТЗ(Результаты[#Заголовки])	Подсчёт количества элементов в #Заголовки.	4	B2:E2
=Результаты[[#Итоги];[Продажи]]	Формула возвращает итоговую строку для столбца Продажи. Это не одно и тоже, что Результаты[Продажи], так как итоговая функция может быть разной, например, средней величиной.	54	D8

Предупреждение для любителей полазить по иностранным сайтам: товарищи, учитывайте различия в региональных настройках России и западных стран (англоязычных то точно). В региональных настройках есть такой параметр, как "Разделитель элементов списка ". Так вот на Западе это запятая , а у нас точка с запятой . Поэтому, когда они пишут формулы в Excel, то параметры разделяются запятыми , а когда мы пишем, то - точкой с запятой . Тоже самое и в переводных книгах, везде.

Проблемы