Сделай Сам Свою Работу на 5

Подготовка отчётов с помощью формул





Ниже приводятся формулы для расчета итогов (оборотов и сальдо по счетам и группам счетов) в отчетах трех типов:

q оборотные ведомости по счетам,

q ведомость итогов по корреспонденциям счетов (типовым проводкам),

q отчеты для внешних пользователей по группам счетов — баланс и другие финансовые отчёты.

Список счетов и корреспонденций счетов для подготовки оборотных ведомостей соответственно по счетам и по корреспонденциям счетов (типовых проводкам) можно получить командой Данные–Фильтр–Расширенный фильтр.

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

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

Создание макета оборотно-сальдовой ведомости

Для создания макета ведомости (см.Табл. 17) выполним следующее.

Табл. 17 — Пример оборотно-сальдовой ведомости

Прежде всего, укажем отчётный период, например Январь. Для этого введем в ячейку В1 любую январскую дату.



Затем установим для даты, введённой в ячейку В1, формат Янв 2001. Для этого сделаем следующее:

q выделим ячейку В1 и выполним команду Формат–Ячейки–Число,

q выберем на вкладке Число в окне диалога Формат ячеек (см. рисунок ниже) нужный образец в поле Числовые Форматы.

Рис. 5 — Окно диалога Формат ячеек

Если подходящего формата в поле Числовые Форматы нет, выберем строку (Все форматы) и введем в поле Тип код формата формулу:

mmm yyyy, где:

m — код месяца (три знака выводят сокращённое название месяца).

y — код года (четыре знака выводят полный номер года).

Более подробно о форматах значений Excel см. статью встроенного справочника Создание и удаление пользовательского числового формата.

Теперь пора подготовить исходные данные для создания ведомости оборотов (список счетов и входящие остатки) и рассчитать итоги.

Подготовка списка счетов

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



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

Для удаления дубликатов счетов выполним следующее:

q Выделим в журнале диапазон С1:D28 с названиями и номерами счетов.

q Присвоим этому диапазону имя Список.

q Откроем окно фильтрации командой Данные–Фильтр–Рас­ши­рен­ный фильтр (см. рисунок ниже).

q Введём имя Список в поле Исходный диапазон или нажмём клавишу F3 и выберем имя в окне Вставка имени (см. рисунок ниже).

q Щёлкнем по переключателю Скопировать результат в другое место.

q Укажем адрес свободной ячейки, например адрес D95 в поле Поместить результат в диапазон. Эта ячейка должна удовлетворять двум условиям:

○ Ячейка должна находиться только на текущем листе.

○ Ячейка должна находиться под или над списком операций.

q Установим флаг Только уникальные записи (см. рисунок ниже).

В результате получим искомый список счетов вместе с лишними строками, включающими имена операций (см. рисунок ниже).

Чтобы избавиться от лишних данных и отсортировать список по номерам счетов, выделим список и выполним команду Данные–Сор­ти­ровка. Откроется окно Сор­ти­ровка диапазона (см. рисунок ниже).

Выберем столбец Е, включающий номера счетов, в поле Сортировать по и нажмём клавишу ОК.



Откроется окно Предупреждение сор­ти­ровки с предложением выбрать способ упорядочения строк(см. рисунок ниже).

Дело в том, что способ сортировки чисел зависит от формата чисел. Применяют две группы форматов чисел:

q Группа обычных числовых форматов — Числовой, Денежный, Финансовый (см. Рис. 5, с.84).

q Текстовый формат

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

Чтобы посмотреть таблицу символов в Word, надо выполнить команду Вставка–Символ (см. рисунок ниже).

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

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

Аналогично отфильтруем счета с номерами счетов в одной строке.

Расчет итогов по счетам в месячном журнале учёта

Требуется рассчитать итоги двух видов — обороты и сальдо по счетам.

Расчет оборотов по счетам в месячном журнале учёта

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

Для расчёта оборотов по всем операциям с одинаковыми номерами счетов введем функцию СуммЕсли() в столбец Оборот дѐбета в оборотно-сальдо­вой ведомости (см. выше Табл. 17):

=СуммЕсли(СчетЖ; Номер Материалы; ДебетЖ), где

СчетЖ — это имя диапазона адресов D3:D28 в журнале операций со счетами в одном столбце.

ДебетЖ — это имя диапазона адресов Е3:Е28 в журнале операций со счетами в одном столбце.

Номер — это заголовок стол­б­ца в оборотно-сальдовой ведомости.

Материалы — это заголовок строки в оборотно-сальдовой ведомости.

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

Ввести формулу можно в окне мастера функций (см. рисунок ниже).

В версии Excel 2007 возможность использования заголовков в формулах исключена, придётся вместо заголовков использовать адреса или имена.

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

=Сумм­Ес­ли(СчетЖ; Номер; Деб­етЖ).

Аналогично оценим оборот по крѐдиту счёта по формуле:

=СуммЕсли(СчетЖ; Номер Материалы; КредитЖ) ), где:

КредитЖ — это имя диапазона F3:F28 в журнале учёта операций со счетами в одном столбце.

Особенности расчёта итогов для счетов разных типов

Как известно, способ расчёта итога (оборота и сальдо) зависит от типа счёта. Исходя из этого, разделим счета на три группы:

q Счета активные, пассивные и активно-пассивные. Активно-пассивные счета могут быть двух типов:

○ Счета с одним сальдо (или по дебету или по кредиту).

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

q Счета без переходящего остатка.

q Забалансовые счета в балансе не используются (вот почему их называют забалансовыми). При подсчёте итогов по этим счетам двойная запись не используется.

В нашем примере (см. выше Табл. 15) есть счета следующих типов.

○ Первые шесть счетов — 10 , 19 , 20, 26, 50 являются активными.

○ Счёт 60 Расчёты с поставщиками и подрядчиками — активно-пассивный.

○ Счёт 68.2 Расчёты по налогам и сборам — пассивный.

○ Счёт 26 Общехозяйственные расходы не имеет переходящего остатка.

Схема расчёта итогов по счёту зависит от типа счёта:

q Для активного счёта рассчитывают конечный остаток (сальдо) по дѐбету.

q Для пассивного счёта рассчитывают сальдо по крѐ­ди­ту.

q Схема расчета итогов для активно-пассивного счёта зависит от типа счёта:

○ Для активно-пассивного счёта с двумя сальдо рассчитывают оба сальдо — сальдо по дѐбету и крѐ­ди­ту. Например, для счёта 60 Расчёты с поставщиками и подрядчиками сальдо по кредиту показывает задолженность организации перед поставщиками и подрядчиками за поставленные, но не оплаченные материальные ценности. В свою очередь сальдо по дебету счёта 60 свидетельствует о суммах выданных авансов (предварительной оплате) и задолженности поставщиков и подрядчиков.

○ Для активно-пассивного счёта с одним сальдо рассчитывают оба сальдо, но выводят одно саль­до. Например, для счёта Прибыли и убытки выводится сальдо по прибыли, если прибыль больше убытков. Если же прибыль меньше по убытков, выводится сальдо по убыткам.

q Некоторые счета закрывают в конце отчётного периода. Это означает, что они не имеют конечного и, следовательно, начального остатка, а сальдо равно разности оборотов дебета и кредита (см. сальдо по счёту 26 в Табл. 17, с. 84). Поэтому их называют счетами без переходящего сальдо.

Обратите внимание, если начальное сальдо для счёта 26 приравнять нулю, конечное сальдо счёта станет отрицательным (см. таблицу ниже), чего быть не может.

Дело в том, что в журнале операций (см. Табл. 15, с. 75), зарегистрирована только одна операция со счётом 26 (см. таблицу ниже).

В этой операции в кредит счёта 26 записаны 2 единицы. Счёт 26 является активным. Поэтому сальдо по счёту вычисляется по формуле:

Начальное сальдо + кредитовый оборот – дебетовый оборот (0 + 0 – 2)

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

В результате конечный остаток на счёте 26 становится равным нулю (см. оборотную ведомость ниже).

Для активных счетов, как уже известно, рассчитать конечное сальдо можно несколькими способами (см. выше Табл. 17 — Пример оборотно-сальдовой ведомости, подготовленной по проводкам Табл. 15 — Пример журнала операций со счетами в столбце. См. также раздел «Расчет итогов по счетам в годовом журнале учёта», с. 97):

q Формула с адресами (формула приводится для ячейки F3 (Конечный остаток) в первой строке — счёт 10 Материалы)

=C3 + D3 - E3.

q по формуле с заголовками:

=На­ча­ль­ный остаток + Оборот дѐбета - Оборот крѐ­ди­та.

Приведенные выше оборотные ведомости включают только два сальдо — начальное и конечное. В общем случае, учитывая разницу между ак­тив­ны­ми, пассивными и активно-пассивными счетами, ведомость должна иметь начальное и конечное сальдо по дѐбету и по крѐ­ди­ту (см. ниже образец).

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

q Создать оборотно-сальдовую ведомость в разрезе договоров.

q Создать оборотно-сальдовую ведомость в разрезе поставщиков. При этом есть возможность взаимозачёта дебиторской и кредиторской задолженностей в рамках отношений с каждым поставщиком.

q Создать оборотно-сальдовую ведомость в разрезе дебиторской и кредиторской задолженностей.

Расчет итогов по счетам в годовом журнале учёта

Самый большой грех по отношению к ближнему — говорить ему то, что он поймёт с первого раза. В.В. Ерофеев

При расчёте итогов на основе годового журнала учёта надо проверить выполнение двух условий:

q совпадение даты (месяца) регистрации операции в жур­нале учёта и месяца, для которого создается обо­ротная ведомость,

q совпадение номера счета в жур­нале учёта и номера счета, для которого подсчитывается итог в обо­ротной ведомости.

Например, в ячейке D3 оборотной ве­до­мо­сти надо проверить:

q совпадение месяца создания обо­ротной ведомости (Январь) с датами регистрации операций в жур­нале учёта,

q совпадение номера счёта в ячейке В3 обо­ротной ведомости (10 Материалы) с номерами счетов в журнале учёта.

Для проверки одновременного выполнения условий можно перемножить формулы, соответствующие этим условиям. Например, проверить одновременное выполнение условий 1 >= 1 и 1 <= 2 можно с помощью их произведения (см. подробнее в моей работе [11]):

(1 >= 1) * ( 1 <= 2 ) = 0.

Не сложно вычислить это выражение, учитывая способ представления значений Истина и Ложь в программах Microsoft. Оказывается, значение Истина рассматривается при умножении как 1 (единица), значение Ложь — как 0 (ноль). В этом легко убедиться, вычислив эти значения в Word, Excel или Access.

Используя этот способ, оценим итог дѐбета в ячейке D3 по формуле массивов (фигурные скобки вводить не надо, это знак формата формулы массива, который появляется при вводе формулы нажатием комбинации клавиш Ctrl+Shift+Enter):

{=Сумм( Если((

Месяц( ТекМесяц ) = Месяц( ДатаЖ ))*( Номер Материалы = СчетЖ );

ДебетЖ ))}, где ( 1 )

функция Месяц() преобразует дату в номер месяца,

ДатаВ — это имя ячейки B1 с датойсоздания ведомости; эта дата преобразуется функцией Месяц() в номер месяца, для которого создаётся журнал,

ДатаЖ — это имя столбца с датами В3:В28 в журнале операций.

Для вычисления итогов по другим счетам, скопируем формулу вниз во все ячейки с активными счетами. В ячейки со счетами 60 и 68.2 нужно вводить другие формулы.

Как работает формула массива

Формула массива сложнее для понимания по сравнению с обычной формулой. Объясняется это, видимо, следующим. Обычная формула обрабатывает отдельно взятые значения, например числа. Аргументами формулы массива, могут быть не только отдельные значения, но также и группы значений (массивы). Массивы — это диапазоны ячеек или констант трёх типов: прямо­уголь­ные таблицы (матрицы), столбцы и строки (векторы).

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

q Для автоматического вычисления формулы выполнить команду Сервис–Зависимости формул–Вычислить формулу.

q Для вычисления формулы вручную включить режим правки содержимого ячейки, выделить часть формулы и нажать клавишу F9.

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

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

q Команда меню вычисляет только формулы, имена в формулах она не вычисляет. Например, эта команда вычисляет сразу формулы Месяц(ДатаВ) и Месяц(ДатаЖ), но не показывает значения имён скаляра ДатаВ и вектора ДатаЖ.

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

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

Вычисление формулы массива вручную

При вычислении формулы вручную надо сначала включить режим правки формул. Для этого нужно выделить ячейку с формулой (в нашем случае это формула ( 1 ) на с. 98) и получить доступ к её содержимому нажатием клавиши F2 или щелчком по строке формул. Затем выделить любую часть формулы и вычислить ее значение, нажав клавишу F9. Мы будем вычислять части формулы по порядку слева направо, т.е. сначала вычислим константу ДатаВ, затем выражение Месяц(ДатаВ) и т.д. Результаты вычислений с помощью клавиши F2 имеют следующий вид (см. таблицу ниже).

Табл. 19 — Анализ формулы для расчёта годовых оборотов

Часть формулы Результат вычислений
ТекМесяц
Месяц(ТекМесяц)
Месяц(ДатаЖ) {1:1: … :1}
(Месяц(ТекМесяц)=Месяц(Да­таЖ)) {Истина:Истина: … :Истина}
Номер Материалы "10"
СчётЖ {0:"10":"60":0:"20":"26":0:"50":"51":0:"20":"10":0:"68.2":"19":0:"60":"51":0:"10":"60":0:"60":"51":0:"51":"50"}
(Номер Материалы=СчетЖ) {Истина:Ложь: … :Истина:Ложь: … :Ложь:Истина: … :Ложь:Ложь}
(Месяц(ТекМесяц)= Месяц(ДатаЖ))* (Номер Материалы=СчетЖ) {1:0:0:0:0:0:0:0:0:0:1:0:0:0:0:0:0:0:1:0:0:0:0:0:0:0}

Как видим дата, представлена в Excel также как и в других программах Microsoft Office одним числом (см. первую строку), а не тремя числами — день, месяц, год. Легко убедиться, что даты пронумерованы по порядку, начиная с 1900 года, Для проверки введите в ячейку дату 01/01/1900 и присвойте ей числовой формат. В результате на экране появится дата с номером 1(единица).

Функция Месяц() вычисляет номер месяца (см. вторую строку). Поэтому выражение Месяц(ДатаЖ) вычисляет столбец (вертикальный массив) из 27 единиц, разделенных двоеточием (см. третью строку, в которой указаны только первая, вторая и последняя единицы, вместо остальных единиц стоит знак многоточия …).

Действительно, в столбце ДатаЖ (В3:В28) зарегистрировано 9 проводок. Каждая из них занимает три строки. Итого 3×9=27 единиц.

Столбец дат ДатаЖ используется в формуле массива. Как известно, строки массива (в нашем случае числа в столбце ДатаЖ) разделяют двоеточием.

Обратите внимание, как работает функция Месяц().Она вычисляет единицу (номер января) для пустых ячеек — В5, В8 и др. Оказывается, первой датой, которую понимает Excel, является нулевая дата января 1900 г. (00/01/1900). Первая дата января 01/01/1900 является второй датой.

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

q Введите в любую ячейку дату 01/01/1900.

q Введите в ячейку справа от введённой даты функцию =Месяц(Дата) с адресом этой даты.

q Протащите маркер заполнения ячейки с датой вниз на две строки и затем вверх на три строки (см. рисунок ниже).

Табл. 20 — Нумерация дат в Excel

Как видим, дате 01.01.1900 предшествует дата 00.01.1900 (см. третью строку в Табл. 20).

Посмотрим на выражение вчетвёртой строке Табл. 19 (см. с. 100):

(Месяц(ТекМесяц) = Месяц(ДатаЖ)).

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

q Ведомость составляется на январь.

q Функция Месяц() вычисляет значение 1 для пустых ячеек.

Поэтому выражение вычисляет массив-столбец, включающий 27 значений Истина.

Ссылка Номер Материалы, состоящая из заголовков (достаточно ввести только один заголовок Номер), вычисляет номер счёта в текстовом формате "10".

Имя СчётЖ присвоено столбцу D3:D28, включающему номера счетов в журнале учёта операций. Поэтому оно вычисляется как массив-столбец — 10, 19, 20, 26, 50, 51, 60, 68.2.

Выражение (Номер Материалы=СчетЖ) вычисляет массив из 26 логических значений. Эти значения равны Истина, когда номера счетов в оборотной ведомости и журнале совпадают, т.е. в проводках №1, №4 и №7.

Рассмотрим выражение, введённое в первый аргумент функции Если():

(Месяц(ТекМесяц) = Месяц(ДатаЖ)) * (Номер Материалы = СчетЖ).

Это выражение вычисляет массив:

{1:0:0:0:0:0:0:0:0:0:1:0:0:0:0:0:0:0:1:0:0:0:0:0:0:0}.

Этот массив является произведением двух столбцов-массивов.

Первый столбец — Месяц(ТекМесяц) = Месяц(ДатаЖ).

Второй столбец — Номер Ма­те­ри­алы = СчетЖ.

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

Второй столбец аналогичным образом обеспечивает выбор из журнала учёта операций с заданным номером счёта.

Журнал учёта операций включает 27 строк. Поэтому результаты вычисления аргументов формулы массива включает 27 значений. Причем единица вычисляется, разумеется, для проводок №1, №4 и №7, для которых оба условия возвращают истину.

Имя ДебетЖ во втором аргументе функции Если() возвращает дебетуемые суммы по счетам №1, №4 и №7.

Функция Сумм() завершает вычисления.

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

При вводе формул массива часто совершаются две ошибки:

q Нажатие неверной комбинации клавиш ввода. Надо нажимать Ctrl+Sh+8 .

q Несоответствие размеров операндов в формуле массивов.

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

Автоматическое вычисление формулы массива

При автоматическом вычислении формулы массива надо выделить ячейку с формулой и выполнить команду Сервис–Зависимости формул–Вычислить формулу (см. рисунок ниже).

Откроется окно Вычисление формулы (см. рисунок ниже).

В этом окне можно последовательно в пошаговом режиме посмотреть результаты всех этапов вычисления формулы массивов.

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

q Результат последнего этапа вычислений выделяется курсивом.

q Выражение, вычисляемое на следующем этапе вычислений нажатием кнопки Вычислить, выделяется подчёркиванием.

Как видим, ни один элемент формулы на этом рисунке не выделен курсивом, т.к. мы ещё не начали вычисления. Выражение Месяц(ТекМесяц) (первая часть формулы массива) выделено подчёркиванием. Поэтому после первого нажатия кнопки Вычислить это выражение заменяется числом 1 (единицей — номером января), выделенным курсивом (см. рисунок ниже).

В этом (втором) окне мастера вычислений выделяется подчёркиванием выражение Месяц(ДатаЖ). Имя ДатаЖ — это столбец-массив дат. Поэтому, нажав второй раз кнопку Вычислить, определим номера месяцев дат в этом столбце (см. рисунок ниже). Результатом двух этапов вычислений является следующее выражение (см. также рисунок ниже):

1 = {1:2:2:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1}.

Левой частью этого выражения является единица, а правая часть представляет собой константу массива. В этой константе массива каждой проводке соответствует три цифры — по одной цифре на каждую строку проводки (см. Табл. 15 на стр. 75). Для оживления картины первая проводка оформлена февральской датой. Поэтому в первой тройке цифр появились две двойки.

Нажав третий раз кнопку Вычислить, сравним значения первой и второй части фор­мулы массива — выражений Месяц(ДатаЖ) и Месяц(ДатаЖ). В результате получим константу массива, в которой значение Истина заменена для компактности знаком И, значение Ложь — знаком Л (см. рисунок ниже).

{И:Л:Л:И:И:И:И:И:И:И:И:И:И:И:И:И:И:И:И:И:И:И:И:И:И:И:И}

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

q Один из аргументов формулы является отдельным значением (скаляром). В этом случае скаляр автоматически увеличивается до размера второго аргумента формулы массива. Это событие называют авторасширением массива.

q В двух частных случаях, когда возможно авторасширение массива:

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

○ Один аргумент формулы массива является вектором, а второй двумерным массивом. В этом случае авторасширение массива возможно, если размер вектора равен размеру одной из сторон массива.

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

{1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1}

{1:2:2:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1} ( 2 )

Как видим, эти выражения отличаются значениями второго и третьего элементов. Поэтому второй и третий элементы вектора-результата сравнения этих выражений являются значениями Ложь.

Предоставляем читателю самостоятельно продолжить анализ процесса вычисления формулы массив ас помощью меню Сервис.

Как упростить анализ формулы массива

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

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

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

(Месяц(ДатаВ) = Месяц(ДатаЖ)) * (Номер Материалы = СчетЖ).

Результаты вычислений операндов формул можно просматривать с помощью двух инструментов Excel — в строке формул и в области формулы ячейки (непосредственно в самой ячейке). В любом случае надо включить режим правки значения ячейки (формулы) одним из двух способов:

q В первом случае надо выделить ячейку с формулой, которую надо проверить, и щёлкнуть по строке формул.

q Во втором случае сделать двойной щелчок по ячейке, или выделить ячейку с формулой и нажать клавишу F2.

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

Очевидно, что последовательный способ вывода результатов расчётов (длинных рядов единиц) затрудняет их сопоставление друг с другом. Поэтому было бы полезно преобразовать строку с результатами вычислений аргументов формулы массива в две строки, расположенные друг над другом. Тогда сопоставлять аргументы формулы массива будет легче (см. формулу ( 2 ) на с. 107).

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

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

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

Теперь надо передать числа в Word из Excel. Для обмена данными между программами Microsoft Office можно использовать три способа:

q Перетаскивание из окна одной программы в окно другой программы.

q Копирование через буфер обмена.

q Запуск программы из окна другой программы. Например, для запуска Word из Excel можно использовать команду Вставка–Объект, открыть вкладку Новый и выполнить команду Документ Microsoft Word.

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

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

q Вычислить клавишей F9 два приведённых ниже сомножителя выражения в первом аргументе функции Если():

(Месяц(ТекМесяц)=Месяц(ДатаЖ)) * (Номер Материалы=СчетЖ). ( 3 )

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

q Скопировать в буфер результаты вычисления сомножителей формулы ( 3 ).

q Запустить Word из Excel командой Вставка–Объект. Откроется окно Вставка объекта с двумя вариантами действий на двух вкладках:

○ Вставить файл (вкладка Новый, команда Документ Microsoft Word).

○ Связать файл (вкладка Из файла, флаг Связь с файлом).

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

q Вставить содержимое буфера в документ Word. На приведённом ниже рисунке показаны значения, вычисляемые первым множителем формулы ( 3 ) и начало второго множителя, отделённое от первого выражения знаком умножения * . Оба выражения заключены в фигурные скобки

q Разделить полученный ряд значений на две части, которые формируются двумя приведенными выше сомножителями. Эти части отделяются друг от друга знаками } * { (звездочка и пара фигурных скобок). Напомним, что фигурные скобки ограничивают константы массивов.

q Заменить значения Истина и Ложь на значения соответственно И и Л командой Правка­Заменить.

q Преобразовать числа в две строки таблицы. Для этого выделить числа и выполнить команду ТаблицаПре­обра­зо­вать–Текст в таблицу. Ввести в окне Преобразовать в таблицу знак разделителя (:) и установить переключатель По содержимому.

q Установить подходящий размер шрифта, например 10 пунктов.

q Вставить в таблицу строку сверху и пронумеровать в ней ячейки.

В результате получим примерно такой результат (см. таблицу ниже).

Во второй строке этой таблицы выводятся значения Истина, т.е. в журнале зарегистрированы операции только за январь. В третьей строке Истина выводится в ячейках 2, 12 и 20.

Посмотрев в журнал учёта операций, видим, что счет 10 используют в операциях 1, 4 и 7. Имена ДатаЖ и СчетЖ присвоены массивам-столбцам, начиная с третьей строки журнала учёта. Поэтому текущий счет в оборотной ведомости 10 совпадает со счетом 10 в журнале учёта в строках 2, 12 и 20.

Если Вас утомили эти процедуры, надо создать макрос для автоматизации этих действий.


Создание ведомости итогов по проводкам

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

Подготовка списка проводок

Вернемся к приведенному в начале раздела примеру журнала операций со счетами в строке (см. Табл. 16, с.78). Для получения списка типовых проводок с помощью расширенного фильтра надо выделить в журнале только номера счетовдѐбета икрѐ­дита. На­звания операций выделять нельзя, т.к. одной паре счетов может соответствовать несколь­ко операций с разными названиями. Например, см. типовые проводки 10 – 60 и 60 – 51, по которым зарегистрированы по две операции (проводки).

Далее фильтруем проводки так же, как при создании ведомости оборотов по счетам (см. выше раздел «Подготовка списка счетов» на с. 63).

Результат фильтрации (см. ниже Табл. 21) копируем в столбцы А2:В9 ведомости оборотов (см. ниже Табл. 22).

Табл. 21 — Список типовых проводок

Для автоматизации процедур фильтрации и копирования можно создать макрос и кнопку для его выполнения.

Расчет итогов по проводкам в месячном журнале

В этой задаче сравниваются номера связанных (корреспондирующих) счетов в двух списках проводок:

 








Не нашли, что искали? Воспользуйтесь поиском по сайту:



©2015 - 2024 stydopedia.ru Все материалы защищены законодательством РФ.