Внимание! Ввод формулы должен быть завершен одновременным нажатием клавиш Ctrl+Shift+Enter. Эта комбинация клавиш сообщает Excel, что мы ввели не обычную формулу, а так называемую формулу массива. Если просто нажать Enter, то нужного результата мы не достигнем.

Обратите внимание, что после нажатия Ctrl+Shift+Enter в строке формул наша формула заключена в фигурные скобки (рис. 6.25).

Рисунок 6.25 – Отображение формулы массива в строке формул Excel

Наличие обрамляющих фигурных скобок означает, что Excel воспринимает содержимое ячейки именно как формулу массива. Фигурные скобки для формул массива нельзя вводить вручную. Если это сделать, то Excel будет воспринимать введенное как обычный текст. Формулу массива можно ввести, только закончив ее ввод нажатием Ctrl+Shift+Enter. Если нужно внести в такую формулу изменения, то после корректировки также следует нажать Ctrl+Shift+Enter.

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

Сначала остановимся на сути решаемой задачи.

А суть состоит в том, что надо подвести итог по всем суммам проводок на листе «Проводки», у которых код дебетуемого счета совпадает с кодом счета из данной строки столбца A («Дебет») листа «Обороты», а код кредитуемого счета листа «Проводки» с кодом счета данной строки столбца B («Кредит»). Например, проводка в дебет счета 19 с кредита счета 60 в нашем примере на листе «Проводки» встречается дважды – в строке 3 и в строке 12. В первом случае сумма проводки равна 2000, а во втором 4000. Именно эти числа и должны быть просуммированы.

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

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

Первым сомножителем является функция ЕСЛИ:

ЕСЛИ(Проводки!$A$2:$A$5000=$A2;1;0)

Эта функция, в свою очередь, имеет три аргумента. Первый определяет условие (Проводки!$A$2:$A$5000=$A2), второй – значение, которое следует использовать при выполнении этого условия (1), а третий – значение, которое соответствует случаю, когда условие не выполняется (0).

Проверяемое условие задано в форме сравнения диапазона (Проводки!$A$2:$A$5000) с единственным значением ($A2). Именно использование диапазона и позволяет Excel сформировать промежуточный массив. Фактически, значение первого из сомножителей суммируемого массива определяется последовательным сравнением значения из ячейки $A2 листа «Обороты», с ячейками из диапазона $A$2:$A$5000 листа «Проводки». Если их значения совпадают, то первый сомножитель равен 1, в противном случае – нулю. Таким образом, функция ЕСЛИ, заданная в приведенной выше форме, создает массив значений, состоящий из нулей и единиц. Далее, каждый элемент этого массива используется как один из сомножителей при получении массива элементов, суммируемых функцией СУММ.

---

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

Второй сомножитель используемый при неявном формировании массива суммируемых функцией СУММ значений также задается функцией ЕСЛИ:

ЕСЛИ(Проводки!$B$2:$B$5000=$B2;1;0)

Эта функция, в свою очередь, также строит массив из нулей и единиц, в зависимости от совпадения или несовпадения кода счета из ячейки $B2 листа «Обороты» с кодом кредитуемого счета таблицы листа «Проводки».

Третий аргумент функции СУММ (Проводки!$C$2:$C$5000) определяет массив значений, в точности соответствующий столбцу C («Сумма») листа «Проводки». Его элементы являются третьим сомножителем при формировании массива значений, суммируемых функцией СУММ.

Таким образом, аргументы функции СУММ строят три массива одинаковой длины. Их значения, имеющие одинаковые номера перемножаются. В результате получается массив такого же размера, содержащий произведения соответствующих элементов. Каждый элемент равен сумме соответствующей проводки тогда и только тогда, когда ее дебетуемый счет совпадает с кодом счета из ячейки $A2, а кредитуемый счет – с кодом счета из ячейки $B2 листа «Обороты». В противном случае элемент суммируемого массива равен нулю, поскольку равен нулю хотя бы один из первых двух сомножителей. Следовательно, получается именно то, что нам нужно: итог по всем проводкам с заданной корреспонденцией счетов.

Для того чтобы окончательно уяснить как работает используемая нами формула внимательно изучите следующую таблицу, которая иллюстрирует процесс формирования Excel промежуточных массивов, используемых при расчете итога по проводкам в дебет счета 19 с кредита счета 60 на основе данных нашего примера.

Таблица 6.1 – Процесс формирования Excel промежуточных массивов, используемых при расчете итога по проводкам в дебет счета 19 с кредита счета 60 на основе данных нашего примера

Проводка			Первый сомножитель	Второй сомножитель	Третий сомножитель	Результат
Дебет	Кредит	Сумма
08	60	10000	0	1	10000	0
19	60	2000	1	1	2000	2000
01	08	10000	0	0	10000	0
60	51	12000	0	0	12000	0
68.1	19	2000	0	0	2000	0
62	46	60000	0	0	60000	0
46	68.1	10000	0	0	10000	0
46	41	40000	0	0	40000	0
51	62	50000	0	0	50000	0
41	60	20000	0	1	20000	0
19	60	4000	1	1	4000	4000
60	51	24000	0	0	24000	0
68.1	19	4000	0	0	4000	0
Результат функции СУММ						6000

---

 

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

 

Рисунок 6.26 – Итоги по проводкам с одинаковой корреспонденцией счетов

Тесты для самопроверки

1. Какие преимущества дает использование электронных таблиц в MS Excel?

а) наглядность представления данных;

б) оперативность пересчетов;

в) полная управляемость процессом вычислений;

г) все перечисленные ответы являются верными.

2. Какой первый этап в технологии учета основных средств в MS Excel?

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

б) таблица дополняется колонкой, в которой проставляются соответствующие счета, на другом листе рабочей книги должны быть определены шаблоны проводок;

в) файл рабочей книги копируется, ему присваивается новое имя;

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

3. Можно ли с помощью MS Excel автоматически рассчитывать износ?

а) да;

б) нет.

4. Какую функцию стоит использовать для начисления износа в MS Excel:

а) СУММ ();

б) ЕСЛИ ().

5. Верно ли утверждение «Интервал просмотра – это интервал ячеек, среди которых осуществляется поиск определенной информации»?

а) да;

б) нет.

6. Какая функция используется при учете материальных ценностей в MS Excel?

а) СУММ ();

б) ЕСЛИ ();

в) ВПР ().

7. Можно ли с помощью MS Excel выделять НДС?

а) да;

б) нет.

8. Можно ли в MS Excel составить оборотную ведомость?

а) да;

б) нет.

9. Можно ли в MS Excel составить финансовые документы?

а) да;

б) нет.

10. Можно ли в MS Excel составить подсчет сумм проводок?

а) да;

б) нет.

---

Смотрите также файлы

• РАБОТА 4 Диаграмма прецедентов.docx

• Проектный практикум Самостоятельная работа.docx

• Задание на КР ОИТ.pdf

• Курсовая Проектирование базы данных туристического агентства.doc

• Контрольная работа Математические методы теории сетей связи.pdf