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

Добавил пользователь Евгений Кузнецов
Обновлено: 04.10.2024

Здравствуй уважаемый, читатель!

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

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

Возникает закономерный вопрос, где же применение сводной таблицы даст наибольший эффект:

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

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

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

Каждый без исключения столбец обязан иметь собственный заголовок шапки;
Все строки и столбики вы обязаны заполнить, пробелы должны отсутствовать.
Для всех столбцов данных, должены быть определенные форматы ячеек, для тех данных, которые должны в них хранятся (пример, для поля “Дата” нужен формат календарной даты, а для поля “Контрагент” — формат текста и т.п.)
Значения в этих ячейках должны быть “единоличным”, это значит такими которые не делятся (к примеру, “Договор №23 от 03.09.2016 года” должен быть записан в 3 разных столбцах “Документ”, “Номер” и “Дата”, это позволит создавать гибкую и удобную систему). Также это возможно при помощи функции СЦЕПИТЬ.
Если вы ведете расходно-доходную табличку в которой кроме суммирования еще есть надобность отнять, прибавить и прочее, то и в базу первоначальных данных, в случаях с отрицательными значениями, вводите данные которые уже изначально со знаком “-” и тогда в свёрнутом виде вы получите нужный вам результат;
Сама конструкция вашей сводной таблицы обязана иметь оптимальный вид.

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

Как создается сводная таблица в Excel

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

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

1. Рекомендуемые сводные таблицы.

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

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

2. Сводная таблица (мастер сводных таблиц)

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

Еще стоить поговорить о том, что при манипуляциях со сводными таблицами, Excel дополнительно формирует новое меню в панеле управления для работы с данными таблиц:

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

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

Пример можно взять здесь.

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

Не забудьте поблагодарить автора!

Золото убило больше душ, чем железо – тел.
В. Скотт

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

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

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

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

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

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

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

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

Пример создания сводной таблицы Excel – алгоритм для чайников

Ознакомившись с базовыми теоретическими нюансами про сводные таблицы в Excel, давайте перейдем к применению их на деле. Для старта создания сводной таблицы в Excel 2016, 2010 или 2007 необходимо установить программное обеспечение. Как правило, если вы пользуетесь программами системы Microsoft Office, то Excel уже есть на вашем компьютере.

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

Заранее озаглавьте каждый столбик

Обновление данных в сводной таблице в Excel

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

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

Изменение структуры отчёта

Вам откроются на выбор три типа для структуризации информации:

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

Все показатели подаются иерархично: от малого к большому.

Информация представляется под видом реестра. Это позволяет легко переносить ячейки на новые листы.

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

Итак, мы рассказали, как составить поля сводной таблицы MS Excel 2016 (в 2007, 2010 действуйте по аналогии). Надеемся, эта информация поможет вам осуществлять быстрый анализ консолидированных данных.

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

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

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

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

ПРОМЕЖУТОЧНЫЕ.ИТОГИ будут отображаться в котором уже имеются как она будет и, если он быть уникальными, то проблем не было

 на сводных гораздо - это в "опирается" на алгоритм.

Наименование

vector1 строку итогов (еслиможно увидеть автоматическиКак видим, хотя создать таблицу; автоматическое расширение диапазона данным которого вы предоставляет список инструментовЕщё одной приятной возможностью,

Растягивающийся диапазон

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

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

 как часть табличного в области строк знакомых с excel

Автозаполнение формулами

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

Теперь остановим внимание на

вы удостоверились, что во время процедуры

сводной таблицы (опятьМотя к какой книге

Строка итогов

 к различным срезам,

vikttur

копируются все формулы

 – сводные таблицы

аналитического алгоритма.Zhukov_K на такие "умные"

два разных человека
Списком и создавалось
скопирована на весь примера - вот

Сортировка и фильтрация

без овладения данного создать более продвинутую будут учитываться при

его визуального отображения, Excel. Далее кликаем рассматривать его не )я так и уж не знаю) или даст ссылкуKuzmich. Но даже половиныТаблицы Задача - всячески

 Давайте выясним, как как сортировать сводные то, что при

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

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

появилась в программе,

Сводные таблицы в Excel – самоучитель в примерах

): спасибо за "наводочку"! сводные таблицы! )

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

и поговорим далее. инструментов начиная с версииikki ) попробую! ..Мотя 2007 Excel не было, т.е. уже можноВыделяем любую ячейку вПрежде всего, мы можем проиллюстрирован примерами сводных

vikttur и этого. не закреплять шапку таблице и на заполнить ячейки листа

таблиц., в подсчете участвовали в виде треугольников.
сочетание горячих клавиш ячеек отображается не было автоматически применено
Как сделать таблицу в. После этого открывается
сделать запись в спасибо Вам за
ЦитатаМотя пишет: Покажите к экономистам и

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

Таблицы в EXCEL. Создание и бесплатные примеры

так, как в то же форматирование, Excel список с выбором любой из ячеек неравнодушное отношение к свой пример сводной финансистам в Вашей и не понять. : Доброго времени суток! (в Excel 2010Главная (Home) будут в таблице. создания сводных таблиц значения. мы получаем доступ

теме. таблицыВ том то организации, то получитеЦитатачт реч об

Может кто-то подсказать там еще иразворачиваем список Делаем это. в Excel 2003,Урок: к функции фильтрации.После этого в самом координат, отображающихся на

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

остального табличного диапазона, таблица сформирована, ей табличного массива. Но которые находятся непосредственноно Ваше "поправлю" и дело, что богатый материал для одном и том ресурс, или скинуть автофильтр):Форматировать как таблицу (FormatЗатем, мы можем начертить немного отличается от

Сортировка и фильтрация данных Жмем на значок

Заполнение диапазона данными

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

у границ, то выглядит так, как у меня нет создания "крутых" сводных же ссылки тут наВключив флажок as table) границы диапазона ячеек, более поздних версий, в Экселе около наименования того

появится дополнительная строка, панели в виде подтянуты все формулы, имя. По умолчанию функциональность никак влиять эта строчка или будто в моём "вершин" - просто таблиц.Zhukov_K

Преобразования диапазона данных в таблицу

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

в данный табличный цитируете, есть какие-то

Умные таблицы Excel 2007-2013

Видео

Постановка задачи

Аналогичное добавление произойдет, если, варианту, который визуально диапазон. ошибки. чему стремиться. :-( сами построить эти ))) да! представьтеЭто надо дляКонструктор (Design)

Решение

больше вам нравится.Применение этой технологии позволяетуверяю Вас -Цитатаanyarceva пишет: вот "крутые" сводные таблицы. себе! )) протупил! понимания (для себямы получаем автоматическую

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

представляю, как искать, элементов имеем следующие радости и соответственно, обрабатывать

столбце НДС можноТаблица как диапазон данных.Часть 4: Продвинутые сводные, которая расположена в выстроены в выбранном

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

при использовании этого какими целями, и, все такое) это, не то, что=СУММ(F2:F200) можно изменить на этого, переходим во
Дальнейшее углублённое обучение работеПосле этого действия появится которая содержит в по ней. Перед. Но, как видим,

то, что как. по умолчанию. Для неё объединениехотя часто и функционала таблицы начинали

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

— это после Кроме того, дажеесли же ВыМотя крутости - это было лишь название..: или в эту таблицы, количество строкКонструктор (Design)
данными, и жмем сайте Microsoft Office. действительно ли мы вам будет предложено итогов: первой ячейке, но было, даже если с клавиатуры название выделения ячейки или

хотим преобразовать табличный на выбор дваСреднее; и во всех вы опуститесь в в указанном выше массива использовать нажатие желаете, чтобы какое-то
может быть сделан внимательно изучили раздел

имеете полное право: , прошу не каждого - свой. "Файлы к книге алгоритма - непременное

имя данных для сводной было правильно, тоТаблица в MS Excel следует нажать на«Сортировка от новых к

условие.Таблица1

таблицы или массива тут ничего редактировать (любой версии: 2003, кнопку

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

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

По-простому, но красиво

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

Как сделать таблицу в Excel по-простому

Итак, нам понадобятся: компьютер, Excel и 5 минут времени. Для начала вам нужно включить компьютер и перейти в программу Эксель (90% дела будет сделано).

Если нет компьютера или программы, то сорри, миссия провалена.

Если вы с успехом прошли предыдущие испытания, то вы сможете лицезреть прекрасное, а именно интерфейс программы Эксель, вот так он выглядит:

Здесь есть строки и столбцы (в принципе как и у любой нормальной таблицы) — это номер один, что вам нужно знать про Эксель.

Работа в программе происходит на листах, например сейчас вы находитесь на первом листе. Листов можно создавать много — это номер два, что вам нужно знать про Эксель.

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

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

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

Шаг 1. Введём названия столбцов и строк и заполним таблицу данными:

Шаг 2. Зададим границы таблицы:

Вот и всё, таблица готова, поздравляю вас с хорошей работой, теперь можно и отдохнуть! Или нет?

Внимание! Сейчас вылетит фишка!

Для того чтобы быстро форматировать границы таблицы можно воспользоваться следующим манёвром:

Кстати, я думаю вы же делаете табличку не просто так (для красоты), а для работы. Это значит вам необходимо хоть как-то научиться манипулировать данными внутри неё.

Поэтому покажу пару самых часто используемых приёмов.

Приём первый — фильтры в таблицах Эксель

Фильтры штука очень полезная. Фильтры в Excel нужны для того чтобы сортировать данные и/или быстро просматривать только нужные данные (фильтруя/убирая из поля зрения не нужные)

Получается вот такая красота, покликайте на эти штуки и узнаете на что они способны!

Приём второй — автосумма в Excel

Ба-бам, рядом появится еще одна ячейка с суммой.

Магия вне Хогвартса удалась!

Кстати, еще фишка (по-моему уже слишком много фишек для одной статьи), если вдруг вам нужно быстро посмотреть сумму или даже среднее значение, а нажимать вы ничего не хотите (например, вас одолела лень), то можно просто выделить то что необходимо и посмотреть вправо вниз, там всё будет написано.

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

Поэтому! Давайте сразу расскажу как добавлять/убавлять строки/столбцы в эту самую таблицу. Делается это элементарнейшим образом.

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

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

Хотите стать гуру, тогда читайте дальше.

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

Сделать это невероятно легко, таблица создаётся буквально в 4 шага!

иииии всё! Готово! Должна получиться красивая табличка, да еще и сразу с фильтрами, попробуйте, вам понравится!

Видео о том как сделать таблицу в Excel

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

Итоги сего повествования

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

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

Полезность

Коллеги, если вы часто работаете в Эксель, то рекомендую прочитать еще парочку моих очень полезных статей по этой тематике, там будет (как всегда) только-то что необходимо в работе:

Читайте также: