Советы по построению таблиц в MS EXCEL

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

Ниже приведены правила «хорошего поведения» при построении таблиц. Действуйте по правилам и EXCEL Вас поймет (что существенно упростит работу).

  • Всегда создавайте заголовки столбцов у таблиц (заголовок должен быть в одну строку и иметь отличное от данных форматирование). Например, при использовании функций для работы с базами данных (БДСУММ(), БИЗВЛЕЧЬ()) наличие заголовка – обязательное условие;
  • Заголовки столбцов размещайте на первой строке таблицы, сразу же под заголовком должны начинаться записи таблицы;
  • Храните логически связанные данные в одной непрерывной таблице (например, не стоит разбивать информацию о сотрудниках на 2 таблицы, если в одной перечислены ФИО, должность и срок найма, а в другой ФИО, номер трудового договора, стаж, домашний адрес. Подсчитать количество сотрудников со стажем менее 5 лет, занимающих определенную должность в этом случае будет гораздо сложнее);
  • На листе желательно размещать только одну таблицу Если на листе несколько таблиц, то разделяйте их, по крайней мере, одной пустой строкой или пустым столбцом (см. статью Текущая область);
  • Не распределяйте логически связанные данные по нескольким книгам;
  • Избегайте ссылок на данные из других книг;
  • Исключайте появление пустых строк и столбцов в таблицах. EXCEL автоматически определяет диапазоны с данными (текущую область) при построении формул, графиков, Сводных таблиц и в других случаях. Пустые строки как раз и служат разделителем текущих областей. При наличии пропусков в столбцах, появляются существенные и никому не нужные сложности, например, при Поиске номера строки, содержащей последнюю заполненную ячейку в столбце;
  • Избегайте объединения ячеек (объединенные ячейки могут стать причиной некорректной работы фильтра, Сводных таблиц, структуры и др., например, см. статью Автоподбор ширины столбца и высоты строки по содержимому ячеек);
  • Избегайте излишнего форматирования, т.к. «на вкус и цвет – товарищей нет» и это, к тому же, увеличивает размер файла.
  • Если предполагается использовать Расширенный фильтр или функции БДСУММ(), БСЧЕТ(), то желательно оставить 3-5 строк над таблицами для размещения формул, пояснений и критериев;
  • По возможности сортируйте данные, это ускорит работу многих формул, повысит наглядность данных, в ряде случаев позволит использовать стандартные функции, вместо того, чтобы изобретать велосипед и придумывать собственные решения (см. Поиск ЧИСЛА ближайшего к заданному. ЧАСТЬ 1. Сортированный список);
  • Форматируйте таблицы в формат EXCEL 2007 (Вставка/ Таблицы/ Таблица). Это избавит от необходимости ручного форматирования и снизит трудоемкость написания формул;
  • В ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы. Например, не рекомендуется в одну ячейку вводить адрес в формате: «Город, Название улицы, дом №». Нужно создать 3 одноименных столбца, иначе для того, чтобы отфильтровать записи таблицы по названию города (или по названию улицы) придется использовать формулы;
  • В каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата; столбец «Поставщик» - названия компаний только в текстовом формате);
  • Прежде чем заполнять таблицу данными подумайте какими средствами и функциями EXCEL Вы будете анализировать данные. Если формулы получаются слишком громоздкими, то, возможно, стоит перепроектировать таблицу.
  • Заполняйте пустые ячейки повторяющимися данными (не оставляйте ячейки пустыми). При наличии пустых ячеек затруднено использование фильтра и построение формул (см. пример №2).

Лично я уверен, что правильно сформированная исходная таблица - это 80% решения задачи. Ко мне ни разу не обращались за помощью люди, у которых была грамотно сформированная исходная таблица с данными и они не могли на ее основе сделать, например, Сводную таблицу или сделать отчет с помощью формул.

Ниже приведу несколько примеров неправильно сформированных таблиц (не делайте так!).

Пример №1 неправильного построения таблицы

Рассмотрим пример таблицы для учета денежных средств (приход/ расход).

Исходная таблица пользователя выглядит так.

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

Уникальность примера состоит в том, что здесь практические все "не так".

  • Столбец В (приход) практически не заполняется;
  • Столбец D - лишний;
  • Комментарий не позволяет соотнести расход с его типом (статьей затрат);
  • Строка итогов находится под заголовком, а не над ним или под таблицей.

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

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

Теперь избавимся от лишних столбцов.

Заметим, что в статьи расходов попали суммы прихода денежных средств. Чтобы не ввести в замешательство пользователей можно либо вводить суммы расходов со знаком минус, а приходов с +, либо сделать еще один столбец "Тип платежа", где указывать Платеж это или Расход. В нашем случае просто переименуем заголовок "Статья расходов" в "Тип платежа".

Теперь переделаем расчет итогов.

Как видно из рисунка выше, мы преобразовали таблицу в формат таблиц EXCEL 2007, а итоги разместили над таблицей.

Для расчета прихода мы использовали простую формулу =СУММЕСЛИ(Таблица1[тип платежа];B2;Таблица1[сумма])

Для расчета расходов - формулу =СУММ(Таблица1[сумма])-C2 (из общей суммы вычли приход).

И, наконец, заключительный аккорд - составление Сводной таблицы.

Сводная таблица получается в несколько кликов и позволяет провести простейший анализ по статьям расходов. Строку Приход можно скрыть с помощью фильтра (см. ячейку А3).

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

Пример №2 неправильного построения таблицы (наличие в строках пустых ячеек)

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

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

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

Чтобы отобразить все места работы сотрудника Козлов, необходимо заполнить значениями розовые ячейки в столбцах Фамилия, Имя, Отчество, Код_сотрудника.

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

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

Пример №3 неправильного построения таблицы (повторяющиеся столбцы)

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

Ремонт происходит в здании в 6 разных комнатах №№41-46. В каждой комнате нужно разместить 5 типов розеток (трех-, двухфазные, разного цвета и др.) К каждому типу розетки нужен свой кабель (с разным количеством жил, диаметром).

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

Вроде бы все правильно: легко подсчитать количество розеток и кабеля для каждой комнаты =СУММ(B5:B9). Подсчет общего количества розеток для всех комнат (=B10+D10+F10+H10+J10+L10), вроде тоже легко осуществить, хотя и не совсем удобно, при вводе формулы можно ошибиться столбцом или забыть просуммировать нужную ячейку.

Теперь подсчитаем общее количество розеток каждого типа и длину кабеля для каждого типа розетки. Для этого создадим другую таблицу (см. рисунок ниже). В ячейку B16 введем формулу
=B5+D5+F5+H5+J5+L5

Далее, Маркером заполнения, формулу нужно скопировать в ячейки ниже и вправо.

Если комнат в нашем примере было бы не 6, а, например, 18 или 50, то стало бы очевидно, что такой подход не годится (таблица разрастается вправо, а не вниз, формулы становятся сложнее). Конечно, для подсчета розеток определенного типа вместо формулы =B5+D5+F5+H5+J5+L5 можно написать формулу =СУММПРОИЗВ(ЕЧЁТН(СТОЛБЕЦ(B5:M5))*B5:M5), но как правило, пользователь способный написать такую формулу, изначально проектирует таблицу по другому.

Более корректно спроектировать таблицу, разместив номера комнат в строках:

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

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

Например, с помощью фильтра (CTRL+SHIFT+L) и функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ() можно быстро подсчитать количество розеток для каждой комнаты.

Стандартная и общеизвестная функция СУММЕСЛИ() быстро позволяет подсчитать количество розеток определенного типа =СУММЕСЛИ($A$6:$A$35;$A42;B$6:B$35) и соответствующую длину кабеля =СУММЕСЛИ($A$6:$A$35;$A42;C$6:C$35).

Формула теперь не сильно зависит от количества комнат, меняется лишь размер диапазона ячеек.

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

Определенной платой за удобство является повторяющийся ввод номеров комнат (столбец D) и наименований типов розеток (столбец А).

Связанные статьи

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

Комментарии

Гость (не проверено)

Скажите, можно ли в таблице сложить данные только определенных столбцов наприме,р каждого второгоили каждого пятого столбца или столбцов с одинаковыми именами?

Creator

@#1 Обычно, при проектировании таблиц предполагается, что количество столбцов будет порядка 10-15. В этом случае для сложения можно воспользоваться формулой =СУММ(A2:A10) с относительной адресацией (см. статью http://excel2.ru/articles/tipy-ssylok-na-yacheyku-otnositelnaya-a1-absol...), которую можно скопировать Маркером заполнения в нужные столбцы (лишние формулы можно затем удалить).
Если число столбцов более 50 и названия столбцов периодически повторяются, то возможно стоит пересмотреть структуру таблицы. Например, избавиться от повторяющихся столбцов. Если проектируется электрическая разводка в нескольких комнатах (столбцы Материал розетки, Кол-во, Длина кабеля), то вместо того, чтобы повторять эти столбцы для каждой комнаты, лучше создать ОДИН столбец №Комнаты и заполнить строки таблицы (придется повторять наименование Материал розетки для каждой комнаты). Для суммирования, например, количества розеток, можно использовать функцию СУММЕСЛИМН(см. соответствующую статью), в качестве аргументов используя Материал розетки и номер комнаты.

Гость (не проверено)

Можно ли из столбца с итогами вычесть определенные столбцы таблицы?например заголовки столбцов это номера комнат в здании, столбец А - наименование материала -розетки и пр,столбец В содержит количество розеток, столбец С содержит длину кабеля к этим розеткам. и так несколько раз(54!).в столбце итогов нужно посчитать только общее кол -во розеток и общую длину кабеля к ним раздельно.как это можно сделать?

гостья (не проверено)

Почему когда в базе для сводной таблицы добавляешь данные (цифры 5555), то в сводной таблице цифра (5562)другая? Хотя итоговая сумма посчитанна правильно?

Creator

Причин может быть множество. Пришлите Ваш файл на creator@excel2.ru

Яндекс.Метрика