Как выполнить сверку в Excel

Обновлено: 04.07.2024

Выверка банка Формула

Выверка банковских счетов — одна из важнейших задач каждого бухгалтерского и финансового отдела компании. Что такое сверка банков? Процессы банковской выверки, в которых баланс банковского счета в книгах компании сопоставляется с балансом счета нашей компании в книгах банка. Проще говоря, это процесс сверки остатка на банковском счете в бухгалтерских книгах Компании с остатком, отраженным в книгах банка. Этот процесс осуществляется в каждой компании. Это помогает определить, нужно ли внести какие-либо бухгалтерские изменения в бухгалтерские книги. Процесс банковской выверки выполняется на периодической основе в зависимости от объема транзакций. Если объем операции велик, то банковская сверка должна проводиться ежемесячно, раз в две недели или ежедневно. Если объем транзакций меньше, то это может быть выполнено ежеквартально, раз в полгода или ежегодно.

Загрузить корпоративную оценку, инвестиционно-банковские услуги, бухгалтерский учет, калькулятор CFA и другие

Каковы причины несоответствия баланса банковского счета в книгах компании с балансом в книгах банка?

Ниже приведены записи, благодаря которым выполняется процесс сверки:

<р>1. Непредъявленные чеки: Непредставленные чеки означают чеки, выпущенные компанией, но не представленные в Банке. В этом случае компания передаст запись в книги по мере выдачи чека, но банк передаст запись, как только получит этот чек. Следовательно, это одно из действий, которое вызывает разницу в балансе.

<р>2. Депозит в пути: Депозит в пути также является одной из причин расхождений в остатках на дату сверки. При этом компания депонировала чеки или наличные деньги и прошла регистрацию в книгах, но банк не кредитовал их из-за процедуры банковского клиринга. Это вызвано задержкой во времени.

<р>3. Ошибки в Банке: сумма, ошибочно введенная Банком в бухгалтерскую книгу Компании.

Все в одном пакете для финансового аналитика: более 250 курсов, более 40 проектов, более 250 онлайн-курсов | 1000+ часов | Поддающиеся проверке сертификаты | Пожизненный доступ
4,9 (3296 оценок)

<р>4. Ошибки компании: сумма, ошибочно введенная Компанией в бухгалтерскую книгу банка.

Процедура банковской сверки

Ниже приведена пошаговая процедура выверки банковских счетов:

  1. Сопоставьте начальное сальдо согласно банковской выписке с бухгалтерскими книгами.
  2. Проверьте и отметьте все записи о дебете, отраженные в банковской книге, с записями о кредите в выписке по счету, определите, какие из них пропущены.
  3. Проверьте и отметьте все кредитовые записи, отраженные в банковской книге, с дебетовой стороной в выписке по счету, определите, какие из них пропущены.
  4. Исправьте записи, которые ошибочно переданы в банковской книге.
  5. Возьмите скорректированное конечное сальдо банка в отчете о сверке банковских счетов.
  6. Добавьте назад все непредставленные чеки и другие кредиты, которые не отражены в выписке по счету, но отражены в банковской книге.
  7. За вычетом всего депозита в пути и другого дебета, который не отражается в выписке по счету, но запись о том же передается в книгах.
  8. После корректировки вышеуказанной суммы вы получите Баланс в соответствии с выпиской по счету.

Форма выписки банковской сверки

Ниже приведен краткий формат отчета о банковской сверке.

Формат выписки о банковской сверке

Примеры формулы банковской выверки (с шаблоном Excel)

Давайте рассмотрим пример, чтобы лучше понять расчет формулы банковской сверки.

Вы можете скачать этот шаблон Excel для формулы банковской выверки здесь — Шаблон Excel для формулы банковской выверки

На основе следующих сведений подготовьте отчет о сверке банковских данных для M/s XYZ и компании по состоянию на 31 декабря 2018 года.

  1. Баланс по банковской книге: 8 000
  2. Чеки, выпущенные на сумму рупий. 20 000 и 25 000, представленных 5 января 2019 года.
  3. Клиент внес наличные непосредственно в банк на сумму рупий. 50 000
  4. Банковские сборы, дебетованные банком, в размере рупий. 1050
  5. Чек, полученный Банком, на сумму рупий. 20 000
  6. Чек на сумму рупий. 10 000 внесены в банк 30 декабря 2018 года, выплачены банком 3 января 2019 года.
  7. Конечное сальдо в виде банковской выписки, рупий. 51 950 / 

Решение:

Ниже приведен отчет о банковской сверке на дату 31 декабря 2018 года.

Формула банковской сверки-1.2

Ниже представлена ​​банковская выписка ABC International Limited:

Выписка из банка ABC International Limited

Ниже представлена ​​книга учета банковских счетов в бухгалтерских книгах ABC International Limited:

Книга учета банковских счетов в бухгалтерских книгах ABC International Limited

Подготовлен отчет о банковской сверке на 31 января 2019 года.

Решение:

Шаг 1. Сначала мы должны сопоставить все записи о дебете и кредите в банковской книге и банковской выписке ABC International. Из проверки можно отметить следующее:

  1. Чеки, выданные DFP Limited и XYZ Limited, выпущенные компанией, но не представленные в Банке.
  2. Чек от клиента C депонирован в Банк 31 января 2019 года, но не указан в выписке по счету.
  3. Чек, полученный от клиента DF, не принимается банком и не регистрируется в бухгалтерских книгах ABC International Limited.
  4. Банковские сборы, дебетованные Банком, но не зарегистрированные в бухгалтерских книгах

Ниже приведен отчет о банковской сверке на основе приведенной выше информации:

Формула банковской сверки-2.3

Релевантность и использование

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

Рекомендуемые статьи

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

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

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

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

    Например, посмотрите на таблицу ниже.

Сопоставление данных Пример 1

Данные 1 – таблица

Сопоставление данных Пример 1-2

Данные 2 – таблица

Сопоставление данных Пример 1-3Сопоставление данных Пример 1-3

Теперь нам нужно изучить это подробно. Итак, давайте применим функцию ВПР для каждой даты.

Сопоставление данных Пример 1-4

Сопоставление данных Пример 1-5

Сопоставление данных Пример 1-6

Сопоставление данных Пример 1-7

Сопоставление данных Пример 1-8

Сопоставление данных Пример 1-9

Сопоставление данных Пример 1-10

Сопоставление данных Пример 1-11

В данных 1 у нас есть 12104 для даты 04 марта 2019 года, а в данных 2 у нас есть 15104 для той же даты, поэтому разница составляет 3000.

Сопоставление данных, пример 2-1

Выберите массив в качестве диапазона столбцов результатов, т. е. от B2 до B14.

Сопоставление данных, пример 2-2

Чтобы получить номер строки, откройте функцию ПОИСКПОЗ в качестве следующего аргумента.

Пример 2-3

Выберите искомое значение как ячейку D3.

Пример 2-4

Далее выберите массив поиска в качестве столбца «Дата продажи» в данных 1.

Пример 2-5

В типе соответствия выберите «0 — точное совпадение».

Пример 2-6

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

Сопоставление данных Пример 2-7

Это также дает тот же результат, что и функция ВПР. Поскольку мы использовали одни и те же данные, мы получили цифры как есть

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

Пример 3

В приведенных выше данных у нас есть данные о продажах по зонам и по датам, как показано выше. Нам нужно снова выполнить процесс сопоставления данных. Применим функцию ВПР, как в предыдущем примере.

Пример 3-1

У нас много расхождений. Давайте рассмотрим каждый случай отдельно.

В ячейке I5 мы получили дисперсию 8300. Давайте посмотрим на основную таблицу.

Сопоставление данных, пример 3-2

Несмотря на то, что значение основной таблицы равно 12 104, мы получили значение 20 404 из функции ВПР. Причина этого в том, что функция ВПР может возвращать значение первого найденного значения поиска.

В данном случае искомым значением является дата, например 20 марта 2019 г. В приведенной выше ячейке для северной зоны за ту же дату у нас есть значение 20 404, поэтому функция ВПР вернула это значение и для восточной зоны.

Чтобы решить эту проблему, нам нужно создать уникальные значения поиска. Объедините зону, дату и сумму продаж в данных 1 и данных 2.

Данные 1 – таблица

Пример 3-3

Данные 2 – таблица

Сопоставление данных Пример 3-4

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

Используя эти уникальные значения, применим функцию ВПР.

Сопоставление данных Пример 3-5

Применить формулу ко всем ячейкам; мы получим нулевую дисперсию во всех ячейках.

Сопоставление данных Пример 3-6

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

Рекомендуемые статьи

Это руководство по сопоставлению данных в Excel. Здесь мы узнаем, как сопоставлять данные в Excel, используя функцию ВПР, функцию ИНДЕКС + ПОИСКПОЗ и собственное значение ПРОСМОТР с загружаемым шаблоном Excel. Вы можете узнать больше об Excel из следующих статей –

В этом посте мы будем использовать запрос «Получить и преобразовать», чтобы упростить сверку. Идея этого поста возникла из вопроса Лауры (спасибо, Лаура!) Основная идея заключается в том, что у нас есть два рабочих листа. Один содержит итоговые суммы счетов, а другой содержит сведения о позициях, где на каждый счет приходится много позиций.

Это базовый проект сверки, в котором мы хотим знать, не отображаются ли какие-либо счета в сводке в деталях и не согласуются ли какие-либо итоговые суммы счетов с суммой позиций. Звучит как большой, утомительный проект? Только не с запросом Get & Transform! Проверьте это.

Цель

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

На одном листе у нас есть список сумм счетов. Эта сводка счета, сохраненная в таблице с именем SummaryTable, показана ниже.

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

Сведения о счете от Джеффа Леннинга

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

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

Подробнее

  • Создайте сводный запрос
  • Создайте подробный запрос
  • Создайте запрос на сверку

Примечание. Приведенные ниже шаги представлены для Excel для Windows 2016. Если вы используете другую версию Excel, обратите внимание, что представленные функции могут быть недоступны или вам может потребоваться загрузить и установить Power Query. Надстройка.

Создайте итоговый запрос

Чтобы создать итоговый запрос, щелкните любую ячейку данных в диапазоне и щелкните значок Данные > Из таблицы на ленте. Если данные уже хранятся в таблице, как у нас, откроется редактор запросов. Если данные находятся в обычном диапазоне, Excel предложит вам сначала создать таблицу.

Мы подтверждаем, что сводные данные счета-фактуры попали в редактор запросов, как показано ниже.

Редактор запросов Джеффа Леннинга

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

Когда данные выглядят хорошо, мы используем раскрывающийся список «Главная» > «Закрыть и загрузить» (не кнопку «Закрыть и загрузить»), а затем выбираем «Закрыть и загрузить в».

Загрузить в

В появившемся диалоговом окне «Загрузить в» мы выбираем «Только создать соединение», а затем нажимаем «Загрузить». Мы выбираем этот параметр, потому что не хотим, чтобы результаты запроса возвращались в ячейки рабочей книги, но мы хотим иметь доступ к результатам в другом запросе (в нашем предстоящем запросе на сверку).

Создайте подробный запрос

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

Данные передаются в редактор запросов, как показано ниже.


Наши данные должны быть агрегированы по идентификатору. Итак, во-первых, мы удалим столбец Item. Для этого щелкните правой кнопкой мыши заголовок столбца Элемент и выберите Удалить.

Далее нам нужно создать по одной строке для каждого идентификатора и вычислить соответствующую сумму. Итак, мы щелкаем правой кнопкой мыши заголовок столбца ID и выбираем Group By. Excel отобразит диалоговое окно «Группировать по», в котором мы подтверждаем, что поле «Группировать по» имеет идентификатор и что мы хотим создать новый столбец с именем «Итого», который суммирует столбец «Сумма», как показано ниже.


Мы нажимаем OK, и обновленные результаты появляются в редакторе запросов, как показано ниже.

Обновленный редактор запросов от Джеффа Леннинга

Наши данные выглядят хорошо, поэтому мы выбираем «Закрыть и загрузить в» и еще раз выбираем параметр «Только создать соединение» в диалоговом окне «Загрузить в».

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


Последний шаг – объединить их и поместить результаты в новый лист сверки.

Создайте запрос на сверку

Чтобы сравнить эти запросы, мы выбираем команду Данные > Новый запрос > Объединить запросы > Объединить.

В появившемся диалоговом окне слияния мы выбираем SummaryTable из первого раскрывающегося списка и DetailTable из второго раскрывающегося списка. Затем нам нужно сообщить Excel, какое поле является общим для них. Мы делаем это, щелкая столбец ID в обоих окнах предварительного просмотра. Это показано ниже.


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

Редактор запросов показывает результаты слияния, как показано ниже.


Далее нам нужно указать Excel отображать столбцы из DetailTable, поэтому мы щелкаем значок в заголовке NewColumn и выбираем «Развернуть» (или, альтернативно, значок «Преобразование» > «Структурированный столбец» > «Развернуть»). Мы выбираем столбцы для отображения, в нашем случае мы выбираем столбцы ID и Total и нажимаем OK.

Обновленные результаты показаны ниже.


На данный момент мы можем легко определить, какие счета в сводке не отображаются в деталях из-за значения null. Теперь нам просто нужно упростить сравнение двух сумм. Мы сделаем это, вычислив разницу между двумя столбцами «Итого».

Мы создаем новый вычисляемый столбец, щелкнув значок «Добавить столбец» > «Пользовательский столбец». Диалоговое окно «Добавить пользовательский столбец» показано ниже.


Наш новый столбец называется Diff. Чтобы создать формулу, дважды щелкните столбец Total в списке Available Columns, введите оператор вычитания (-), а затем дважды щелкните столбец NewColumn.Total. Мы нажимаем OK, и новый столбец будет отображаться в редакторе запросов, как показано ниже.


Поскольку мы хотим увидеть результаты этого запроса в Excel, мы нажимаем команду «Закрыть и загрузить». Результаты отображаются в Excel, как показано ниже.


На этом самая интересная часть процесса примирения окончена. Теперь начинается не очень интересная часть, где мы должны углубиться в детали и понять, почему некоторые счета-фактуры не отображаются на листе сведений, например, счет-фактура 1004, и почему некоторые имеют другую сумму (например, счет-фактура 1002).

Обновить следующий период

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

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

Если у вас есть какие-либо другие забавные подходы к согласованию или любые другие забавные советы по запросам Get & Transform, поделитесь ими, опубликовав комментарий ниже… спасибо!

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


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

Подробнее о программном обеспечении

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

Я использую Excel 2016 в 64-разрядной системе Windows 10. Вы можете применить эту статью к более ранним версиям, но шаги будут отличаться. Функции и сводная таблица легко работают в браузере. Хотя браузер будет отображать промежуточные итоги на существующей таблице, вы не можете использовать браузер для применения этой функции. Вы можете работать со своими данными или скачать демонстрационные файлы .xlsx и .xls.

Функции

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

Рисунок А


Этот набор данных напоминает запись базы данных, а не традиционную электронную таблицу.

В настоящее время у нас есть хороший список сумм счетов и платежей; у нас есть записи о причитающихся и уплаченных деньгах. Вот и все. Учитывая текущую структуру, нет простого способа определить, какие клиенты должны нам деньги. Первой мыслью может быть создание быстрой матрицы с помощью простой функции СУММЕСЛИ(), как показано на рисунке B:

=СУММЕСЛИ($B$3:$B$15, "wp-image-2061233 size-article" src="https://d1rytvr7gmk1sx.cloudfront.net/wp-content/uploads/2018/06/excelreconcileb.jpg " ширина="">

Простая функция СУММЕСЛИ() возвращает итоги для каждой компании.

Вторая проблема заключается в том, что вы не можете ограничить результаты СУММЕСЛИ() по дате, что было бы разумным ожиданием при таком типе данных. Вы можете использовать СУММЕСЛИМН() в матрице и опорных датах, но это быстро станет громоздким.

С таким же успехом можно добавить функцию рядом с набором данных, как показано на рисунке C. Подойдет любая из следующих функций:

=СУММЕСЛИМН($D$3:D$15,B$3:B$15,$B3)
=СУММЕСЛИ($B$3:$B$15," wp-image-2061234 size-article" src=" https://d1rytvr7gmk1sx.cloudfront.net/wp-content/uploads/2018/06/excelreconcilec.jpg" ширина="">

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

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

=ОКРУГЛ(СУММЕСЛИ($B$3:$B$15, "wp-image-2061235 size-article" src="https://d1rytvr7gmk1sx.cloudfront.net/wp-content/uploads/2018/06/excelreconciled) .jpg" ширина="">

Вы можете вернуть TRUE и FALSE в зависимости от непогашенного остатка клиента.

Чтобы реализовать простое правило условного форматирования на основе функции ОКРУГЛ(), сделайте следующее:

  1. Выберите набор данных B13:D15.
  2. На вкладке "Главная" нажмите "Условное форматирование" в группе "Стили" и выберите "Новое правило".
  3. На верхней панели выберите Использовать формулу, чтобы определить ячейки для форматирования.
  4. В нижней панели введите формулу

Итого

Функция промежуточного итога в Excel может использоваться для суммирования. При использовании этой функции сортировка данных всегда является первым шагом. В этом случае мы хотим суммировать по клиентам, поэтому сначала вы должны отсортировать по полю «Клиент». Затем щелкните в любом месте набора данных и продолжите:

  1. Перейдите на вкладку "Данные", а затем нажмите "Промежуточный итог" в группе "Структура".
  2. В появившемся диалоговом окне выберите «Клиент» в раскрывающемся списке «При каждом изменении».
  3. Выберите «Сумма» в раскрывающемся списке «Использовать функцию».
  4. Проверьте итоговую сумму заказа в списке "Добавить промежуточную сумму" (рис. E).
  5. Нажмите "ОК", чтобы увидеть результаты, показанные на рис. F.

Рисунок E


Выберите настройки промежуточного итога.

Рисунок F


Промежуточный итог возвращает те же результаты, что и матрица СУММЕСЛИ().

Функция «Промежуточные итоги» возвращает те же результаты, что и матрица, но отображает соответствующие сведения с промежуточными итогами. Однако он не динамичен. Вы также не можете легко вернуть промежуточные итоги для данных «Клиент» и «Дата», по крайней мере, не так, как есть. Чтобы приспособить оба, потребуется некоторая начальная настройка, и даже тогда она все еще не является динамической. Промежуточный итог – это переключаемая функция, которую можно включать и выключать, чтобы обновлять результаты по мере добавления данных.

Сводная таблица

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

  1. Нажмите в любом месте набора данных, перейдите на вкладку "Вставка", а затем нажмите "Таблица" в группе "Таблицы".
  2. В появившемся диалоговом окне проверьте диапазон данных (он должен быть правильным).
  3. Проверьте параметр "Моя таблица имеет заголовки".
  4. Нажмите "ОК".

Теперь вы готовы создать сводную таблицу следующим образом:

  1. Нажмите в любом месте таблицы.
  2. Перейдите на вкладку "Вставка" и нажмите "Сводная таблица" в группе "Таблицы".
  3. При необходимости нажмите "Новый рабочий лист" и нажмите "ОК".
  4. На панели полей сводной таблицы перетащите «Клиенты» в список «Строки», а «Сумму заказа» — в список «Значения».
  5. Затем перетащите дату в список строк. Когда вы это сделаете, Excel автоматически добавит поле «Месяцы». Перетащите месяцы из списка строк в список фильтров (рис. G).

Рисунок G


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

На данный момент у вас есть подробные записи и промежуточные итоги, сгруппированные по компаниям. Вы не сопоставляете платежи со счетами, но можете сразу увидеть, какие компании имеют непогашенный остаток. Чтобы отфильтровать промежуточные итоги по месяцам, используйте фильтр. Как вы можете видеть на рисунке H, детали и итоги за июнь показывают в основном платежи. Вы также можете отфильтровать по компании, выбрав параметр в раскрывающемся списке «Ярлыки строк». (Если в вашем наборе данных есть отдельные столбцы для дебета и кредита, добавьте их в список строк.)

Рисунок Н


Используйте фильтр для отображения записей по месяцам.

Сводная таблица будет отражать изменения в таблице, даже новые записи. После обновления таблицы щелкните внутри сводной таблицы, перейдите на вкладку Контекстный анализ, а затем нажмите Обновить в группе Данные.

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