Почему не работает сводная таблица в excel

Сводная таблица Excel не работает фильтр

COM-объект Сводная таблица Excel
Доброго времени суток. Вопрос. Не могу разобраться почему не срабатывает код и выдает ошибку в.

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

Режимы Сводная таблица и Сводная диаграмма в access 2013
куда делись режимы Сводная таблица и Сводная диаграмма в access 2013? Остались только режимы.

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

2635 / 1334 / 256
Регистрация: 25.10.2010
Сообщений: 2,196
Файл с примером покажете?
87844 / 49110 / 22898
Регистрация: 17.06.2006
Сообщений: 92,604
Помогаю со студенческими работами здесь

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

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

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

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

Сводная таблица
Гуру, подскажите! Есть ли ВООБЩЕ готовый/стандартный механизм для создания сводных таблиц на SQL.

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

Или воспользуйтесь поиском по форуму:

СВОДНЫЕ ТАБЛИЦЫ. ПОЧЕМУ В РЕШЕНИЯХ EXCELSOFT ОНИ ПОЧТИ НЕ ИСПОЛЬЗУЮТСЯ

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

Конечно, каждый инструмент предназначен для своих задач. Например, сводные таблицы — идеальны для анализа данных о продажах. Не случайно по-моему 99% примеров применения сводных таблиц — это именно об этом.

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

1. Критичность к исходным данным

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

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

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

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

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

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

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

4. Сводная таблица имеет довольно убогий внешний вид.

Конечно, можно настроить форматы чисел, ширины столбцов. Но при обновлении сводной таблицы все ваши настройки слетят.

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

Конечно проблема решается созданием таблиц-оболочек в которые с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ будут подтягиваться данные из сводных таблиц. Но тогда пропадают основные достоинства сводной — простота и быстрота.

5. Для большинства отчетов придется строить сложную модель данных .

Быстро и просто сводная таблица строится для данных, находящихся на одном листе, например данные о продажах. Но, например, в программе ES: Финансовое планирование и учет управленческий баланс в формате план-факт собирается на основании данных 6 листов . Для сборки такой сводной надо строить модель данных c использованием надстройки Power Pivot. А это уже не совсем простая задача и не за пару минут.

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

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

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

В таблице имеются:

визуальные разделители (пустые строки);
закладки с помощью которых можно быстро перемещаться по таблице, например «Транспортные 2019», «Коммунальные 2019» в колонке Дата;
данные разных типов в одной колонке (например колонка НДС,%);
строка суперфильтра (оранжевая строка);
пустые ячейки в табличной части;

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

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

Как сделать сводные таблицы в Excel: пошаговая инструкция со скриншотами

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

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

Разберёмся, для чего нужны сводные таблицы. На конкретном примере покажем, как их создать, настроить и использовать. В конце расскажем, можно ли делать сводные таблицы в «Google Таблицах».

Зачем нужны сводные таблицы и когда их используют

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

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

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

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

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

Шаг 1

Создаём сводную таблицу

Чтобы сводная таблица сработала корректно, важно соблюсти несколько требований к исходной:

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

Теперь переходим во вкладку «Вставка» и нажимаем на кнопку «Сводная таблица».

Появляется диалоговое окно. В нём нужно заполнить два значения:

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

В нашем случае выделяем весь диапазон таблицы продаж вместе с шапкой. И выбираем «Новый лист» для размещения сводной таблицы — так будет проще перемещаться между исходными данными и сводным отчётом. Жмём «Ок».

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

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

Шаг 2

Настраиваем сводную таблицу и получаем результат

В верхней части панели настроек находится блок с перечнем возможных полей сводной таблицы. Поля взяты из заголовков столбцов исходной таблицы: в нашем случае это «Марка, модель», «Цвет», «Год выпуска», «Объём», «Цена», «Дата продажи», «Продавец».

Нижняя часть панели настроек состоит из четырёх областей — «Значения», «Строки», «Столбцы» и «Фильтры». У каждой области своя функция:

«Значения»

Настроить сводную таблицу можно двумя способами:

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

Первый вариант не самый удачный: Excel редко ставит данные так, чтобы с ними было удобно работать, поэтому сводная таблица получается неинформативной. Остановимся на втором варианте — он предполагает индивидуальные настройки для каждого отчёта.

В случае с нашим примером нужно, чтобы сводная таблица отразила ФИО менеджеров по продаже, проданные автомобили и их цены. Остальные поля — технические характеристики авто и дату продажи — можно будет использовать для фильтрации.

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

После этого в левой части листа появится первый блок сводной таблицы: фамилии менеджеров по продажам.

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

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

Определяем, какая ещё информация понадобится для отчётности. В нашем случае — цены проданных автомобилей и их количество.

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

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

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

Шаг 3

Настраиваем фильтры сводной таблицы

Чтобы можно было фильтровать информацию сводной таблицы, нужно перенести требуемые поля в область «Фильтры».

В нашем примере перетянем туда все поля, не вошедшие в основной состав сводной таблицы: объём, дату продажи, год выпуска и цвет.

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

В блоке фильтров нажмём на стрелку справа от поля «Год выпуска»:

В появившемся окне уберём галочку напротив параметра «Выделить все» и поставим её напротив параметра «2017». Закроем окно.

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

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

Шаг 4

Проводим дополнительные вычисления

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

Кликнем правой кнопкой на любое значение цены в таблице. Выберем параметр «Дополнительные вычисления», затем «% от общей суммы».

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

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

Чтобы снова раскрыть данные об автомобилях — нажимаем +.

Чтобы значения снова выражались в рублях — через правый клик мыши возвращаемся в «Дополнительные вычисления» и выбираем «Без вычислений».

Шаг 5

Обновляем данные сводной таблицы

Предположим, в исходную таблицу внесли ещё две продажи последнего дня квартала.

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

Переходим на лист сводной таблицы. Во вкладке «Анализ сводной таблицы» нажимаем кнопку «Изменить источник данных».

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

После этого данные в сводной таблице меняются автоматически: у менеджера Трегубова М. вместо восьми продаж становится десять.

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

Например, поменяем цены двух автомобилей в таблице с продажами.

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

Теперь у менеджера Соколова П. изменились данные в столбце «Цена, руб.».

Как использовать сводные таблицы в «Google Таблицах»? Нужно перейти во вкладку «Вставка» и выбрать параметр «Создать сводную таблицу». Дальнейший ход действий такой же, как и в Excel: выбрать диапазон таблицы и лист, на котором её нужно построить; затем перейти на этот лист и в окне «Редактор сводной таблицы» указать все требуемые настройки. Результат примет такой вид:

Другие материалы Skillbox Media для менеджеров

Руководство: как сделать ВПР в Excel и перенести данные из одной таблицы в другую
Статья с разбором диаграммы Ганта — что должен знать каждый менеджер
Подборка советов, как превратить хороший проект в великий, из книги Коллинза Good to Great
Рассказ о модели VUCA и о том, как она помогает процветать в хаосе
Подборка одиннадцати типичных ошибок при создании презентации

Исходная таблица — данные, которые сводная таблица собирает, группирует и формирует в отчёт.