Как в excel сделать сводную таблицу из нескольких файлов

Временные файлы и программы

Если файл был загружен с помощью программы Torrent, то его можно найти в папке «Мои документы» или в папке «Загрузки» или самостоятельно задать место хранения скачанного файла, зайдя в настройки программы.

Практически всех начинающих пользователей интересует также вопрос о месте хранения временных файлов. Оно будет зависеть от их характеристики. Например, временные файлы любых программ автоматически сохраняются на том жестком диске, где находится операционная система: диск С — папка «Documents and Settings» — папка «Имя пользователя» — папка «Local Settings» — папка «Temp». Это при наличии установленной программы Windows XP. Для Windows 7 и Windows Vista поиск будет выглядеть немного иначе: диск C – папка «Users» — папка «Имя пользователя» — «Local Settings» — «Temp».

Временные файлы браузера легко найти, пройдя по следующей цепочке: диск C:\users\Имя пользователя\Local Settings\Application Data\Название браузера. Если же файл так и не был обнаружен, но в памяти осталась хотя бы несколько слов из его названия, то можно воспользоваться обычным поиском системы Windows через меню «Пуск».

Как сделать сводную таблицу из нескольких листов/диапазонов Excel

Создание

Создать сводную таблицу можно в программах различных версий (2010-2016). Рассматриваемая инструкция создана на основе Майкрософт Эксель 2013.

В запущенной программе необходимо перейти во вкладку «Вставка» и выбрать пункт «Сводная таблица».

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

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

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

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

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

Как в Excel использовать сводную таблицу вместо формул

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

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

Рис. 103.1. Нужно найти самый лучший способ вычислить средний балл по тесту для парней и девушек

Вставка промежуточных итогов

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

1. Выберите любую ячейку в столбце С.
2. Щелкните правой кнопкой мыши и в контекстном меню выберите Сортировка ► Сортировка от А до Я.
3. Выберите Данные ► Структура ► Промежуточный итог для открытия диалогового окна Промежуточные итоги.
4. В окне Промежуточные итоги выберите в списке При каждом изменении в пункт Пол, в списке Операция — Среднее, а в поле Добавить итоги по установите флажок Оценка.

Результат добавления промежуточных итогов показан на рис. 103.2

Обратите внимание, что Excel также создает контур, так что вы можете скрыть подробности и просматривать только итоги. Формулы, добавленные Excel, используют функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ со значением 1 в качестве первого аргумента (1 обозначает среднее)

Вот эти формулы: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;B2:B13) =ПРОМЕЖУТОЧНЫЕ.ИТ0ГИ(1;B15:B28) =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;B2:B28) Формула в ячейке В30 вычисляет общее среднее и использует диапазон, который включают в себя другие две формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ в ячейках В14 и В29 . Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ игнорирует ячейки, которые содержат другие формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Рис. 103.2. Excel добавляет промежуточные итоги автоматически

Использование формул

Другой способ вычисления средних значений заключается в использовании формул. Формула для расчета среднего для всех студентов простая: =СРЗНАЧ(B2:B27) .

Чтобы узнать среднее значение в зависимости от пола, можете воспользоваться функцией СРЗНАЧЕСЛИ для создания следующих формул: =СРЗНАЧЕСЛИ(C2:C27;”Женский”;B2:B27) =СРЗНАЧЕСЛИ(C2:C27;”Мужской”;B2:B27)

Функция СРЗНАЧЕСЛИ была введена в Excel 2007. Если вам нужно, чтобы ваша книга была совместима с более ранними версиями, используйте эти формулы: =СУММЕСЛИ(C2:C27;”Женский”;B2:B27)/СЧЕТЕСЛИ(C2:C27;”Женский”) =СУММЕСЛИ(C2:C27;”Мужской”;B2:B27)/СЧЕТЕСЛИ(C2:C27;”Мужской”)

Использование сводной таблицы

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

1. Выберите любую ячейку из диапазона данных и выполните команду Вставка ► Таблицы ► Сводная таблица для открытия диалогового окна Создание сводной таблицы.
2. В диалоговом окне убедитесь, что Excel выбрал правильный диапазон данных, и укажите ячейку в текущем листе в качестве местоположения. Ячейка Е1 будет неплохим выбором.
3. Нажмите кнопку ОК, и Excel отобразит список полей сводной таблицы.
4. В списке полей перетащите пункт Пол в раздел Названия строк, в самый низ.
5. Перетащите пункт Оценка в раздел Значения. Excel создаст сводную таблицу, но отобразит функцию СУММ, а не СРЗНАЧ.
6. Чтобы изменить используемую итоговую функцию, щелкните правой кнопкой мыши на любом значении в сводной таблице и выберите Итоги по ► Среднее в контекстном меню (рис. 103.3).

Рис. 103.3. Эта сводная таблица вычисляет средние значения без использования формул

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

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

Способ 4: Сохранение на компьютер с помощью кода страницы

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

1. Для этого откройте в браузере на странице Instagram изображение, которое требуется загрузить, а затем сделайте по нему правый клик мыши и во всплывшем контекстном меню выберите «Просмотреть код страницы».

Когда код будет отображен, вызовите поиск сочетанием клавиш Ctrl+F.

Введите запрос «jpg» (без кавычек). Первым же результатом поиска отобразится наша картинка в виде адреса на страницу. Вам потребуется скопировать ссылку вида «https://адрес_изображения.jpg». Для наглядности смотрите скриншот ниже.

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

Немного теории

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

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

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

Плюсы использования такого вида группировки данных:

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

Обучение работе со сводными таблицами в Excel не займет много времени и может основываться на видео.

Как обновить сводную таблицу.

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

Как обновить вручную.

1. Нажмите в любом месте на свод.
2. На вкладке «Анализ» нажмите кнопку «Обновить» или же нажмите клавиши ALT + F5.

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

Чтобы обновить все сводные таблицы в файле, нажмите стрелку кнопки «Обновить», а затем  —  «Обновить все».

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

После запуска обновления вы можете просмотреть статус или отменить его, если вы передумали. Просто нажмите на стрелку кнопки «Обновить», а затем — «Состояние обновления» или «Отменить обновление».

Автоматическое обновление сводной таблицы при открытии файла.

1. Откройте вкладку параметров, как это мы только что делали.
2. В диалоговом окне «Параметры … » перейдите на вкладку «Данные» и установите флажок «Обновить при открытии файла».

Генератор примеров (массивы, таблицы, отчеты).

Как удалить сводную таблицу?

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

• Если таблица находится на отдельном листе, просто удалите этот лист.
• Ежели она расположена вместе с некоторыми другими данными на листе, выделите всю её с помощью мыши и нажмите клавишу Delete.
• Щелкните в любом месте в сводной таблице, которую хотите удалить, перейдите на вкладку «Анализ» (см. скриншот выше) => группа «Действия», нажмите небольшую стрелку под кнопкой «Выделить», выберите «Вся сводная таблица», а затем нажмите Удалить.

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

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

Возможно, вам также будет полезно:

Удаление

Германия

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

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

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

В то же время заметны скованные сухие пропорции.

2. Собор в Вормсе, построенный в тринадцатом веке по приказу местного управителя.

3. Нотр-Дам в Ульме.

4. Собор в Наумбурге.

flick•er1

Как работать со сводными таблицами в Excel

Начнем с простейшего: добавления и удаления столбцов. Для примера рассмотрим сводную табличку продаж по разным отделам (см. выше).

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

Добавим в сводную таблицу еще одно поле для отчета. Для этого установим галочку напротив «Даты» (или напротив «Товара»). Отчет сразу меняется – появляется динамика продаж по дням в каждом отделе.

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

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

А вот что получится, если мы уберем «дату» и добавим «отдел»:

А вот такой отчет можно сделать, если перетащить поля между разными областями :

Чтобы название строки сделать названием столбца, выбираем это название, щелкаем по всплывающему меню. Нажимаем «переместить в название столбцов». Таким способом мы переместили дату в столбцы.

Поле «Отдел» мы проставили перед наименованиями товаров. Воспользовавшись разделом меню «переместить в начало».

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

В открывшемся меню выбираем поле с данными, которые необходимо показать.

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

Инструкция по работе в Excel

Книга Excel состоит из листов. Лист – рабочая область в окне. Его элементы:

Чтобы добавить значение в ячейку, щелкаем по ней левой кнопкой мыши. Вводим с клавиатуры текст или цифры. Жмем Enter.

Значения могут быть числовыми, текстовыми, денежными, процентными и т.д. Чтобы установить/сменить формат, щелкаем по ячейке правой кнопкой мыши, выбираем «Формат ячеек». Или жмем комбинацию горячих клавиш CTRL+1.

Для числовых форматов можно назначить количество десятичных знаков.

Примечание. Чтобы быстро установить числовой формат для ячейки — нажмите комбинацию горячих клавиш CTRL+SHIFT+1.

Для форматов «Дата» и «Время» Excel предлагает несколько вариантов изображения значений.

Отредактируем значение ячеек:

1. Щелкнем по ячейке со словом левой кнопкой мыши и введем число, например. Нажимаем ВВОД. Слово удаляется, а число остается.
2. Чтобы прежнее значение осталось, просто изменилось, нужно щелкнуть по ячейке два раза. Замигает курсор. Меняем значение: удаляем часть текста, добавляем.
3. Отредактировать значения можно и через строку формул. Выделяем ячейку, ставим курсор в строку формул, редактируем текст (число) – нажимаем Enter.

Для удаления значения ячейки используется кнопка Delete.

Чтобы переместить ячейку со значением, выделяем ее, нажимаем кнопку с ножницами («вырезать»). Или жмем комбинацию CTRL+X. Вокруг ячейки появляется пунктирная линия. Выделенный фрагмент остается в буфере обмена.

Ставим курсор в другом месте рабочего поля и нажимаем «Вставить» или комбинацию CTRL+V.

Таким же способом можно перемещать несколько ячеек сразу. На этот же лист, на другой лист, в другую книгу.

Чтобы переместить несколько ячеек, их нужно выделить:

1. Ставим курсор в крайнюю верхнюю ячейку слева.
2. Нажимаем Shift, удерживаем и с помощью стрелок на клавиатуре добиваемся выделения всего диапазона.

Чтобы выделить столбец, нажимаем на его имя (латинскую букву). Для выделения строки – на цифру.

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

Чтобы значение поместилось в ячейке, столбец можно расширить автоматически: щелкнуть по правой границе 2 раза.

Чтобы сохранить ширину столбца, но увеличить высоту строки, нажимаем на ленте кнопок «Перенос текста».

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

Объединим несколько ячеек: выделим их и нажмем кнопку «Объединить и поместить в центре».

В Excel доступна функция автозаполнения. Вводим в ячейку А2 слово «январь». Программа распознает формат даты – остальные месяцы заполнит автоматически.

Цепляем правый нижний угол ячейки со значением «январь» и тянем по строке.

Апробируем функцию автозаполнения на числовых значениях. Ставим в ячейку А3 «1», в А4 – «2». Выделяем две ячейки, «цепляем» мышью маркер автозаполнения и тянем вниз.

Если мы выделим только одну ячейку с числом и протянем ее вниз, то это число «размножиться».

Чтобы скопировать столбец на соседний, выделяем этот столбец, «цепляем» маркер автозаполнения и тянем в сторону.

Таким же способом можно копировать строки.

Удалим столбец: выделим его – правой кнопкой мыши – «Удалить». Или нажав комбинацию горячих клавиш: CTRL+»-«(минус).

Чтобы вставить столбец, выделяем соседний справа (столбец всегда вставляется слева), нажимаем правую кнопку мыши – «Вставить» — «Столбец». Комбинация: CTRL+SHIFT+»=»

Чтобы вставить строку, выделяем соседнюю снизу. Комбинация клавиш: SHIFT+ПРОБЕЛ чтобы выделить строку и нажимаем правую кнопку мыши – «Вставить» — «Строку» (CTRL+SHIFT+»=»)(строка всегда вставляется сверху).

Форматирование сводных таблиц в Excel

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

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

Во-первых, найдём запись Sum of Amount в области Values (Значения) и кликнем по ней. В появившемся меню выберем пункт Value Field Settings (Параметры полей значений):

Появится диалоговое окно Value Field Settings (Параметры поля значений).

Нажмите кнопку Number Format (Числовой формат), откроется диалоговое окно Format Cells (Формат ячеек):

Из списка Category (Числовые форматы) выберите Accounting (Финансовый) и число десятичных знаков установите равным нулю. Теперь несколько раз нажмите ОК, чтобы вернуться назад к нашей сводной таблице.

Как видите, числа оказались отформатированы как суммы в долларах.

Раз уж мы занялись форматированием, давайте настроим формат для всей сводной таблицы. Есть несколько способов сделать это. Используем тот, что попроще…

Откройте вкладку PivotTable Tools: Design (Работа со сводными таблицами: Конструктор):

Далее разверните меню нажатием на стрелочку в нижнем правом углу раздела PivotTable Styles (Стили сводной таблицы), чтобы увидеть обширную коллекцию встроенных стилей:

Выберите любой подходящий стиль и посмотрите на результат в своей сводной таблице:

Урок 46. Как создать сводную таблицу в Excel?

Приветствую вас, посетители блога!

При аналитике различных рекламных инструментов необходима простота и наглядность отчетов. Смотреть на сухие цифры не очень-то и хочется, да и глаза разбегаются от их большого количества. Сегодня вы узнаете, как создать сводную таблицу в Excel. Это поможет вам составить наглядные отчеты по эффективности рекламных кампаний в Яндекс.Директ или Google Adwords.

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

Еще кое-что: статья написана не только для тех, кто занимается интернет-маркетингом, но и для людей, занимающимся другими сферами деятельности. Поэтому этот урок я разделю на две части:

1. Выгрузка отчета из Метрики;
2. Создание сводной таблицы;

Тем, кто не интересуется интернет-маркетингом первую часть можно не читать!

Выгрузка отчета из Метрики

Итак. Давайте выгрузим отчет о расходах рекламных кампаний Директ из Яндекс.Метрики в XLSX-файл. Для этого перейдите в отчет “Директ-расходы”: 

Затем нажмите на небольшую кнопку “Экспорт” в правом верхнем углу отчета и выберите “XLSX” из графы “Данные из таблицы”: 

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

Создание сводной таблицы

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

• Убираем все объединенные ячейки;
• Убираем пустые ячейки;

После всех подготовок исходная таблица примет вид: 

Еще раз перепроверьте табличку. Наличие пустых и объединенных ячеек неприемлемо для сводной таблицы.

Ну-с начнем создавать:

1. Перейдите во вкладку “Вставка”: 
2. Во вкладке “Вставка” найдите кнопку “Сводная таблица” (крайняя с лева). Нажмите на нее: 
3. В появившемся окошке укажите куда поместить отчет: на новый лист или на существующий. Нажмите “ОК”: 

И вот перед вами конструктор сводной таблицы. Наша задача – перетащите все необходимые данные таблицы в определенные поля сводной таблицы. Делается это в области с права: 

Как видите здесь четыре области:

• Фильтр отчета. Предназначается для фильтрации данных;
• Названия строк. Сюда переносятся анализируемые поля. Например, рекламная кампания, объявление и ключевая фраза;
• Названия столбцов. Здесь указываются значения. Подставлять ничего не нужно, данные из поля значения перенесутся автоматически;
• Значения. Сюда переносятся данные, с помощью которых анализируются поля, перенесенные в область “Названия строк”;

• Кажется доходчиво объяснил.
• Итак, вот пример моей сводной таблицы, где я просматриваю расходы, переходы по каждой кампании, по каждому объявлению, по каждой фразе: 
• В области “Названия строк” сохранена изначальная иерархия, то есть сперва кампании, потом объявления, затем ключевые фразы. Благодаря этому сводная таблица принимает следующий вид: 
• Как видите, все данные вложены по порядку. А вся таблица выглядит так: 
• Как создать сводную таблицу в Excel вы теперь знаете, осталось только сделать ее восприятие проще.

Упрощаем восприятие данных таблицы

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

Сделаем это следующим образом:

1. Во вкладке “Конструктор” выберите любой, понравившийся вам, стиль оформления таблицы: 
2. Затем переименуйте столбцы. Дважды щелкните по столбцу и дайте название;
3. Присвойте денежный формат столбцу с расходами. Нажмите правой кнопкой мыши на ячейке с данными о расходах и выберите “Числовой формат”, затем в списке с лева найдите пункт “Денежный формат”: 
4. Указываем гистограмму в каждой ячейке столбца “Расходы”. Выделите ячейки столбца “Расходы” и на вкладке “Главная” найдите кнопку “Условную форматирование”, нажмите на нее. В выпадающем списке выберите пункт “Гистограмма”: 

Результат: 

Теперь вы наглядно видите на какую кампанию больше всего тратите средств. В данном примере на кампанию “Котлы – Поиск” больше всего тратится денежных средств. Это может означать только одно – необходимо оптимизировать цену клика посредством увеличения CTR. Этот процесс называется ведение рекламных кампаний.

Ну а на этом все! Думаю, что на вопрос “Как создать сводную таблицу в Excel?” я ответил.

До свидания!

Не забывайте подписываться и оставлять комментарии к уроку.

Включаем в файле Word линейку — пошаговая инструкция

Чем сводные таблицы полезны

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

1

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

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

2

Эта таблица должна быть заполнена, то есть, надо узнать сумму по выручке с нужных регионов по каждой из товарных позиций. Эта задача легко выполняется, если использовать функцию СУММЕСЛИМН. Кроме того, нужно добавить итоги. После этого появится сводный отчет по каждой области. 

Ура, теперь вы довольны и несете отчет начальнику, который окинул его взором и попросил воплотить в жизнь еще ряд идей:

1. При составлении отчета использовать не выручку, а прибыль. 

1. Показать по колонкам – регионы, а по рядам – товары.
2. Делать аналогичные отчеты для каждого менеджера по отдельности. 

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

Template #48: Vintage YouTube Banner Template

О чем идет речь

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

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

Узнаем общую сумму продаж по каждой категории.

Проверим остатки по каждой категории товара и так далее.

Сводная таблица в Excel

Для примера используем таблицу реализации товара в разных торговых филиалах.

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

Самое рациональное решение – это создание сводной таблицы в Excel:

1. Выделяем ячейку А1, чтобы Excel знал, с какой информацией придется работать.
2. В меню «Вставка» выбираем «Сводная таблица».
3. Откроется меню «Создание сводной таблицы», где выбираем диапазон и указываем место. Так как мы установили курсор в ячейку с данными, поле диапазона заполнится автоматически. Если курсор стоит в пустой ячейке, необходимо прописать диапазон вручную. Сводную таблицу можно сделать на этом же листе или на другом. Если мы хотим, чтобы сводные данные были на существующей странице, не забывайте указывать для них место. На странице появляется следующая форма:
4. Сформируем табличку, которая покажет сумму продаж по отделам. В списке полей сводной таблицы выбираем названия столбцов, которые нас интересуют. Получаем итоги по каждому отделу.

Просто, быстро и качественно.

Важные нюансы:

• Первая строка заданного для сведения данных диапазона должна быть заполнена.
• В базовой табличке каждый столбец должен иметь свой заголовок – проще настроить сводный отчет.
• В Excel в качестве источника информации можно использовать таблицы Access, SQL Server и др.

Где применять

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

Расчет KPI

Современные CRM-системы позволяют выгрузить все необходимые отчеты в готовом виде. Но что делать тем, кто специализированный софт не использует? Остается возможность как в Экселе сделать сводную таблицу, так и посчитать необходимые показатели в ручном режиме. Второй способ кажется проще, но он не всегда удобен. Если исходные данные представлены в виде списка подобного вида, использовать объединенные реестры вполне уместно, так как это значительно облегчает последующую работу.

Мы уже посчитали объем продаж для каждого менеджера с помощью сводной базы.

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

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

Отчет по персоналу

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

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

На основе таких отчетов удобно строить диаграммы для графического отображения информации.

Заключение

Сводные таблицы Microsoft Excel содержат очень-очень много функций и настроек. В такой небольшой статье их все не охватить даже близко. Чтобы полностью описать все возможности сводных таблиц, потребовалась бы небольшая книга или большой веб-сайт. Смелые и любознательные читатели могут продолжить исследование сводных таблиц. Для этого достаточно щелкать правой кнопкой мыши практически на любом элементе сводной таблицы и смотреть, какие открываются функции и настройки. На Ленте Вы найдёте две вкладки: PivotTable Tools: Options (Анализ) и Design (Конструктор). Не бойтесь допустить ошибку, всегда можно удалить сводную таблицу и начать все заново. У Вас есть возможность, которой никогда не было у давних пользователей DOS и Lotus 1-2-3.