Каталог статей

Перед Вами обзор возможностей Excel, полезных бухгалтеру. Откройте программу и попробуйте выполнить наши примеры. Это действительно просто!

1. Как поменять цвет ячейки в зависимости от ее значения

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

Чтобы сделать подобное - выделяем ячейки, которые должны автоматически менять свой цвет и выбираем в меню Формат - Условное форматирование.

В открывшемся окне задаем условия и, нажав затем кнопку Формат, параметры форматирования ячейки, если условие выполняется. В этом примере крупных клиентов делаем полужирным синим шрифтом, а мелких - черным курсивом:

рис.1

В условии мы проверяем либо значение ячейки, либо заданную формулу.

Примеры условий:

формула = $А1 =, <, > выделяет цветом всю строку
формула = ячейка = МАКС/МИН ($A$1:$A$10) выделяет максимальные, минимальные значения
формула = ячейка <, >, = СРЗНАЧ ($A$1:$A$10) выделяет значения больше (меньше) среднего

2. Как выбрать из списка нужные записи

Первый способ – автофильтр

Скрываем все строки, за исключением тех, которые удовлетворяют заданным условиям отбора. Для этого выделяем любую ячейку списка и выбираем в меню Данные - Фильтр – Автофильтр.

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

рис.2

Автофильтр выполняет следующие функции:

- сортирует список по выбранному столбцу (выбираем «Сортировка»)
- находит заданное число (или процент) наибольших или наименьших элементов в списке (выбираем «Первые 10»)
- выполняет фильтрацию по заданному условию (выбираем «Условие»)

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

рис.3

Второй способ - расширенный фильтр

Чтобы использовать расширенный фильтр, прежде всего, зададим диапазон условий:

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

Например, чтобы отобрать из списка документы, проведенные в период 15 -16 января, в которых фигурирует счет 903003, нужно задать условие следующим образом:

Чтобы отфильтровать данные по критериям из нашей таблицы, выделяем исходный список и выбираем в меню Данные - Фильтр - Расширенный фильтр. Откроется окно Расширенного фильтра, в которое введем адрес исходного диапазона и адрес диапазона условий.

3. Функция СУММ суммирует весь заданный диапазон, как просуммировать только значения, соответствующие определенному условию?

В этом случае используем функцию СУММЕСЛИ. Рассмотрим конкретный пример. Допустим, необходимо рассчитать выручку, облагаемую НДС, по данной таблице:

Наши действия в этом случае таковы: используем функцию СУММЕСЛИ («ставка, %»,">0", «всего покупок»)

Синтаксис функции - СУММЕСЛИ (Диапазон; Критерий; Диапазон суммирования). Здесь «Диапазон» — ячейки, которые проверяются на соответствие критерию; «Критерий» — критерий в форме числа, выражения или текста; «Диапазон суммирования» - ячейки для суммирования.

Также мы можем посчитать количество счетов-фактур не с 0% НДС – используем функцию СЧЕТЕСЛИ («ставка, %»,">0")

Синтаксис функции – СЧЕТЕСЛИ (Диапазон; Критерий)

4. Как рассчитать выплаты по кредиту?

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

В примере мы рассчитываем сумму месячных выплат и полную сумму выплат.

Синтаксис функции ПЛТ (ежемесячная процентная ставка; число периодов; величина ссуды)

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

- функция КПЕР (Ставка; Платеж; Приведенная стоимость; Будущая стоимость; Тип) – вычисляет срок, за который можно расплатиться с кредитом, если есть данные о сумме кредита, ежемесячных выплат и проценте банка.

- функция СТАВКА (Периоды; Платеж; Приведенная стоимость; Будущая стоимость; Тип) - вычисляет, каков должен быть процент банка для кредита с заданными суммой, сроком и ежемесячной выплатой.

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

5. Как соотнести записи из одной таблицы с записями из другой?

Рассмотрим, данный вопрос на примере. Наш план счетов представлен в Excel в виде – счет, название. Перед обновлением программного обеспечения было решено удалить счета, по которым не было оборота в течение последних 3 лет. Теперь перед нами список счетов и к ним требуется скопировать наименования из первоначального плана счетов. Функция ВПР позволяет автоматизировать этот процесс.

Вырезка из первоначального плана счетов (1):

Счета, по которым был оборот в течение последних 3 лет (2) – 1000003 и 1100001.

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

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

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

рис.4

6. Как из огромной таблицы сформировать компактный и наглядный отчет?

Для этого мы используем сводные таблицы. Например, с помощью данного механизма очень удобно анализировать выгруженные из программы 1С проводки. Рассмотрим эту ситуацию подробнее.

Исходная таблица имеет вид:

рис.5

Чтобы построить сводную таблицу, ставим курсор в любую ячейку списка данных и выбираем пункт меню Данные – Сводная таблица. Далее следуем указаниям мастера создания сводных таблиц. В результате этой операции получаем шаблон отчета на отдельном листе:

рис.6

Работать с шаблоном несложно: перетаскиваем мышью названия столбцов (полей) из окна списка полей сводной таблицы в области строк, столбцов, страниц и данных макета:

1) в область строк таблицы данных перетаскиваем поле «Дебет»;
2) в область столбцов помещаем поле «Месяц»;
3) в область данных - поле «Сумма» и т.д.

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

Отчет можно детализировать, перетащив в область строк любое дополнительное поле, например, счет кредита, субконто и т.д.

рис.7

7. Как быстро подсчитать итоги в большом списке?

Когда количество показателей в таблице не больше 3-4, удобней вместо сводных таблиц использовать механизм Промежуточных итогов. Действуем по следующей схеме:

Первый шаг – Сортировка

Сначала отсортируем таблицу, чтобы сформировать группы строк, по которым в дальнейшем будем подсчитывать итоги – Данные – Сортировка

Второй шаг – Подведение итогов

Далее идем в меню Данные – Итоги:

рис.8

В этом диалоговом окне мы указываем поле, по которому отсортирована наша таблица, выбираем математическую функцию (сумма, среднее, минимум-максимум, количество и т.д.), отмечаем галочками те столбцы, по которым необходимо подвести итоги.

Нажимаем на клавишу ОК и получаем таблицу, в которую автоматически включены итоги.

При подведении итогов Excel сам производит группировку. Отсортированные группы быстро и удобно сворачивать-разворачивать с помощью знаков "плюс" и "минус" слева от таблицы. В свернутом состоянии отчет выглядит более компактно и информативно.