Советы по построению таблиц в MS EXCEL - совместимо с Microsoft Excel 2007, Excel 2010

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ремонт происходит в здании в 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. Это позволит Вам решать широкий класс подобных задач.

13 мая 2013 - 16:26

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

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

13 мая 2013 - 11:08

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

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

9 сен 2014 - 15:39

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

Книги по учету денежных средств

Posted March 17, 2008 7:46am UTC by admin Category. Финансы и налоги

Перед тем как начать заполнять кассовую книгу, нужно пронумеровать все листы, прошнуровать и опечатать. Количество листов в кассовой книге заверяется подписями руководителя и главного бухгалтера данного предприятия.
Регистрировать кассовую книгу в отличие от журнала кассира-операциониста в налоговой инспекции не требуется. Поэтому многие бухгалтеры нумеруют страницы лишь после того, как заполнят всю книгу. Это дает возможность в случае ошибки вырвать страницу и заполнить лист заново.
Разумеется, при проверке предъявлять такую книгу нельзя. Но всегда можно сказать, что в настоящее время книги на объекте (в магазине, кафе и пр.) нет. Обратите внимание: предприятие не обязано держать кассовую книгу непосредственно там, где продает товары или оказывает услуги.
Тем более если у вас не один, а несколько магазинов или любых других объектов. Ведь предприятие ведет только одну кассовую книгу вне зависимости от количества объектов и кассовых аппаратов.
Все записи в кассовой книге ведутся в двух экземплярах через копировальную бумагу.
Первые экземпляры листов остаются в кассовой книге, а вторые отрываются и скрепляются со справкой-отчетом кассира-операциониста (как заполнять эту справку, рассказано ниже) и приходными и расходными кассовыми ордерами.
Записи в кассовую книгу должны производиться кассиром сразу же после получения или выдачи денег. Конечно, на практике так редко кто поступает. Обычно кассовую книгу заполняют в конце дня, а то и сразу за неделю. Но в этом случае имейте в виду — такую книгу показывать проверяющим нельзя. Вначале ее нужно заполнить вплоть до текущего дня.
Ежедневно в конце рабочего дня кассир подсчитывает итоги операций (приход и расход) за день, выводит остаток денег в кассе на конец дня. Эта же сумма будет остатком на начало следующего рабочего дня. Кассовая книга может вестись и в компьютере. Но листы должны ежедневно распечатываться и храниться в бухгалтерии.
Старший кассир перед началом рабочего дня выдает другим кассирам авансом необходимую для расходных операций сумму наличных денег под расписку в книге учета принятых и выданных кассиром денег.
Хранение в кассе наличных денег и других ценностей, не принадлежащих данному предприятию, запрещается.

Книга учета доходов и расходов

Книга учета доходов и расходов открывается на один календарный год и должна быть прошнурована и пронумерована.
На последней странице пронумерованной и прошнурованной налогоплательщиком книги учета доходов и расходов указывается число содержащихся в ней страниц, которое подтверждается подписью индивидуального предпринимателя (руководителя организации), а также заверяется подписью налогового инспектора и скрепляется печатью до начала ее ведения.
Вести книгу учета доходов и расходов очень просто. Рассмотрим порядок ее заполнения на примере.
Обратите внимание: в книгу учета доходов и расходов бухгалтеры могут не вносить те доходы и расходы, которые не влияют на расчет единого налога. К такому выводу пришел Высший арбитражный суд РФ (решение ВАС РФ от 8 сентября 2004 г. N 9352/04). Индивидуальные предприниматели и раньше были освобождены от такой обязанности (письмо МНС России от 24 мая 2004 г. N САЭ-3-22/322).

Документальное оформление кассовых операций с использованием ККМ

Итак, день закончился. Значит, нужно оформить кассовые документы. Прежде всего пробейте так называемый Z-отчет. Что он собой представляет? Это итоговый чек, в котором отпечатаны показания счетчиков ККМ на начало рабочего дня и на тот момент, когда вы сняли отчет по кассе. Разница между этими показателями и будет вашей дневной выручкой.
После того как кассир снял показания счетчиков (отпечатал Z-отчет), он делает записи в журнале кассира-операциониста. Такой журнал заводят на каждый кассовый аппарат. В его графы ежедневно записывают показания счетчиков аппарата на начало и окончание рабочего дня, остатки наличных денег в кассе на начало и конец дня и сумму дневной выручки.
Кассир должен также обязательно составить и Справку-отчет кассира-операциониста (форма N КМ-6). К отчету прилагают приходный ордер на сумму дневной выручки и вместе с деньгами сдают в кассу организации.
Перед тем как сделать записи, нужно обязательно проверить, совпадает ли сумма, пропечатанная в итоговом чеке, с наличкой в кассе. Вообще-то, эти суммы должны совпадать. Однако может быть и по-другому. Так, например, происходит, когда кассир неправильно пробил чек либо возвратил деньги покупателям.
Итак, предположим, что покупатель потребовал вернуть ему деньги или кассир неправильно пробил чек, скажем, не на ту сумму. «Ошибочный» чек и чек, возвращенный покупателем, обязательно сохраните. Ведь они нужны, чтобы оформить Акт о возврате денежных сумм покупателям (форма N КМ-3) по неиспользованным кассовым чекам. В акте отражают номер чека и сумму (если за день таких чеков несколько, то каждого чека). Акт визируют кассир-операционист, старший кассир (если такая должность предусмотрена) и руководитель организации (индивидуальный предприниматель).
«Ошибочные» чеки и чеки, возвращенные покупателями, погашают специальным штампом или просто надрывают. Потом клеят на лист и вместе с актом отдают в бухгалтерию. Ошибочно пробитую сумму отражают в графе 15 журнала кассира-операциониста.

Пример
ЗАО «Комфорт» продает стройматериалы. В торговом зале магазина установлен один кассовый аппарат. За ним работает кассир-операционист Суханкина И.Р. С ней заключен договор о полной материальной ответственности.
Вечером 27 ноября 2005 года Суханкина пробила итоговый чек (Z-отчет). В чеке были пропечатаны показания денежных суммирующих счетчиков:
— на начало дня — 10 946 700 руб.;
— на конец дня (когда сняли отчет по кассе) — 11 487 300 руб.
То есть сумма дневной выручки составила 540 600 руб. (11 487 300 — 10 946 700). Но вечером в магазин приехал покупатель и вернул приобретенную в этот же день кафельную плитку. Магазин вернул покупателю деньги — 4750 руб.

Теперь представьте ситуацию, когда ваш магазин работает круглосуточно. Тут вам нужно решить, в какой момент пробивать итоговый чек. Как правило, это делается в тот момент, когда меняется смена.
Кассовый чек нужно пробить и в том случае, если ваш клиент — юридическое лицо. Но помимо чека контрольно-кассовой машины представителю организации-клиента нужно выдать еще и корешок приходного ордера. А деньги, полученные от каждого клиента-организации, нужно занести в кассовую книгу по отдельному приходному ордеру.
Отражая в кассовой книге итоговую выручку (пробитую по кассовому аппарату) за день, уменьшите ее на сумму денег, полученных от организаций. Ведь они уже записаны в кассовой книге и на них уже выписаны «приходники».
И еще один совет. Лучше сразу пробивать чеки на суммы, полученные от покупателей-граждан и покупателей-организаций по разным секциям. Тем самым вы упростите себе работу. Тогда вам не придется долго считать, на какую величину нужно будет уменьшить общую дневную выручку.

Пример
ООО «Норма» продает женскую одежду. Наряду с гражданами одежду в магазине покупают и организации. В магазине установлен один кассовый аппарат. Суммы, полученные от граждан, проводятся по первой секции. А наличные за одежду, проданную оптовикам, пробиваются по второй секции.
18 апреля 2006 года дневная выручка ООО «Норма» составила 896 000 руб. Из них от населения было получено 441 000 руб. Остальные 455 000 руб. пришлось на три организации:
— 168 000 руб. — ООО «Кастор» (приходный ордер N 547);
— 179 400 руб. — ЗАО «Поллукс» (приходный ордер N 548);
— 107 600 руб. — ЗАО «Твин» (приходный ордер N 549).
В конце дня кассир Рокотова И.С. составила справку-отчет кассира-операциониста и сделала запись в журнале кассира-операциониста. В этих документах была учтена общая сумма выручки по двум секциям — 896 000 руб.
Но приходный ордер (N 550) кассир Рокотова выписала на 441 000 руб. — деньги, полученные от граждан. Суммы, проведенные по второй секции (деньги юридических лиц), учтены в кассовой книге по отдельным ордерам.
В конце дня 890 000 руб. было сдано инкассаторам (расходный ордер N 455). Остаток на начало дня равен 1147 руб.
Кассовая книга организации за 18 апреля 2006 года будет заполнена следующим образом:

Бланки строгой отчетности

Организации и предприниматели, оказывающие бытовые услуги населению, могут выбирать — работать с кассовым аппаратом или же применяя бланки строгой отчетности (БСО).
Сразу отметим, что невыгодно применять эти бланки предпринимателям и организациям, среди клиентов которых не только граждане, но и юридические лица. Принимать наличные от юридических лиц можно только с использованием ККМ. А использовать в работе и кассовый аппарат, и бланки строгой отчетности неудобно.
Бланк строгой отчетности — это документ, на основании которого приходуется наличная выручка. Формы бланков строгой отчетности утверждает Минфин России. Для различных сфер деятельности существуют различные формы бланков строгой отчетности. В настоящее время формы этих бланков утверждены для сферы бытовых услуг и еще нескольких сфер деятельности (туристической, экскурсионной и т.п.). Заказать бланки можно в любой типографии или приобрести уже готовые в специализированных магазинах.
Как правило, бланк строгой отчетности состоит из двух или трех частей — наряда-заказа и квитанции. В некоторых бланках предусмотрена еще и копия квитанции. Одна из них выдается клиенту, вторая лежит непосредственно у мастера, исполняющего заказ, а третья хранится в бухгалтерии. Принимая деньги у клиента, кассир заполняет бланк, принимает деньги и на основании заполненного бланка делает запись в кассовой книге. Копии квитанций, корешки, отрывные талоны и другие экземпляры использованных документов строгой отчетности должны храниться не менее пяти лет.
Бланки строгой отчетности можно изготовить и самостоятельно на компьютере. Главное, чтобы форма этих документов содержала все обязательные реквизиты:
— гриф утверждения;
— наименование формы документа;
— номер;
— серию;
— код по ОКУД;
— дату составления документа;
— наименование организации;
— код организации по ОКПО;
— вид оказываемых услуг, работ;
— единицу измерения в натуральном выражении;
— подпись ответственного лица.
Можно ввести и дополнительные реквизиты, например эмблему фирмы.
Ниже мы привели номера форм бланков строгой отчетности. А сами формы вы можете посмотреть и распечатать c диска, который вы получили вместе с этой книгой. Образцы всех форм, которые указаны в таблице, приведены в письме Госналогслужбы России от 22 июня 1995 г. N ЮУ-4-14/29н.