Создание сложных таблиц в excel
Сводные таблицы - один из самых замечательных инструментов в Excel. Но до сих пор, к сожалению, ни одна из версий Excel не умеет "на лету" делать такой простой и нужной вещи как построение сводной по нескольким исходным диапазонам данных, находящимся, например, на разных листах или в разных таблицах:
Прежде, чем начать давайте уточним пару моментов. Априори я полагаю, что в наших данных выполняются следующие условия:
- Таблицы могут иметь любое количество строк с любыми данными, но обязательно - одинаковую шапку.
- На листах с исходными таблицами не должно быть лишних данных. Один лист - одна таблица. Для контроля советую использовать сочетание клавиш Ctrl + End , которое перемещает вас на последнюю использованную ячейку листа. В идеале - это должна быть последняя ячейка таблицы с данными. Если при нажатии на Ctrl + End выделяется какая-либо пустая ячейка правее или ниже таблицы - удалите после таблицы эти пустые столбцы справа или строки снизу и сохраните файл.
Способ 1. Сборка таблиц для сводной с помощью Power Query
Начиная с 2010 версии для Excel существует бесплатная надстройка Power Query, которая умеет собирать и трансформировать любые данные и отдавать их потом как источник для построения сводной таблицы. Решить нашу задачу с помощью этой надстройки совсем несложно.
Сначала создадим новый пустой файл в Excel - в него будет происходить сборка и в нем же потом будет создаваться сводная таблица.
Затем на вкладке Данные (если у вас Excel 2016 или новее) или на вкладке Power Query (если у вас Excel 2010-2013) выберем команду Создать запрос - Из файла - Excel (Get Data - From file - Excel) и укажем исходный файл с таблицами, которые надо собрать:
В появившемся окне выберем любой лист (не принципиально какой именно) и внизу жмем кнопку Изменить (Edit) :
Поверх Excel должно открыться окно редактора запросов Power Query. В правой части окна на панели Параметры запроса удалим все автоматически созданные шаги кроме первого - Источник (Source) :
Теперь мы видим общий список всех листов. Если в файле кроме листов с данными есть еще какие-то побочные листы, то на этом шаге наша задача - отобрать только те листы, с которых нужно загрузить информацию, исключив все остальные с помощью фильтра в шапке таблицы:
Удалим все столбцы, кроме колонки Data, щелкнув по заголовку столбца правой кнопкой мыши и выбрав команду Удалить другие столбцы (Remove other columns) :
Затем можно развернуть содержимое собранных таблиц, щелкнув по двойной стрелке в верхней части столбца (флажок Использовать исходное имя столбца как префикс можно при этом отключить):
Если вы всё сделали правильно, то на этом моменте должны увидеть содержимое всех таблиц, собранных друг под другом:
Осталось поднять первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) на вкладке Главная (Home) и удалить попавшие в данные повторяющиеся шапки таблиц с помощью фильтра:
Сохраним всё проделанное с помощью команды Закрыть и загрузить - Закрыть и загрузить в. (Close & Load - Close & Load to. ) на вкладке Главная (Home) , а в открывшемся окне выберем опцию Только подключение (Connection Only) :
Всё. Осталось только построить сводную. Для этого идём на вкладку Вставка - Сводная таблица (Insert - Pivot Table) , выбирыем опцию Использовать внешний источник данных (Use external data source) , а затем, нажав кнопку Выбрать подключение, наш запрос. Дальнейшее создание и настройка сводной происходит совершенно стандартным образом путем перетаскивания нужных нам полей в области строк, столбцов и значений:
Если в будущем изменятся исходные данные или добавится еще несколько листов-магазинов, то достаточно будет обновить запрос и нашу сводную с помощью команды Обновить все на вкладке Данные (Data - Refresh All) .
Способ 2. Объединяем таблицы SQL-командой UNION в макросе
Еще одно решение нашей задачи представлено вот таким макросом, который создает набор данных (cache) для сводной таблицы, используя команду UNION языка запросов SQL. Эта команда объединяет таблицы со всех указанных в массиве SheetNames листов книги в единую таблицу данных. То есть вместо физического копирования-вставки диапазонов с разных листов на один мы делаем то же самое в оперативной памяти компьютера. Потом макрос добавляет новый лист с заданным именем (переменная ResultSheetName) и создает на нем полноценную(!) сводную на основе собранного кэша.
Чтобы воспользоваться макросом используйте кнопку Visual Basic на вкладке Разработчик (Developer) или сочетание клавиш Alt + F11 . Затем вставляем новый пустой модуль через меню Insert - Module и копируем туда следующий код:
Готовый макрос потом можно запустить сочетанием клавиш Alt + F8 или кнопкой Макросы на вкладке Разработчик (Developer - Macros) .
Минусы такого подхода:
- Данные не обновляются, т.к. кэш не имеет связи с исходными таблицами. При изменении исходных данных надо запустить макрос еще раз и построить сводную заново.
- При изменении количества листов необходимо правки в код макроса (массив SheetNames).
Зато в итоге получаем самую настоящую полноценную сводную таблицу, построенную по нескольким диапазонам с разных листов:
И скачайте и установите бесплатный движок обработки данных из Access с сайта Microsoft - Microsoft Access Database Engine 2010 Redistributable
Способ 3. Мастер консолидации сводных таблиц из старых версий Excel
Этот способ немного устарел, но тоже стоит упоминания. Формально говоря, во всех версиях до 2003 включительно в мастере сводных таблиц была опция "построить сводную по нескольким диапазонам консолидации". Однако, отчет, построенный таким образом, к сожалению, будет лишь жалким подобием настоящей полноценной сводной и не поддерживает многие "фишки" обычных сводных таблиц:
В такой сводной нет заголовков столбцов в списке полей, нет гибкой настройки структуры, ограничен набор используемых функций и, в общем и целом, все это слабо похоже на сводную таблицу. Возможно именно поэтому начиная с 2007 года Microsoft эту функцию убрали из стандартного диалога при создании отчетов сводных таблиц. Теперь эта возможность доступна только через настраиваемую кнопку Мастер сводных таблиц (Pivot Table Wizard) , которую при желании можно добавить на панель быстрого доступа через Файл - Параметры - Настройка панели быстрого доступа - Все команды (File - Options - Customize Quick Access Toolbar - All Commands) :
После нажатия на добавленную кнопку нужно выбрать на первом шаге мастера соответствующую опцию:
А затем в следующем окне выделить по очереди каждый диапазон и добавить его в общий список:
Но, повторюсь, это не полноценная сводная, так что не ждите от нее слишком многого. Подобный вариант могу рекомендовать только в очень простых случаях.
Ссылки по теме
если мне не нужно собирать данные из разных источников как мне сделать точно такой же конструктор чтобы я мог перетаскивать шапки и у меня менялся отчет
Обожаю такие формулировки вопросов. Куда перетаскивать? Какой конструктор? Если вы про создание сводной таблицы по одному диапазону без консолидации, то см. первую ссылку в Ссылки по теме .
Возможно изменить функцию так, что-бы имена листов вычислялись из книги (каждый раз писать новые имена в макрос не удобно)
Спасибо!
Добрый день, Николай!
Спасибо за Ваш сайт и за данную статью.
на одном из форумов нашел вот такое решение с листами .
http://www.excelworld.ru/forum/10-17185-1
Заменить в макросе:
'массив имен листов с исходными таблицами
SheetsNames = Array("a1", "a2", "a3", "a4";)
Dim s As String
'Ввод массива имен листов с исходными таблицами
s = InputBox("Имена листов через запятую";)
s = Replace(s, ", ", ",";) 'Удаляем возможные пробелы после запятой
SheetsNames = Split(s, ",";)
Добрый день! Подскажите пожалуйста как поменять столбцы, в вашем файле берет столбик А:К, а мне например нужен К:Р )
Здавствуйте я использовал ваш пример в своих таблицах , все прекрасно но
есть нюанс\\ Дело в том что этот макрос не подсчитывает строки ,если их больше 50.000
(у меня тоже 4 листа как в примере но каждый состоит из 110.000-120.000 строк )
Может я что-то упускаю.
Подскажите если не трудно.
В коде этого ограничения точно нет. Возможно, это уже нехватка ресурсов компьютера (ограничение кэша Excel 2010 версии - 2 Гб). Точнее сказать, не видя вашего примера, сложно.
Может вам уже бесплатную надстройку PowerPivot использовать - при таких размерах таблиц как раз она замечательно будет работать. И макросов никаких не надо.
Добрый день! Подскажите, пожалуйста, а как в PowerPivot можно объединить данные из двух источников данных? (у меня данные в sharepoint, отдельно я их из списков могу получить, а как объединить в один источник данных для построения сводной - не могу найти.
как я установила опытным путем, ограничение 65536, т.е. кол-во строк в старом Excel
причем что характерно: если строк будет больше 65536, то брать в сводную таблицу будет не первые 65536, а например, первые 4000. или 9000.
т.е. получается даже не обрыв таблицы, а совсем что-то неадекватное.
дело точно не в кэше Excel, потому что я разбила каждую из своих 12 таблиц по 85 тыс.строк на две таблицы по 40-45 тыс строк.и стало все нормально.
т.е. 12 таблиц по 85 тыс.строк не сводились.
а 24 таблицы по 45 тыс.строк сводятся абсолютно корректно. кэша хватает
Николай, вещь полезная, но при использовании данного варианта становится невозможно использование СРЕЗОВ. Оптимизация кода не планируется?
Спасибо за наводку. Подумаем
Я подошел к решению этой проблемы с другой стороны. Я сделал макрос который на новом листе формирует таблицу, которая содержит дание с других листов независимо от колиства строк в этих таблицах, а также выделяет новою таблицу. Осталось только вставить сводную таблицу, но проблема заключается в том как задать диапазон даных для сводной таблици, так как количество строк может менятся. Я только начал изучать VBA и ище многого не усвоил, помогите пожайлуста.
Включите макрорекордер и создайте простую сводную таблицу. Затем посмотрите код VBA, который получился и замените в нем фиксированный диапазон с исходными данными на:
Вы можете использовать по-старинке так называемые "динамические диапазоны" (с формулой СМЕЩ, англ. OFFSET), но сейчас уже следует использовать "умные таблицы", которые автоматически расширяют диапазон. Соответственно, необходимо ссылаться на диапазон данных по имени этого диапазона, а не по адресу, и на таблицу - по ее имени.
Если у вас в столбце одновременно есть и текстовые и числовые значения, то что вы планируете увидеть в сводной?
хотелось бы видеть и текстовые и числовые данные.Вообще элементы как правило имеют числовые значения, но некоторые - тектовые и они просто не попадают в сформированную сводную таблицу
Тогда, боюсь, единственный вариант - форматировать все как текст. Поля смешанного типа тут не поддерживаются.
Макрос допускал ошибку на Win 8 x64, Office 2013 x64.
Помогла замена " Microsoft.Jet.OLEDB.4.0 " на " Microsoft.ACE.OLEDB.12.0 "
Спасибо за уточнение. На 64-битном Office мне его негде было потестировать
Добрый день, помогите пожалуйста:)
Как изменить код так, что-бы сформированный кэш из нескольких листов выводился на скрытый лист "база" без формирования сводной, и каждый раз при запуске макроса информация на листе "база" заменялась
Дело в том, что при выводе данных на отдельный лист, я смогу создать несколько сводных таблиц (т.е. в разных разрезах) из диапазона данных на листе, и при запуске макроса нет необходимости опять настраивать таблицы, а просто их обновить
Спасибо
Выдает ошибку в SheetNames(1) Подскажите пожалуйста что я сделал неправильно? Ведь у PetrovichOskol видимо все получилось. С уважением. Юрий. |
Подскажите пожалуйста, вообще нет возможности сделать обновление сводной таблицы, которая была построена Вашим макросом?
Попробовал решить задачу с обновлением таблицы без повторного ее создания. Да и просят, вижу, многие.
Постарался не отходить от Вашего стиля, вынес часть кода по созданию объекта ADODB.Recordset в отдельную функцию GetData().
И добавил процедуру обновления кэша: UpdateCache() :
- получил ссылку на уже созданную сводную таблицу,
- подменил ей источник данных (PivotCache.Recordset),
- обновил саму таблицу.
Этого оказалось достаточно. При этом текущее состояние таблицы не нарушается, обновляются только данные.
Сравните:
Н.П.: "UNION ALL SELECT * FROM [" & SheetsNames(i) & "$]"
К.Л.: "UNION ALL SELECT * FROM `" & arrFiles(i) & "`.[" & strSheet & "$]"
Различие вот здесь: у меня arrFiles(2) возвращает путь к файлу К.Л. --> C:\Users\User1\Downloads\KL_PivotWkbks\DB-Manitoba.xls
У Николая путь к файлу опущен.
формат строки подключения вот здесь:
www.connectionstrings.com/excel-2003/
При указании имени листа Excel надо добавлять знак "$" и оборачивать его в квадратные скобки "[" "]", как это показано выше.
Еще одно добавление для тех, кто будет читать, по поводу установки Microsoft Access. Эта программа есть не у всех (в стандартный комплект не входит), но здесь она и не нужна, нужен только OLEDB провайдер данных (Microsoft.Jet.OLEDB.4.0 или его аналоги под конкретную среду).
Владимир Безносюк, чуть выше (05.07.2013 11:58:49), спрашивает:
"Как изменить код так, что-бы сформированный кэш из нескольких листов выводился на скрытый лист "база" без формирования сводной, и каждый раз при запуске макроса информация на листе "база" заменялась
Дело в том, что при выводе данных на отдельный лист, я смогу создать несколько сводных таблиц (т.е. в разных разрезах) из диапазона данных на листе, и при запуске макроса нет необходимости опять настраивать таблицы, а просто их обновить".
Вероятно, правильным будет использовать один, главный, кэш для всех созданных таблиц.
Пример изменения кэша для всех сводных таблиц книги:
В результате у всех созданных Вами сводных таблиц будет единый кэш. Правда, сводные таблицы, использующие его, будут иметь некоторые общие черты, такие как вычисляемые элементы и сгруппированные поля (возможность разделить кэш всё равно существует, но это другая тема).
"Работать" такая книга будет намного лучше.
Здравствуйте, друзья. Думаю, каждому, кто работал в Эксель, попадались таблицы со структурой данных. Группировка в Эксель, чаще всего, применяется, когда есть организация со структурой, и мы оцениваем некоторые показатели согласно этой структуры. Например, продажи по Компании в целом, по региональным представительствам, по менеджерам. Посмотрите на картинке, как это может быть без группировки:
На рисунке детальный пример отчета о продажах по структурным подразделениям подневно. А что если нужно предоставить руководителям отчет в разрезе только лишь департаментов и помесячно, без детализации? Чтобы было вот так:
Согласитесь, такой вид таблицы более нагляден и показателен для анализа работы Компании в целом. Но как получить такую таблицу достаточно быстро, не скрывая и не удаляя ячейки? Очень просто, задайте структуру документа.
На рисунке выше я привел уже структурированный документ, с маркерами группы. В таблице сгруппированы менеджеры по регионам и регионы в компании, а так же, дни недели в месяцы. Чтобы скрыть какую-то отдельную группу, кликните на значке «минус» в итоговой строке этой группы». Чтобы раскрыть – на значке «плюс». Чтобы отобразить какой-то определенный уровень – нажмите на его номер в верхнем левом углу листа.
Обратите внимание, данные сгруппированы в несколько уровней. Каждый последующий вложен в предыдущий. Всего Эксель позволяет создать до восьми уровней в строках и восьми – в столбцах.
Есть два способа создать структуру листа: автоматический и ручной.
Автоматическое создание структуры в Excel
Программа может попытаться создать структуру автоматически. Для этого нажмите на ленте: Структура – Группировать – Создать структуру.
Если у Вас правильно и логично записаны формулы суммирования, структура будет создана правильно. У меня эта функция часто срабатывает правильно, поэтому сначала я пробую именно автоматическое создание. Структура из примера отлично создалась автоматически.
Ручное создание структуры
Создание структуры вручную позволяет держать весь процесс под контролем и гарантированно получить правильный результат. Чтобы структурировать лист вручную, выполните такие действия:
- Оформите и наполните таблицу, создайте итоговые строки и столбцы
- Кликните на маленькой пиктограмме со стрелкой в нижнем правом углу ленточной группы Данные – Структура. В открывшемся окне настройте расположение строк и столбцов итогов
- Выделите строки или столбцы, которые нужно группировать (не выделяйте итоговые). Делайте это с каждой группой по очереди, начиная с самых «глубоких». В моем примере это фамилии менеджеров.
- Нажмите на ленте Структура – Группировать (ли нажмите Alt+Shift+стрелка вправо). Будет создана группировка
- Повторяйте п.3-4 до полного структурирования данных. Повторюсь, начинайте с подчиненных групп, потом переходите на уровень выше. Т.е. в таблице из примера мы поочередно сгруппируем менеджеров в филиалы, потом филиалы в компанию.
Когда Вы вставляете новые строки и столбцы в структурированную таблицу – будьте осторожны. Если, например, добавляется новый менеджер в группу, вставляйте строку где-то между сгруппированными строками, не первым элементом группы и не последним. В противном случае, новая строка не впишется в структуру.
При копировании диапазона ячеек, скрытые строки и столбцы не копируются. Но когда они скрыты группировкой – все равно копируются. Чтобы выделить только данные, отображенные на экране, выделите нужный диапазон и нажмите F5. В открывшемся окне выберите Выделить – Только видимые ячейки. Теперь ячейки, скрытые группировкой, не будут скопированы.
Если Вам больше не нужна структура – удалите ее. Для этого выполните на ленте Данные – Структура – Разгруппировать – Удалить структуру.
Группированные данные, структурированное рабочее пространство – не только добавляют практичности Вашим таблицам, это правило хорошего тона, когда одну и ту же таблицу можно легко посмотреть как в развернутом виде, так и в детальном.
Вот и все, тренируйтесь и применяйте на практике структурирование данных в Экселе. Приглашаю Вас комментировать изложенный материал, задавайте Ваши вопросы и делитесь опытом!
Если у вас имеется большая и сложная таблица на небольшом мониторе (например, ноутбука), то в процессе работы с ней часто возникает желание скрыть часть ненужных в текущий момент данных (строк или столбцов). Основные способы для решения этой проблемы — скрытие строк и группировка. Вот о последнем способе и пойдет речь далее.
Предположим, что мы работаем вот с такой сложной многоуровневой таблицей с данными:
Обратите внимание на то, что в таблице ясно и четко прослеживаются вложенные друг в друга уровни данных. Такую структуру часто можно увидеть в:
- бюджетировании — статьи доходов/расходов группируются в блоки (cost centers) или по географическому признаку (страна-край-город)
- управлении проектами — этапы проектов разбиты обычно на более мелкие подзадачи и действия
- строительных сметах — похожим образом обычно расписываются расчеты расхода материалов и их стоимости при строительстве
- и т.д. — дальше придумайте сами.
Делать такую группировку вручную весьма мучительно, вследствие неудобности и ограниченности средств группировки самого Excel. Поэтому, столкнувшись как-то с подобной задачей в одном из своих проектов, я написал макрос для автоматической группировки подобных списков, которым и хочу тут поделиться. Чтобы применить его к вашему списку, проделайте следующие действия:
Нажмите сочетание клавиш ALT+F11, чтобы открыть редактор Visual Basic. В нем выберите в меню команду Insert — Module, чтобы вставить новый модуль и скопируйте туда текст макроса:
При необходимости, текст можно слегка подкорректировать под ваши особенности, а именно изменить:
- FIRST_ROW — номер первой строки списка, начиная с которой пойдет группировка. Если у вас шапка не из одной строки или над таблицей есть данные — меняйте.
- FIRST_COLUMN — номер первого столбца списка, с которого начинается анализ и группировка. Если слева от вашей таблицы есть еще колонки, то эту константу также нужно изменить.
- NUMBER_OF_LEVELS — количество уровней (столбцов) для анализа. В приведенном выше примере мы хотим проанализировать три первых столбца, поэтому значение этой константы =3
Важно! Макрос предполагает, что:
- Уровни заполняются по порядку, т.е., например, уровень 3 не может быть написан, если ему не предшествовал уровень 2.
- В первом столбце списка в последней строке должно быть слово Конец, которое необходимо, чтобы макрос понял, где заканчивается список и пора остановиться:
Чтобы запустить добавленный макрос для списка на текущем листе, нажмите сочетание клавиш ALT+F8, выберите в списке наш макрос Multilevel_Group и нажмите кнопку Выполнить (Run).
Ссылки по теме
- Что такое макросы, как их создавать, куда копировать текст макроса на Visual Basic
- Скрытие/отображение ненужных строк и столбцов
Таблицы в Excel представляют собой ряд строк и столбцов со связанными данными, которыми вы управляете независимо друг от друга.
Работая в Excel с таблицами, вы сможете создавать отчеты, делать расчеты, строить графики и диаграммы, сортировать и фильтровать информацию.
Если ваша работа связана с обработкой данных, то навыки работы с таблицами в Эксель помогут вам сильно сэкономить время и повысить эффективность.
Как работать в Excel с таблицами. Пошаговая инструкция
- Данные должны быть организованы в строках и столбцах, причем каждая строка должна содержать информацию об одной записи, например о заказе;
- Первая строка таблицы должна содержать короткие, уникальные заголовки;
- Каждый столбец должен содержать один тип данных, таких как числа, валюта или текст;
- Каждая строка должна содержать данные для одной записи, например, заказа. Если применимо, укажите уникальный идентификатор для каждой строки, например номер заказа;
- В таблице не должно быть пустых строк и абсолютно пустых столбцов.
1. Выделите область ячеек для создания таблицы
Выделите область ячеек, на месте которых вы хотите создать таблицу. Ячейки могут быть как пустыми, так и с информацией.
На вкладке “Вставка” нажмите кнопку “Таблица”.
3. Выберите диапазон ячеек
В всплывающем вы можете скорректировать расположение данных, а также настроить отображение заголовков. Когда все готово, нажмите “ОК”.
4. Таблица готова. Заполняйте данными!
Поздравляю, ваша таблица готова к заполнению! Об основных возможностях в работе с умными таблицами вы узнаете ниже.
Форматирование таблицы в Excel
Для настройки формата таблицы в Экселе доступны предварительно настроенные стили. Все они находятся на вкладке “Конструктор” в разделе “Стили таблиц”:
Если 7-ми стилей вам мало для выбора, тогда, нажав на кнопку, в правом нижнем углу стилей таблиц, раскроются все доступные стили. В дополнении к предустановленным системой стилям, вы можете настроить свой формат.
Помимо цветовой гаммы, в меню “Конструктора” таблиц можно настроить:
- Отображение строки заголовков – включает и отключает заголовки в таблице;
- Строку итогов – включает и отключает строку с суммой значений в колонках;
- Чередующиеся строки – подсвечивает цветом чередующиеся строки;
- Первый столбец – выделяет “жирным” текст в первом столбце с данными;
- Последний столбец – выделяет “жирным” текст в последнем столбце;
- Чередующиеся столбцы – подсвечивает цветом чередующиеся столбцы;
- Кнопка фильтра – добавляет и убирает кнопки фильтра в заголовках столбцов.
Как добавить строку или столбец в таблице Excel
Даже внутри уже созданной таблицы вы можете добавлять строки или столбцы. Для этого кликните на любой ячейке правой клавишей мыши для вызова всплывающего окна:
- Выберите пункт “Вставить” и кликните левой клавишей мыши по “Столбцы таблицы слева” если хотите добавить столбец, или “Строки таблицы выше”, если хотите вставить строку.
- Если вы хотите удалить строку или столбец в таблице, то спуститесь по списку в сплывающем окне до пункта “Удалить” и выберите “Столбцы таблицы”, если хотите удалить столбец или “Строки таблицы”, если хотите удалить строку.
Как отсортировать таблицу в Excel
Для сортировки информации при работе с таблицей, нажмите справа от заголовка колонки “стрелочку”, после чего появится всплывающее окно:
В окне выберите по какому принципу отсортировать данные: “по возрастанию”, “по убыванию”, “по цвету”, “числовым фильтрам”.
Как отфильтровать данные в таблице Excel
Для фильтрации информации в таблице нажмите справа от заголовка колонки “стрелочку”, после чего появится всплывающее окно:
- “Текстовый фильтр” отображается когда среди данных колонки есть текстовые значения;
- “Фильтр по цвету” также как и текстовый, доступен когда в таблице есть ячейки, окрашенные в отличающийся от стандартного оформления цвета;
- “Числовой фильтр” позволяет отобрать данные по параметрам: “Равно…”, “Не равно…”, “Больше…”, “Больше или равно…”, “Меньше…”, “Меньше или равно…”, “Между…”, “Первые 10…”, “Выше среднего”, “Ниже среднего”, а также настроить собственный фильтр.
- В всплывающем окне, под “Поиском” отображаются все данные, по которым можно произвести фильтрацию, а также одним нажатием выделить все значения или выбрать только пустые ячейки.
Если вы хотите отменить все созданные настройки фильтрации, снова откройте всплывающее окно над нужной колонкой и нажмите “Удалить фильтр из столбца”. После этого таблица вернется в исходный вид.
Как посчитать сумму в таблице Excel
Для того чтобы посчитать сумму колонки в конце таблицы, нажмите правой клавишей мыши на любой ячейке и вызовите всплывающее окно:
В списке окна выберите пункт “Таблица” => “Строка итогов”:
Внизу таблица появится промежуточный итог. Нажмите левой клавишей мыши на ячейке с суммой.
В выпадающем меню выберите принцип промежуточного итога: это может быть сумма значений колонки, “среднее”, “количество”, “количество чисел”, “максимум”, “минимум” и т.д.
Как в Excel закрепить шапку таблицы
Таблицы, с которыми приходится работать, зачастую крупные и содержат в себе десятки строк. Прокручивая таблицу “вниз” сложно ориентироваться в данных, если не видно заголовков столбцов. В Эксель есть возможность закрепить шапку в таблице таким образом, что при прокрутке данных вам будут видны заголовки колонок.
Для того чтобы закрепить заголовки сделайте следующее:
- Перейдите на вкладку “Вид” в панели инструментов и выберите пункт “Закрепить области”:
- Выберите пункт “Закрепить верхнюю строку”:
- Теперь, прокручивая таблицу, вы не потеряете заголовки и сможете легко сориентироваться где какие данные находятся:
Как перевернуть таблицу в Excel
Представим, что у нас есть готовая таблица с данными продаж по менеджерам:
На таблице сверху в строках указаны фамилии продавцов, в колонках месяцы. Для того чтобы перевернуть таблицу и разместить месяцы в строках, а фамилии продавцов нужно:
- Выделить таблицу целиком (зажав левую клавишу мыши выделить все ячейки таблицы) и скопировать данные (CTRL+C):
- Переместить курсор мыши на свободную ячейку и нажать правую клавишу мыши. В открывшемся меню выбрать “Специальная вставка” и нажать на этом пункте левой клавишей мыши:
- В открывшемся окне в разделе “Вставить” выбрать “значения” и поставить галочку в пункте “транспонировать”:
- Готово! Месяцы теперь размещены по строкам, а фамилии продавцов по колонкам. Все что остается сделать – это преобразовать полученные данные в таблицу.
В этой статье вы ознакомились с принципами работы в Excel с таблицами, а также основными подходами в их создании. Пишите свои вопросы в комментарии!
В этой статье описывается создание и использование двух входных таблиц в Microsoft Excel. Эти таблицы позволяют проверить, как изменения в двух переменных влияют на одну формулу.
Дополнительная информация
При создании таблицы с двумя входными данными в диалоговом окне "Таблица" указываются входные ячейки в полях "Ячейка ввода строк" и "Ячейка ввода столбца".
В Microsoft Office Excel 2007 диалоговое окно "Таблица" называется диалогом таблицы данных.
Чтобы создать простую таблицу с двумя входными данными, выполните следующие действия.
Создайте новую книгу.
В ячейках B15:B19 введите следующие данные:
В ячейках C14:G14 введите следующие данные:
В ячейке B14 введите следующую формулу:
=A14*2+A15
A14 — это входная ячейка столбца (которая заменяет значения 1, 2, 3, 4 и 5), а A15 — ячейку ввода строки (которая заменяет значения 6, 7, 8, 9 и 10). Эти входные ячейки должны находиться за пределами таблицы. они могут содержать или не содержать данные. Так как эта таблица настроена в ячейках B14:G19 и так как A14 и A15 находятся за пределами таблицы, они являются допустимыми входными и входными ячейками строк.
В меню "Данные" щелкните "Таблица".
В Excel 2007 перейдите на вкладку "Данные", выберите "Что если" и "Таблица данных".
В поле "Ячейка ввода строки" введите A15. В поле "Ячейка ввода столбца" введите A14.
Вы увидите следующие результаты:
В качестве примера используйте ячейку C15. Фактические значения, используемые в формуле, — это ячейки B15:B19 (входные ячейки строки) и ячейки C15:G14 (ячейки ввода столбца). Формула со значениями будет иметь значение 1*2+6 (всего 8). Excel внутренне подставляет значения в ячейках ввода строк и столбцов в формулу в ячейке B14.
0 отображается в ячейке B14, хотя ячейка B14 не является числом формата. Чтобы дублировать пустое значение в ячейке B14, выполните следующие действия.
Ссылки
Дополнительные сведения об использовании таблиц данных см. в следующих статьях:
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше
Сводная таблица — это эффективный инструмент для вычисления, сведения и анализа данных, который упрощает поиск сравнений, закономерностей и тенденций.
Работа с помощью стеблей немного отличается в зависимости от того, какую платформу вы используете для Excel.
Создание в Excel для Windows
Выделите ячейки, на основе которых вы хотите создать сводную таблицу.
Примечание: Данные должны быть уместными в столбцах с одной строкой заглавных колонок.
На вкладке Вставка нажмите кнопку Сводная таблица.
Примечание: Если выбрать добавить эти данные в модель данных, таблица или диапазон, используемые для этой таблицы, будут добавлены в модель данных книги. Подробнее.
Выберите, куда следует поместить отчет. Выберите новый таблицу, чтобы разместить ее на новом или на существующем, и уберем место, куда нужно ввести новую таблицу.
Сvotttables from other sources
Нажимая стрелку вниз на кнопке, вы можете выбрать другие источники для своей pivotttable. Кроме использования существующей таблицы или диапазона, для заполнения таблицы можно выбрать еще три источника.
Примечание: В зависимости от настроек вашей организации на кнопке может отображаться название организации. Например, "Из Power BI (Майкрософт)"
Из внешнего источника данных
Из модели данных
Используйте этот параметр, если книга содержит модель данных, и вы хотите создать таблицу на нескольких таблицах, улучшить ее с помощью настраиваемых мер или работать с очень большими наборами данных.
С Power BI
Используйте этот вариант, если ваша организация использует Power BI и вы хотите найти и подключиться к поддерживаемым облачным наборам данных, к которые у вас есть доступ.
Настройка сводной таблицы
Чтобы добавить поле в сводную таблицу, установите флажок рядом с именем поля в области Поля сводной таблицы.
Примечание: Выбранные поля добавляются в области по умолчанию: не числовые поля добавляются в строки, иерархии дат и времени — в столбцы, а числовые — в значения.
Чтобы переместить поле из одной области в другую, перетащите его в целевую область.
По умолчанию поля pivotttable, которые находятся в области значений, отображаются как СУММ. Если Excel данные интерпретируются как текст, они отображаются как счёт. Именно поэтому так важно не смешивать типы данных для полей значений. Вы можете изменить вычисление по умолчанию, щелкнув стрелку справа от имени поля, а затем выбрав Параметры значения.
Затем измените функцию в разделе Операция. Обратите внимание на то, что при изменении метода вычисления Excel автоматически добавляет его название в раздел Пользовательское имя (например, "Сумма по полю имя_поля"), но вы можете изменить имя. Чтобы изменить числовой формат для всего поля, нажмите кнопку Числовой формат.
Совет: Так как при изменении способа вычисления в разделе Операция обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно нажать кнопку Найти и заменить (CTRL+H), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.
Дополнительные вычисления
Значения можно также выводить в процентах от значения поля. В приведенном ниже примере мы изменили сумму расходов на % от общей суммы.
Вы можете настроить такие параметры в диалоговом окне Параметры поля значений на вкладке Дополнительные вычисления.
Отображение значения как результата вычисления и как процента
Просто перетащите элемент в раздел Значения дважды, а затем настройте параметры Операция и Дополнительные вычисления для каждой из копий.
Данные должны быть представлены в виде таблицы, в которой нет пустых строк или столбцов. Рекомендуется использовать таблицу Excel, как в примере выше.
Таблицы — это отличный источник данных для сводных таблиц, так как строки, добавляемые в таблицу, автоматически включаются в сводную таблицу при обновлении данных, а все новые столбцы добавляются в список Поля сводной таблицы. В противном случае вам потребуется либо изменить исходные данные для pivotttable, либо использовать формулу динамического именоваемого диапазона.
Все данные в столбце должны иметь один и тот же тип. Например, не следует вводить даты и текст в одном столбце.
Сводные таблицы применяются к моментальному снимку данных, который называется кэшем, а фактические данные не изменяются.
Если у вас недостаточно опыта работы со сводными таблицами или вы не знаете, с чего начать, лучше воспользоваться рекомендуемой сводной таблицей. При этом Excel определяет подходящий макет, сопоставляя данные с наиболее подходящими областями в сводной таблице. Это позволяет получить отправную точку для дальнейших экспериментов. После создания рекомендуемой сводной таблицы вы можете изучить различные ориентации и изменить порядок полей для получения нужных результатов.
Вы также можете скачать интерактивный учебник Создание первой сводной таблицы.
1. Щелкните ячейку в диапазоне исходных данных или таблицы.
2. Перейдите к > рекомендуемой стебли.
3. Excel анализируются данные и представлены несколько вариантов, например в этом примере с использованием данных о расходах семьи.
4. Выберите наиболее подбираемую вам и нажмите кнопку ОК. Excel создаст сводную таблицу на новом листе и выведет список Поля сводной таблицы.
1. Щелкните ячейку в диапазоне исходных данных или таблицы.
2. Перейдите в > Вставить.
Если вы используете Excel для Mac 2011 или более ранней версии, кнопка "Сводная таблица" находится на вкладке Данные в группе Анализ.
3. Excel отобразит диалоговое окно Создание таблицы с выбранным диапазоном или именем таблицы. В этом случае мы используем таблицу "таблица_СемейныеРасходы".
4. В разделе Выберите, куда следует поместить отчет таблицы выберите Новый или Существующий. При выборе варианта На существующий лист вам потребуется указать ячейку для вставки сводной таблицы.
Список полей сводной таблицы
В области Имя поля в верхней части окна выберите любое поле, которое вы хотите добавить в свою pivottable. По умолчанию не числовые поля добавляются в область строк, поля даты и времени — в область Столбец, а числовые — в область значений. Вы также можете вручную перетащить любой доступный элемент в любое поле или, если элемент в ней больше не нужен, просто перетащите его из списка полей или снимите. Возможность переустановить элементы полей — одна из функций, которые упрощают быстрое изменение внешнего вида.
Список полей сводной таблицы
Суммировать по
По умолчанию поля pivotttable, которые находятся в области значений, отображаются как СУММ. Если Excel данные интерпретируются как текст, они отображаются как счёт. Именно поэтому так важно не смешивать типы данных для полей значений. Вы можете изменить вычисление по умолчанию, щелкнув стрелку справа от имени поля, а затем выбрав Параметры полей.
Затем измените функцию в разделе Суммировать по. Обратите внимание на то, что при изменении метода вычисления Excel автоматически добавляет его название в раздел Пользовательское имя (например, "Сумма по полю имя_поля"), но вы можете изменить это имя. Чтобы изменить числовой формат для всего поля, нажмите кнопку Число. .
Совет: Так как при изменении способа вычисления в разделе Суммировать по обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно выбрать пункт Найти (в меню "Изменить"), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.
Дополнительные вычисления
Значения также можно выводить в процентах от значения поля. В приведенном ниже примере мы изменили сумму расходов на % от общей суммы.
Вы можете настроить такие параметры в диалоговом окне Параметры поля на вкладке Дополнительные вычисления.
Отображение значения как результата вычисления и как процента
Просто перетащите элемент в раздел Значения дважды, щелкните значение правой кнопкой мыши и выберите команду Параметры поля, а затем настройте параметры Суммировать по и Дополнительные вычисления для каждой из копий.
При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить одну сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить. При наличии нескольких сводных таблиц сначала выберите любую ячейку в любой сводной таблице, а затем на ленте откройте вкладку Анализ сводной таблицы, щелкните стрелку под кнопкой Обновить и выберите команду Обновить все.
Если вы создали и решили, что вам больше не нужна, вы можете просто выбрать весь диапазон, а затем нажать кнопку Удалить. Это никак не повлияет на другие данные, сбитые таблицы и диаграммы. Если сводная таблица находится на отдельном листе, где больше нет нужных данных, вы можете просто удалить этот лист. Так проще всего избавиться от сводной таблицы.
Выберите таблицу или диапазон данных на листе и выберите Вставить >, чтобы открыть область Вставка таблицы.
Вы можете вручную создать собственную сетевую или выбрать рекомендуемую. Выполните одно из следующих действий:
На карточке Создание собственной таблицы выберите Новый лист или Существующий лист , чтобы выбрать место назначения для этой таблицы.
В рекомендуемой pivottable выберите Новый лист или Существующий лист , чтобы выбрать место назначения для этой таблицы.
Примечание: Рекомендуемые стеблицы доступны только Microsoft 365 подписчикам.
Вы можете изменить источник данных в создаемой вами pivottable.
В области Вставка pivotTable выберите текстовое поле в поле Источник. При изменении источника карточки в области будут недоступны.
Вы можете выбрать данные на сетке или ввести диапазон в текстовое поле.
Нажмите клавишу ВВОД или кнопку, чтобы подтвердить выбор. В области по-новому источнику данных будут обновляться рекомендуемые с помощью новых стеблей.
Потребность в создании иерархических данных появляется при решении следующих задач:
- Отдел – Сотрудники отдела . При выборе отдела из списка всех отделов компании, динамически должен формироваться список, содержащий всех сотрудников этого отдела (двухуровневая иерархия);
- Город – Улица – Номер дома . При заполнении адреса проживания из списка городов нужно выбирать город , затем из списка всех улиц этого города – улицу , затем, из списка всех домов на этой улице – номер дома (трехуровневая иерархия).
Примечание : Рассмотренный в этой статье Многоуровневый связанный список на самом деле правильнее назвать Трехуровневым, т.к. создать четырехуровневый связанный список, используя рассмотренный здесь подход, очень проблематично. Для тех, кому требуется создать структуру с 4-мя и более уровнями, см. статью Многоуровневый связанный список типа Предок-Родитель .
Постановка задачи
Имеется перечень Регионов . Для каждого Региона имеется свой перечень Стран . Для каждой Страны имеется свой перечень Городов .
Пользователь должен иметь возможность, выбрав определенный Регион , в соседней ячейке выбрать из Выпадающего (раскрывающегося) списка нужную ему Страну из этого Региона . В другой соседней ячейке пользователь должен иметь возможность выбрать нужный ему Город из этой Страны (см. файл примера ).
В окончательном виде трехуровневый связанный список должен работать так:
Сначала выберем, например, Регион «Америка» с помощью Выпадающего списка .
Затем выберем Страну «США» из Региона «Америка».
Причем перечень стран в выпадающем списке будет содержать только страны из выбранного на предыдущем шаге Региона «Америка».
И, наконец, выберем Город «Атланта» из Страны «США».
Причем перечень городов в выпадающем списке будет содержать только города из выбранной на предыдущем шаге Страны, т.е. из «США».
Решение
Итак, приступим к созданию Трехуровневого связанного списка . Таблицу, в которую будут заноситься данные с помощью Трехуровневого связанного списка , разместим на листе Таблица .
Список Регионов и перечни Стран разместим на листе Страны .
Обратите внимание, что названия Регионов (диапазон А2:А12 на листе Страны ) в точности должны совпадать с заголовками столбцов, содержащих названия соответствующих Стран ( В1: L 1 ).
Это требование обеспечивается формулой (см. статьи о Транспонировании ). =ДВССЫЛ(АДРЕС(СТРОКА($A$1)-СТОЛБЕЦ($A$1)+СТОЛБЕЦ();1))
с помощью которой формируются заголовки столбцов. Введем ее в диапазон ячеек В1: L 1 .
Список Стран и перечни Городов разместим на листе Города .
Откуда же возьмется перечень стран на листе Города ? Очевидно, что после заполнения листа Страны названиями стран, необходимо, что они каким-то чудесным образом переместились на лист Города . Это чудесное перемещение организуем формулами. Список Стран сформируем на листе Города в столбце А с помощью решения приведенного в статье Объединение списков . Значения для этого списка будем брать из Именованного диапазона Диап_Стран (его нужно предварительно создать через Диспетчер имен ) . Именованный диапазон Диап_Стран образуем формулой:
Для формирования списка Стран нам также понадобится Именованная формула Строки_Столбцы_Стран
Окончательная формула в столбце А на листе Города выглядит так:
сформирует необходимый нам список Стран .
Теперь создадим Динамический диапазон для формирования Выпадающего списка содержащего названия Регионов . Для этого необходимо:
- нажать кнопку меню « Присвоить имя » ( Формулы/ Определенные имена/ Присвоить имя );
- в поле Имя ввести Регионы ;
- в поле Диапазон ввести формулу
Формула подсчитывает количество элементов в столбце А на листе Страны (функция СЧЁТЗ() ) и определяет ссылку на последний элемент в столбце (функция ИНДЕКС() ), тем самым формируется диапазон, содержащий все значения Регионов . Пропуски в столбце А не допускаются.
Аналогичным образом создадим Динамический диапазон Список_Стран для формирования выпадающего списка содержащего названия стран:
Создадим Именованную формулу Позиция_региона для определения позиции, выбранного пользователем региона, в созданном выше диапазоне Регионы:
Т.к. в формуле использована относительная адресация , то важно перед созданием формулы сделать активной ячейку B5 на листе Таблица .
Аналогичным образом создадим именованную формулу для определения позиции, выбранной пользователем страны, в диапазоне Список_Стран =ПОИСКПОЗ(таблица!B5;Список_Стран;0) . Перед созданием формулы нужно сделать активной ячейку С5 на листе Таблица .
Создадим Именованные константы МаксСтран равную 20 и МаксГородов равную 30. Константы соответствует максимальному количеству стран в регионе и, соответственно, максимальному количеству городов в стране. Эти значения произвольны и их можно изменить.
Создадим именованный диапазон Выбранный_Регион для определения диапазона на листе Страны , содержащего страны выбранного региона:
Теперь, например, при выборе региона Америка функция СМЕЩ() вернет ссылку на диапазон страны!$B$2:$B$20
Создадим аналогичный диапазон Выбранная_Страна для определения диапазона на листе Города , содержащего города выбранного региона: =СМЕЩ(города!$A$2;;Позиция_страны;МаксГородов)
Создадим две последние именованные формулы Страны и Города : =СМЕЩ(страны!$A$2;;Позиция_региона;СЧЁТЗ(Выбранный_Регион)) =СМЕЩ(города!$A$2;;Позиция_страны;СЧЁТЗ(Выбранная_Страна))
Эти формулы нужны для того, чтобы в выпадающих списках не отображались пустые строки.Наконец сформируем связанный выпадающий список для ячеек из столбца Страна налисте Таблица .
Также создадим связанный выпадающий список для ячеек из столбца Город (диапазон С5:С22 , в поле Источник вводим: =Города )
На листе Таблица после выбора Региона и Страны теперь есть возможность выбора Города .
Для добавления новых Регионов и их Стран достаточно ввести новый Регион в столбец A (лист Страны ), в строке 1 автоматически отобразится соответствующий заголовок. Под появившимся заголовком в строке 1 введите страны нового Региона .Для добавления новых Городов, на листе Города в строке 1 найдите нужное название страны (оно автоматически появится там после добавления страны на листе Страны ). Под этим заголовком введите название города.
СОВЕТ: В этой статье города (и страны) размещены в нескольких столбцах. Обычно однотипные значения размещают в одном столбце (списке). В статье Многоуровневый связанный список в MS EXCEL на основе таблицы все исходные данные размещены на одном листе, а однотипные данные (названия городов) - в одном столбце. Это облегчает написание формул и позволяет создать списки с большим количеством уровней иерархии (4-6).
Читайте также: