Функция впр (vlookup) в excel для чайников

Плюсы сводных таблиц

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

1. Таблица составляется по практически любому объему данных.
2. Редактировать вид отчета можно через меню форматов – встроенная библиотека содержит множество цветовых тем таблиц.
3. Возможно объединение данных в более широкие группы, например несколько дат объединяются в кварталы.
4. По результатам отчета можно провести расчеты с помощью инструментов Excel, это не повлияет на источники данных.
5. Информация в сводной таблице может стать основой для составления графика или другого визуального отчета.

Настройки сводной таблицы в Excel.

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

После того, как галочки поставлены, элементы появились в блоках (областях) Фильтры, Столбцы, Строки и Значения.

И уже будет сформирована Сводная таблица. 

Продолжим настройку Сводной таблицы. Наша цель, чтобы в столбцах Сводной таблицы отражалась сумма проданных товаров, во всех пяти магазинах, помесячно (январь — июнь). А в строках отражались товары, по видам (Товар №1, Товар №2 и т.д.). На выходе мы получим суммарное количество проданных товаров, каждого вида, указные по месяцам. В качестве фильтра используем магазины. Таким образом, в случае необходимости, мы сможем отразить в Сводной таблице только нужный нам магазин (Магазин №1, Магазин №2 и т.д.).

Как это сделать.

Перетаскиваем элемент Магазин в поле Фильтры.

Наша таблица готова.

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

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

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

Сортировка значений

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

Для этого нужно сделать следующее.

1. Кликните на треугольник около нужного поля.
2. В результате этого вы увидите следующее меню. Здесь вы можете выбрать нужный вариант сортировки («от А до Я» или «от Я до А»).

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

В результате этого вы увидите следующее окно. Для более детальной настройки нужно нажать на кнопку «Дополнительно».

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

Данные в ключевых столбцах не совпадают

Вот пример: Вы владелец небольшого магазина, получаете товар от одного или нескольких поставщиков. У каждого из них принята собственная номенклатура, отличающаяся от Вашей. В результате возникают ситуации, когда Ваша запись “Case-Ip4S-01” соответствует записи “SPK-A1403” в файле Excel, полученном от поставщика. Такие расхождения возникают случайным образом и нет никакого общего правила, чтобы автоматически преобразовать “SPK-A1403” в “Case-Ip4S-01”.

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

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

Создаём вспомогательную таблицу для поиска.

Создаём новый лист Excel и называем его SKU converter. Копируем весь столбец Our.SKU из листа Store на новый лист, удаляем дубликаты и оставляем в нём только уникальные значения.

Рядом добавляем столбец Supp.SKU и вручную ищем соответствия между значениями столбцов Our.SKU и Supp.SKU (в этом нам помогут описания из столбца Description). Это скучная работёнка, пусть Вас радует мысль о том, что её придётся выполнить только один раз :-).

В результате мы имеем вот такую таблицу:

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

В главную таблицу (лист Store) вставляем новый столбец Supp.SKU.

Далее при помощи функции ВПР (VLOOKUP) сравниваем листы Store и SKU converter, используя для поиска соответствий столбец Our.SKU, а для обновлённых данных – столбец Supp.SKU.

Столбец Supp.SKU заполняется оригинальными кодами производителя.

Замечание: Если в столбце Supp.SKU появились пустые ячейки, то необходимо взять все коды SKU, соответствующие этим пустым ячейкам, добавить их в таблицу SKU converter и найти соответствующий код из таблицы поставщика.

4. Вычисляемые поля

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

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

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

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

В новом всплывающем окне начните с присвоения вычисленному полю имени. в моем случае я назову его Total Order. Общая стоимость заказа это количество, умноженное на цену каждой единицы. Затем я дважды кликнул по первому полю названия (quantity) в списке полей в этом окне.

Чтобы вычислить общую стоимость заказа, я умножил существующее поле цены за единицу (Unit Price) на поле количества (Quantity).

После добавления названия этого поля, я добавлю знак умножения *, а затем дважды кликаю по общему количеству (quantity). Давайте продолжим и нажмите ОК.

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

Если вы не хотите использовать арифметику по двум столбцам, вы также можете ввести свои собственные арифметические значения в вычисляемом поле. Например, если бы я хотел просто добавить 5% налога с продаж для каждого заказа, я мог бы написать следующее в вычисленное поле:

Я умножил общую стоимость заказа на 1.05 для расчета стоимости, включая налог с продаж; вы можете использовать числовые значения наряду с существующими полями.

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

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

Первый этап – выгрузить информацию в программу Excel и привести ее в соответствие с таблицами Excel. Если наши данные находятся в Worde, мы переносим их в Excel и делаем таблицу по всем правилам Excel (даем заголовки столбцам, убираем пустые строки и т.п.).

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

Мы просто создаем сводный отчет на основе данных в нескольких диапазонах консолидации.

Гораздо сложнее сделать сводную таблицу на основе разных по структуре исходных таблиц. Например, таких:

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

Мастер сводных таблиц при таких исходных параметрах выдаст ошибку. Так как нарушено одно из главных условий консолидации – одинаковые названия столбцов.

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

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

Открывается заготовка Сводного отчета со Списком полей , которые можно отобразить.

Покажем, к примеру, количество проданного товара.

Можно выводить для анализа разные параметры, перемещать поля. Но на этом работа со сводными таблицами в Excel не заканчивается: возможности инструмента многообразны.

Консолидация (объединение) данных из нескольких таблиц в одну

Способ 1. С помощью формул 

​ ухода за 2011-2012​ для файлов Риты​ из указанных листов,​ — «Конструктор».​

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

​ этих таблицах идентичны.​ описан выше.​ и выбираем понравившийся​ «Значения». Нужно отметить,​ на кнопку «OK».​

​Нажав правой мышкой,​

​ с шапкой. Нажимаем​ «Лист3» SheetsNames =​ гг., но также​ и Федора. В​ и затем скопировать​На вкладке «Макет» нажимаем​Вручную:​

​ отчеты можно гибко​ соответствии с количестовм​ 20ти файлов Excel?​ должна войти в​ Поэтому мы можем​

​Как видим, создать сводную​

​ вид гистограммы.​ что все арифметические​ Но, пользователи должны​ и, выбрав функцию​ кнопку «Добавить».​ Array(«2011», «2012») Вместо​ должна быть отражена​ итоге в списке​ ее на остальные​ «Промежуточные итоги». Выбираем​Ставим курсор в любом​ настраивать, изменять, обновлять​ отдельных файлов. Листы​ Пробовал сделать все​

Способ 2. Если таблицы неодинаковые или в разных файлах

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

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

​ всю информацию по​ всех таблиц, из​Лист3​ с привязкой к​ три диапазона:​ вправо.​ итоги в заголовке​ В результате становится​У нас есть тренировочная​ Именуете их, например,​ появилось несколько НО:​ столбцы внутри исходной​ отчет.​ двумя способами: обычным​ ячейке. Для того,​ возможны только в​ могут изменить параметры​ конкретному продукту. Она​ которых будем делать​» укажите название того​

​ дате, контрагенту, статье.​Обратите внимание, что в​

1. ​Если листов очень много,​
2. ​ группы».​ видна вкладка «Работа​
3. ​ таблица с данными:​ с File1 по​ формула получается очень​ таблицы, достаточно было​​В ячейке-мишени (там, куда​ ​ способом через кнопку​​ чтобы применить правило​
4. ​ последней области. Как​​ охвата области таблицы.​ ​ появится на новом​​ сводную. Чтобы все​ листа, на который​ Фильтр — по​ данном случае Excel​ то проще будет​​Получается следующий вид отчета:​ ​ со сводными таблицами».​​Каждая строка дает нам​ File20.​ большой и вся​ обновить сводную таблицу.​
5. ​ будет переноситься таблица)​ на ленте, и​ гистограммы для всех​ видим, во время​После этого, таблица превращается​ листе.​

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

​ одной сделке:​​ B9 листа File1​​; слишком много​ сводке появилось поле​

​ = — переходим​ сводных таблиц. Второй​ на кнопку, которая​ проделывали данные манипуляции​ авторастягивающуюся. Она также​ быстро и просто​ ввода последнего диапазона,​2011​Возможно ли как-нибудь​ прописывая для каждого​ немного другую формулу:​ информации.​ (или комбинацию клавиш​в каком магазине были​ вводите формулу связи​

planetaexcel.ru>

Консолидация данных из разных таблиц Excel

Одна из насущных задач, с которыми сталкиваются пользователи, – консолидация данных. Под консолидацией понимается объединение нескольких таблиц в одну. До появления Power Query это была довольно трудоемкая операция, особенно, если процесс требовал автоматизации. Хотя в эксель есть специальная команда Данные → Работа с данными → Консолидация, пользоваться ей не удобно. Мне, по крайней мере. Появление Power Query в корне изменило представление о том, как нужно объединять таблицы.

Рассмотрим пример. В некоторый файл каждый месяц вносится отчет о продажах в формате таблицы Excel. Каждая таблица при этом имеет соответствующее название: Январь_2018, Февраль_2018 и т.д. Необходимо объединить все таблицы книги в одну. Как бы скопировать и вставить одну под другой, создав при этом дополнительный столбец, указывающий, к какой таблице принадлежит конкретная строка. Задача не одноразовая, а с заделом на будущее, поэтому нужно предусмотреть появление в этом файле новых таблиц.

Процесс начинается с запуска пустого запроса: Данные → Получить и преобразовать данные → Создать запрос → Из других источников → Пустой запрос

Затем в строке формул вводим знакомую команду

= Excel.CurrentWorkbook()

Power Query показывает все таблицы в текущей книге.

Их нужно развернуть кнопкой с двумя стрелками в названии поля Content (на скриншоте ниже выделено красным кружком).

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

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

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

Визуально мы наблюдаем и месяц, и год. Но Power Query такое название воспринимает, как текст. Поэтому делаем следующее.

Удалим нижнее подчеркивание. Правой кнопкой мыши по названию столбца Name → Замена значений.

В следующем окне настроек указываем, что меняем _ на пусто, то есть в нижнем поле ничего не указываем.

Подчеркивание удаляется из названия.

Поиск и замена здесь работает так же, как и в обычном Excel.

Далее запускаем команду Преобразование → Столбец «Дата и время» → Дата → Выполнить анализ.

Power Query распознает дату и меняет формат колонки. Мы также переименовываем столбец на Период.

Полученную таблицу можно использовать для анализа данных. Выгрузим ее на лист Excel.Главная → Закрыть и загрузить.

Но что-то пошло не так. Во-первых, внизу таблицы пустая строка; во-вторых, при выгрузке произошла одна ошибка. Обновим запрос (справа от названия запроса значок обновления).

Что-то еще больше пошло не так. Даты исчезли, снизу таблицы добавились новые строки, а количество ошибок уже 19. Спокойствие, только спокойствие! Дело вот в чем.

Помните, на первом шаге мы получили все таблицы из файла? Так ведь и выгруженная таблица – это тоже таблица! Получается, Power Query взял 3 исходных таблицы, обработал, выгрузил на лист Excel и на следующем круге видит уже 4 таблицы!

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

Короче, из запроса нужно исключить таблицу, которая получается на выходе (Запрос1). Есть разные подходы, самый простой – это добавить шаг фильтрации. Выделяем в правой панели первый шаг Источник, открываем фильтр в колонке с названиями, снимаем галку с таблицы Запрос1 → Ok.

Снова выгружаем таблицу в Excel и на этот раз все в порядке.

Сделаем с помощью сводной таблицы маленький отчет по месяцам.

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

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

А вот, как это выглядит при использовании Power Query.

Достаточно два раза нажать кнопку Обновить все (первый раз – для обновления запроса, второй – для сводной таблицы).

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

Вот за это мы так любим Power Query.

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

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

В этом случае встаем в любую из ячеек сводной таблицы (в панели вкладок появится блок Работа со сводными таблицами) и далее выбираем Анализ -> Данные -> Обновить:

Однако же, если в начальные данные добавили новые строки (к примеру, появились данные за новые периоды), то такой способ уже не сработает.

Почему?

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

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

Эту проблему можно решить задав в качестве источника данных не фиксированный диапазон (как в примере выше мы использовали ссылку $A$1:$G$820), а целиком выделить все столбцы, не ограничивая таблицу по высоте (т.е. использовать ссылку $A:$G).

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

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

Использование рекомендуемых сводных таблиц

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

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

1. Перейдите на вкладку «Вставка». Затем нажмите на иконку «Таблица». В появившемся меню выберите второй пункт.

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

1. При наведении на каждый пункт будет доступен предварительный просмотр результата. Так работать намного удобнее.

1. Можно выбрать то, что нравится больше всего.

1. Для вставки выбранного варианта достаточно нажать на кнопку «OK».

1. В итоге вы получите следующий результат.

Настройки Таблицы

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

С помощью галочек в группе Параметры стилей таблиц

можно внести следующие изменения.

— Удалить или добавить строку заголовков

— Добавить или удалить строку с итогами

— Сделать формат строк чередующимися

— Выделить жирным первый столбец

— Выделить жирным последний столбец

— Сделать чередующуюся заливку строк

— Убрать автофильтр, установленный по умолчанию

В видеоуроке ниже показано, как это работает в действии.

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

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

Однако самое интересное – это создание срезов.

Срез – это фильтр, вынесенный в отдельный графический элемент. Нажимаем на кнопку Вставить срез, выбираем столбец (столбцы), по которому будем фильтровать,

и срез готов. В нем показаны все уникальные значения выбранного столбца.

Для фильтрации Таблицы следует выбрать интересующую категорию.

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

Попробуйте сами, как здорово фильтровать срезами (кликается мышью).

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

Сводная таблица из нескольких листов с полным функционалом

​ для получения нужных​​ или столбцов. Они​​ пишем название параметра​ с организаций данных​ бы выходом, если​В «ПРИЕМАХ» дан​: Пардон, предыдущий пример​ FROM ​ объединяя их?​ сообщение об ошибке​ указанных в массиве​ созданные шаги кроме​ два раздела.​ листе, где больше​ функции​ленте​, обобщаются с помощью​Вы также можете скачать​ однострочный заголовок.​ нашем примере, поставим​​Serge​​ программистом. Но я​ позволяет обновлять данные​ (сказывается то, что​ «​Спасибо.​ вида «Provider not​SheetNames​ первого -​​Или можно вызвать функцию,​ нет нужных данных,​СУММ​

​откройте вкладку​​ функции​ интерактивный учебник Создание​На вкладке​ название таблицы «Магазин​

​: :-)​​ не пишу макросы​ в сводной таблице,​ уже 5 часов​lapink2000​lapink2000​ registered», то скорее​листов книги в​Источник (Source)​ нажав на стрелку​ вы можете просто​. Если Excel считает​Анализ сводной таблицы​СУММ​ первой сводной таблицы.​Вставка​

​ 1».​​Serge​ и ничего в​ нужно каждый раз​

​ утра, а я​​: Последнее сообщение -​: Возможно. Вешайте небольшой​

​ всего у вас​​ единую таблицу данных.​​:​ у каждого поля​​ удалить этот лист.​ данные текстом, к​, щелкните стрелку под​. Если Excel считает​Рекомендуемые сводные таблицы​нажмите кнопку​Затем выделяем из​: Ну, строго говоря​ них не понимаю,​ создавать новую, настраивать​ еще не ложился).​​ мое :-)​ (см. правила) пример​ 64-битная версия Excel​ То есть вместо​Теперь мы видим общий​ в окне «Список​ Так проще всего​ ним применяется функция​​ кнопкой​ данные текстом, к​

​Создание сводной таблицы вручную​​Сводная таблица​ списка диапазонов второй​ можно то из​ к сожалению. Поэтому​

​ ее, что очень​​ Вот исправленный вариант.​lapink2000​

​ с данными.​​ или установлена не​ физического копирования-вставки диапазонов​ список всех листов.​ полей сводной таблицы»​ избавиться от сводной​СЧЁТ​Обновить​ ним применяется функция​Щелкните ячейку в диапазоне​.​ диапазон, и в​ любого. Но вот​ пытаюсь решить вопрос​ не удобно, т.к.​гость​: Еще такой вариант​BioVR​ полная версия Office​ с разных листов​ Если в файле​ и настроить нужные​

​ таблицы.​​. Именно почему так​и выберите команду​СЧЁТ​ исходных данных и​В разделе​​ этом же первом​ какая фигня из​​ доступными мне способами.​ у меня данные​: Ээээ… Выспались? (​ с добавлением недостающего​: Возможно — на​​ (нет Access)

Чтобы​ на один мы​ кроме листов с​ параметры.​В сводной таблице Excel​ важно не использовать​Обновить все​. Именно поэтому так​ таблицы.​Выберите данные для анализа​ окне поля пишем​ этого получится -​На данный момент​ обновляются по нескольку​ 😀 )​ поля в обеих​ третьем листе создается​

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

​На вкладке​​установите переключатель​​ название диапазона. Мы​ это второй вопрос​ для решения осталось​​ раз в день.​Как кто-то сказал,​ таблицах (поле не​ 2 сводные таблицы,​ в коде макроса​​ самое в оперативной​ какие-то побочные листы,​ таблицу, пошаговую инструкцию,​ данные из нескольких​​ для полей значений.​Если вам больше не​ разные типы данных​Вставка​Выбрать таблицу или диапазон​ напишем – «Магазин​ ;-)​ понять, почему сводная​Может быть есть​ «Красота — это​​ должно быть пустым)​ на 4-ом создается​ фрагмент:​ памяти компьютера. Потом​ то на этом​ смотрите в статье​ таблиц. В сводной​ Вы можете изменить​ нужна сводная таблица,​ для полей значений.​​нажмите кнопку​.​ 2». Так подписываем​Юрий М​ таблица иногда выдает​ возможность сделать так,​ высшая целесообразность».​гость​ сводная таблица, которая​​Provider=Microsoft.Jet.OLEDB.4.0;​ макрос добавляет новый​

​ шаге наша задача​​ «Сводные таблицы Excel».​

​ таблице установлены разные​​ функцию, которая по​ просто выделите ее​ Вы можете изменить​Рекомендуемые сводные таблицы​

planetaexcel.ru>

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

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

Пошаговая инструкция для создания сводной таблицы:

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

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

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

Для начала встанем в любую ячейку нашей таблицы с данными. Далее в панели вкладок перейдем Вставка -> Сводная таблица:

В данном меню мы можем настроить 2 основных момента — на основе каких данных построить таблицу и где ее разместить.

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

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

Нажимаем OK и перед нами появляется следующий конструктор:

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

В правой части мы видим следующие элементы (список полей и области):

• Список полей;Список всех заголовков столбцов исходной таблицы с данными.
• Фильтры;Добавление дополнительного среза для детализации данных.
• Строки;Поля таблицы вынесенные в строки.
• Столбцы;Поля таблицы вынесенные в столбцы;
• Значения.Вычисляемые числовые данные по соответствующим полям из строк и столбцов (единственный вычисляемый элемент в таблице).

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

Теперь обо всем по порядку.

Создание базы данных для внесения её в сводную таблицу Excel 2003, 2007, 2010

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

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

Шаг 1.

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

Рисунок 1. Создание базы данных для внесения её в сводную таблицу Excel 2003, 2007, 2010

Шаг 2.

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

Рисунок 2. Создание базы данных для внесения её в сводную таблицу Excel 2003, 2007, 2010

Шаг 3.

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

Рисунок 3. Создание базы данных для внесения её в сводную таблицу Excel 2003, 2007, 2010

Добавить свой первый срез в Excel

Чтобы начать работу со срезами, начните, кликнув внутри сводной таблицы. На ленте Excel найдите раздел Работа со сводными таблицами и нажмите Параметры.

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

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

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

После нажатия OK вы увидите окно для каждого из фильтров. Вы можете перетащить эти окна срезов в другие места электронной таблицы.

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

Вот так! Вы вставили срез полей. Теперь вы можете выбрать и кликнуть, чтобы отфильтровать ваши данные. Давайте узнаем больше о том, как использовать и настраивать фильтры.

Выводы

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

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

Изменение функции итогов

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

Изменение порядка сортировки

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

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

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

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