Как сделать плоскую таблицу в excel
Не секрет, что большинство пользователей Excel, создавая таблицы на листах, думают в первую очередь о собственном комфорте и удобстве. Так рождаются на свет красивые, со сложными "шапками", пестрые и громоздкие таблицы, которые при этом совершенно нельзя ни отфильтровать, ни отсортировать, а про автоматический отчет сводной таблицей лучше и не думать вообще.
Рано или поздно пользователь такой таблицы приходит к мысли, что "пусть будет не так красиво, зато можно работать" и начинает упрощать дизайн своей таблицы, приводя его в соответствие с классическими рекомендациями:
- простая однострочная шапка, где у каждого столбца будет свое уникальное название (имя поля)
- одна строка - одна законченная операция (сделка, продажа, проводка, проект и т.д.)
- без объединенных ячеек
- без разрывов в виде пустых строк и столбцов
Но если сделать однострочную шапку из многоэтажной или разбить один столбец на несколько достаточно просто, то реконструирование таблицы может занять много времени (особенно при больших размерах ). Имеется ввиду следующая ситуация:
Из | | сделать | |
В терминах баз данных правую таблицу обычно называют плоской (flat) - именно по таким таблицам лучше всего строить отчеты сводных таблиц (pivot tables) и проводить аналитику.
Преобразовать двумерную таблицу в плоскую можно при помощи простого макроса. Откройте редактор Visual Basic через вкладку Разработчик - Visual Basic (Developer - Visual Basic Editor) или сочетанием клавиш Alt + F11 . Вставьте новый модуль (Insert - Module) и скопируйте туда текст этого макроса:
После этого можно закрыть редактор VBA и вернуться в Excel. Теперь можно выделить исходную таблицу (полностью, с шапкой и первым столбцом с месяцами) и запустить наш макрос через Разработчик - Макросы (Developer - Macros) или нажав сочетание Alt + F8 .
Макрос вставит в книгу новый лист и создаст на нем новый, реконструированный вариант выделенной таблицы. С такой таблицей можно работать "по полной программе", применяя весь арсенал средств Excel для обработки и анализа больших списков.
Если вы работаете в Excel ( или учитесь работать в Excel ), то вы постоянно сталкиваетесь с созданием таблиц. Данная статья будет полезна тем, у кого нет четкого понимания, как создавать правильные таблицы, что не надо делать при создании таблиц и почему построение данных очень важная часть создания таблиц!
Что мы рассмотрим в данной статье?
1. Какие таблицы обычно создает пользователь в Excel?
2. Научимся, как не надо создавать таблицы?
3. Что значит "плохие" таблицы в Excel?
4. Разберем плохо организованные таблицы с точки зрения ее структуры, построения данных.
5. Подготовим таблицы для дальнейшего анализа и создания отчетов.
Ошибки при создании таблиц в Excel
1. Отсутствует структура данных в таблицах
Первая и самая распространенная ошибка пользователей при создании таблиц в Excel - это отсутствие структуры в таблицах Excel.
Важно знать, что при проектировании таблиц, мы должны соблюдать основные правила создания, так называемой, реляционной модели базы данных.
Простыми словами, это база данных с упорядоченной информацией, связанная между собой определёнными отношениями. Такая база данных представлена в виде таблиц, в которых и лежит вся эта информация.
В таких базах данных существует такое понятие, как "нормализация" . Простым языком под нормализацией мы будем понимать - процесс удаления избыточных данных в таблицах. Такие таблицы будут называться - нормализованные таблицы.
Именно создание нормализованных таблиц по модели реляционной базы и есть главный критерий построения правильных таблиц в Excel.
Запомните! Это фундамент для построения будущих таблиц.
Зафиксируем сразу два важных момента:
1. Определяем какие данные мы хотим хранить в таблице. Проектируем структуру данных в таблице Excel.
2. Создаем таблицы без лишней и перегруженной (не нужной) информации.
Эта статья для "Новичков", судьба которых свела с таким мощным рабочим инструментом, как Microsoft Excel.
С чего начать освоение MS Excel?
- Подпишитесь на этот канал ExceLifeHack , для получения "практической выжимки" по данной теме и ознакомьтесь с его содержанием;
- Ознакомьтесь с одной из книг: 5 лучших книг для начинающих изучать Excel ;
- Изучите Горячие клавиши , это реально сохранит драгоценное время;
- Практика, практика и еще раз практика! Чем чаще вы будите взаимодействовать с Excel и разнообразнее будут решаемые при помощи него задачи, тем быстрее освоите инструментарий программы.
Общее количество на листе строк 1 048 576 и столбцов 16 384 , т.е. больше данных не влезет. При необходимости, вы можете хранить данные на нескольких листах.
В ячейку помещается 32 767 знака. Excel не World, чтобы хранить в одной ячейке много данных, поэтому такого количества вполне достаточно.
Количество элементов, отображающихся фильтром 10 000 , при этом при поиске необходимые данные фильтр выводит.
И так, с введением закончим.
Пошаговая инструкция, как сделать таблицу Excel:
1. Хорошо продумайте структуру и внешний вид таблицы , это очень важный этап.
Данные, которые будут в таблице, имеют конечный вид или их нужно будет обрабатывать дальше?
Если таблица отражает конечные результаты, то можно не заморачиваться.
Если же это пополняемая таблица и с данными нужно будет работать, дальше применяйте "плоский вид".
Плоская таблица – двумерный массив данных, состоящий из столбцов и строк, первая строка которого содержит заголовки.
Столбцы образуют информационные атрибуты таблицы, строки тело таблицы. Каждая строка – это отдельная запись, состоящая из множества атрибутов (по числу столбцов):
Добрый день. Сегодня я расскажу Вам, как создавать плоскую сводную таблицу в Excel. Пройдя этот урок, Вы научитесь создавать сводные таблицы, как на картинке ниже.
Известно, что при формировании сводной таблицы по умолчанию, Excel строит ее в древововидном виде. Это неплохо, когда нужно быстро получить обобщенные данные, но когда требуется в дальнейшем продолжить обработку таблицы, такой вид не годится. Нам на помощь придет плоская сводная таблица, которую можно сделать в несколько простых приемов.
Рассмотрим в качестве учебного примера ситуацию с простым отчетом по продажам. Есть несколько записей о выручке, которую получили наши агенты в разных городах в разное время. Нужно обобщить информацию по месяцам и фамилиям для последующего расчета премий.
1. Создаем сводную таблицу
Подробно останавливаться на этом пункте не буду, так как у нас на сайте есть отличная статья на эту тему: "Как создать сводную таблицу в Excel". Если Вы еще не знаете как это сделать, рекомендую прочитать, иначе двигаемся дальше.
2. Изменяем макет сводной таблицы на табличный
Для этого кликните на любую ячейку сводной таблицы, вверху справа должен появиться блок меню "Работа со сводными таблицами". В нем выберите меню "Конструктор", далее подменю "Макет отчета" и там "Показать в табличной форме".
Выполнив эти действия, мы получаем сводную таблицу уже не в древовидном виде, а в форме таблицы. Однако для полноценной работы, нам мешают итоговые строки и отсутствие данных в части ячеек. Сейчас мы это исправим.
3. Удаляем итоговые строки
Для удаления итоговых строк кликните правой кнопкой мыши на любую итоговую ячейку, к примеру "Январь 2017 Итог". В появившемся контекстном меню выберите "Параметры поля" и там в блоке "Итоги" выберите "Нет".
Итак, итоги удалены. Осталось добавить в пустые строки дублирующие данные и плоская сводная таблица будет готова!
4. Заполняем пропущенные данные
Если Вы обратите внимание на сводную таблицу в самом начале урока, то заметите что дата проставлена не во всех ячейках, сейчас мы это исправим. Для этого снова вызовите контекстное меню, нажав правой кнопкой мыши на любую из дат и снова выберите "Параметры поля. ", там перейдите в раздел "Разметка и печать" и установите галочку напротив пункта "Повторять подписи элементов".
Поздравляю плоская сводная таблица готова, теперь ее можно анализировать различными доступными методами.
Кросс-таблица (Сводная таблица) – способ визуализации данных, в котором они упорядочены по строкам и столбцам. Строкам соответствует одна группа данных, столбцам другая, их пересечение содержит информацию, объединяющую их.
Выполнить такую магию без COPY/PASTE можно при помощи Макроса или надстройки Power Query.
Видео на тему ⬇⬇⬇
1. Откройте редактор VBA: ALT+F11;
2. Создайте новый модуль Insert ► Module:
3. Вставьте макрос:
Sub ПреобразованиеТаблиц()
Dim OutputRng As Range
Dim InputRng As Range
Dim out_row As Long, out_col As Long
Dim in_col As Long, in_row As Long
Set InputRng = ActiveCell.CurrentRegion
Set OutputRng = Application.InputBox(prompt:="Выберите ячейку для вывода новой таблицы", Type:=8)
OutputRng.Range("A1:C1") = Array("Столбец1", "Столбец2", "Столбец3")
out_row = 2
out_col = 2
For in_row = 2 To (InputRng.Rows.Count - 1) * (InputRng.Columns.Count - 1) + 1
For in_col = 1 To 3
If in_col = 1 Then OutputRng.Cells(in_row, in_col) = InputRng.Cells(out_row, 1)
If in_col = 2 Then OutputRng.Cells(in_row, in_col) = InputRng.Cells(1, out_col)
If in_col = 3 Then OutputRng.Cells(in_row, in_col) = InputRng.Cells(out_row, out_col)
Next in_col
out_col = out_col + 1
If out_col = InputRng.Columns.Count + 1 Then
out_col = 2
out_row = out_row + 1
End If
Next in_row
End Sub
4. Сохраните книгу с поддержкой макросов;
5. Создайте кнопку для вызова макроса Разработчик ► Вставить:
6. Назначьте на кнопку макрос:
7. Выделите любую ячейку исходной таблицы, нажмите кнопку. В открывшемся окне выберите ячейку для вывода новой таблицы:
1. Загрузите таблицу в редактор, вкладка Данные ► Получить данные ► Из других источников ►Из таблицы/диапазона;
2. Выделите первый столбец, нажмите пкм на шапке столбца ► Отменить свертывание других столбцов:
3. Загрузите запрос, окно редактора запросов, Главная ►Закрыть и загрузить ► Закрыть и загрузить в. :
4. Выберите вариант Таблица, указав расположение для выгрузки:
Полезно? Пишите в комментах, следующую тему.
MS, Libreoffice & Google docs
535 постов 13.4K подписчика
Правила сообщества
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Важно - сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.
Утверждения вроде "пост - отстой", это оскорбление автора и будет наказываться баном.
А самый простой способ с помощью сводной таблицы и не рассказал)
Мне вот эта конструкция понравилась, ни че такая
For in_col = 1 To 3
If in_col = 1 Then OutputRng.Cells(in_row, in_col) = InputRng.Cells(out_row, 1)
If in_col = 2 Then OutputRng.Cells(in_row, in_col) = InputRng.Cells(1, out_col)
If in_col = 3 Then OutputRng.Cells(in_row, in_col) = InputRng.Cells(out_row, out_col)
Next in_col
OutputRng.Cells(in_row, 1) = InputRng.Cells(out_row, 1)
OutputRng.Cells(in_row, 2) = InputRng.Cells(1, out_col)
OutputRng.Cells(in_row, 3) = InputRng.Cells(out_row, out_col)
ExceLifeHack, ты спас мне жизнь! Это как открыть мир с ВПР )))
Может кто подскажет , как таблицу с иерархией превратить в плоскую ? Заранее благодарен
Макрос написан не оптимально. Можно написать короче с автоматическим масштабированием по количеству строк.
Какой-то слишком сложный метод. Можно же INDEX-ами и ROW/COLUMN на коленке разобрать.
Может вы знаете, как таблицу перевернуть? Например, когда три строки и 100500 столбцов, и удобнее фильтровать, если бы были те же данные, но в 3 столбца и 100500 строк. Была бы очень признательна, Гугл не помог вообще.
Как из таблицы на одном листе выбрать строки с уникальным ключом и из этих строк выбрать некоторые столбцы, и поместить это на другом листе?
Конкретно вот эта таблица:
В первом столбце уже стоят уникальные индексы по второму столбцу (формулой проставил). Надо сделать таблицу из второго и третьего столбца там, где есть номер в первом столбце.
Могу усердно работать 2-3 часа в день. Это нормально?
Последние 8 лет я работал в digital и продажах. И здесь принято хвалиться тем, что ты работаешь по 12 часов.
Хотя бы иногда. А если у тебя ещё и получилось выйти на уровень выше среднего, то лишь потому что ты пахал, аки проклятый.
С другой стороны, я вижу коллег, которые пишут: «могу усердно работать лишь 2-3 часа. Что со мной не так?»
Правда в том, что с тобой всё нормально. Нет, если накатывает слабость или головокружение, то нужно сходить к терапевту, как минимум.
Но мы сейчас о другом. О ситуации, когда ты можешь трудиться с максимальной эффективностью лишь пару часов в день. А потом только в lite-режиме с кофе, беседами с коллегами о насущном, постами на Пикабу.
Не исключено, что ещё с чувством вины, потому что все вокруг такие продуктивные. Летают и носятся половину суток напролёт. Хорошо, какое-то мизерное количество «энерджайзеров» среди нас есть.
Таких ещё часто двужильными называют. Но их мало. А все остальные врут. Сознательно или не очень.
Специалисты сервиса Rescue Time (прога для учёта времени) проанализировали 225 миллионов рабочих часов своих клиентов. Что они выяснили? Оказывается, с ударной отдачей мы реально работаем лишь 2,5 часа в сутки.
В неделю получается около 12,5 часов. Лучше всего мы трудимся в 15:00 по средам, а самый продуктивный день в году — 14 ноября.
А теперь давайте посмотрим на японцев. Этих ребят принято хвалить за их работоспособность. Когда я попал в мир большого корпората, то ощутил на себе все кайдзены и прочие канбаны.
И если в данных системах что-то реально есть, то продуктивность японцев — самый натуральный миф.
Работают они очень много. Порядка 60 часов в неделю и больше. Труд в Стране восходящего солнца — настоящий культ.
Но есть одно но. По производительности Япония худшая среди стран «Большой семёрки». Среди стран ОЭСР она дальше первой двадцатки. Французы, исландцы или австралийцы работают куда лучше.
Как же так? Ведь японские служащие настолько усердные, что у них даже появился отдельный термин «Кароши». Так называют сотрудников, которые умерли от сердечных приступов или совершили суицид после нервного срыва на работе.
Всё ради показушной борьбы с безработицей. Она на родине Годзиллы действительно низкая. Даже в первую волну пандемии составила всего до 2,8%. И поднялась впервые за 11 лет.
Но как и в СССР, японские рабочие весь день пытаются открутить огромным ключом маленький болт.
Поэтому не важно, сколько вы работаете. Гораздо важнее — что вы делаете и как. И тут мы плавно переходим к теме осознанной практики Андерса Эрикссона. Но это уже совсем другая история.
Автор: Евгений Селезнев
Остальные мои посты о психологии вот тут: Психолог с твоего района (канал в Телеграме). Перевожу на простой русский язык всякие академические сложности и не только. 4,5 года на психфаке отбыл, административные аресты с повышением квалификации бывают регулярно.
Как и какие диаграммы надо строить
Частые вопросы про диаграммы - какой тип диаграммы выбрать в каких случаях и как правильно настраивать диаграммы, чтобы получить хорошую понятную визуализацию? Попробую в понятных слайдах рассказать основные принципы построения диаграмм.
Какой тип диаграммы выбрать?
Если данные содержат сравнительную характеристику, подойдет гистограмма или линейчатая диаграмма. Если категорий данных немного, не более 7, гистограмма будет смотреться лучше, если более 7 – лучше использовать линейчатую диаграмму.
Аналогично есть разграничение по подписям на оси. Длинные подписи на горизонтальной оси поворачиваются или частично скрываются, что затрудняет их чтение. Если так получилось, независимо от количества категорий данных лучше использовать линейчатую диаграмму.
Для отражения данных в динамике, то есть изменения их во времени, подойдет гистограмма и график – тоже в зависимости от количества данных. Немного данных – менее 5 – гистограмма, более 5 – график.
Круговую или кольцевую диаграммы можно использовать только для визуализации долевого соотношения, но и то, если в данных менее 8 категорий. Если категорий больше, лучше снова использовать линейчатую диаграмму.
И все эти правила на общей схеме:
Правила настройки диаграмм:
1. Располагать временную ось горизонтально и слева направо. Это логичный ожидаемый вариант расположения данных:
2. Не загромождать диаграмму ненужными или дублирующимися сведениями. Ненужные элементы также затрудняют понимание диаграммы:
3. Не раскрашивать диаграмму "веселыми" цветами. Избыток цвета на диаграмме отвлекает от понимания информации, лучше, когда диаграммы представлены в монохромных цветах или в нескольких сочетающихся цветах:
4. Не использовать объемные типы диаграмм. Мода на объемы давно прошла, особой красоты в объемах нет, а восприятию мешает. Всякая другая "красота" - эффекты, тени перспектива - тоже желательно использовать минимально или воздержаться:
5. Добавлять подписи данных. Числовые оси не так информативны, как подписи. Следует всегда добавлять подписи данных и по возможности скрывать ось значений. Также не стоит подписывать ось, если в этом нет особой необходимости:
6. В линейчатых диаграммах использовать сортировку. Так как линейчатые диаграммы хорошо отображают большое количество данных, лучше для данных применить сортировку, так будет проще сравнивать значения. Наиболее значимый ряд (например, наибольший) можно выделить другим цветом:
7. Комбинировать типы диаграмм (можно по разным осям), если данные различны по смыслу. Например, на картинке видно, что ряд Выручка – это гистограмма, а ряд Прибыльность – график. При этом, Выручка измеряется в тысячах, а Прибыльность – в процентах. Если такие данные строить по одной оси, сравнительного анализа не получится, здесь требуется разделение по осям:
Главная задача диаграммы - облегчить понимание контента: пришел, увидел, победил все понял. Если диаграмму приходится долго рассматривать, чтобы понять, о чем здесь вообще? - это плохая диаграмма. Не делайте такие, делайте хорошие диаграммы!
Читайте также: