Excel для экономистов. 10 статей про отчеты, сверки, сопоставления

Введение

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

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

Excel в данном случае является инструментом, позволяющим облегчить работу с данными.

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

Кроме этой брошюры полезные рекомендации по работе с данными в Excel можете посмотреть на моем канале в Яндекс.Дзен «Excel для Экономистов».

1. Как преобразовать форматы и подготовить данные для анализа

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

Например, отчет выгружается в таком виде:

Рис. 1

В чем трудности работы с формой и форматами на рис. 1:

1) формат чисел не позволяет проводить с ними вычисления: в столбцах D и E данные представлены в нечисловом формате, то есть их нельзя суммировать, в столбце F ситуация более сложная (пробелы между разрядами, вместо «,» десятичный знак «.», есть символ валюты);

2) данные сгруппированы по Поставщику и Накладной, использовать фильтры Excel для группировки и анализа данных мы не можем, так как есть объединенные ячейки.

Для того чтобы с этими данными можно было работать, в Excel нужно сделать следующие преобразования:

1) убрать все группировки ячеек, так как они не позволяют работать с фильтрами и сводными таблицами. Для этого выделим таблицу и нажмем кнопку «Объединить» по красной стрелке.

Рис. 2

После отмены объединения данные по поставщикам будут находиться в столбце А, данные с номером накладной в столбце В, а данные с названиями материалов в столбце С. Но после отмены объединения таблица еще не готова к работе с фильтрами и сводными таблицами, то есть в таблице нет связи между Поставщиком, Накладной и Материалом.

2) Чтобы обеспечить связь между Поставщиком, Накладной и Материалом, нужно чтобы каждому Поставщику в строке соответствовала Накладная и Материал, то есть нам нужно заполнить пустые ячейки в первом и втором столбце.

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

Предварительно перенесем Поставщика 1 в строку с Накладной 1, выделим два первых столбца и нажмем клавишу на клавиатуре F5 и кнопку «Выделить…»

Рис. 3

В открывшемся диалоге выберем пункт «пустые ячейки».

Рис. 4

После всего проделанного пустые ячейки, которые требуется заполнить, будут выделены:

Рис. 5

Далее в ячейке под «Накладная 1» ставим знак «=», нажимаем на клавишу на клавиатуре стрелка вверх (в строке формул будет отражаться ссылка на ячейку, содержащую «Накладная 1»).

Рис. 6

Чтобы скопировать формулу во все выделенные ячейки, нажимаем сочетание клавиш Ctrl + Ввод. В результате получаем таблицу:

Рис. 7

Больше активная формула в этих ячейка нам не нужна, и ее нужно заменить на «значение» (чтобы избежать случайного изменения данных при работе с таблицей). Чтобы формулы в столбцах А и В заменить на значения, выделяем эти столбы полностью, нажимаем правую кнопку мыши и выбираем пункт «Специальная вставка» и пункт «значения».

Рис. 8

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

Рис. 9

3) Далее убираем из таблицы ненужные строки. На этом этапе уже можно сделать для будущей таблицы «шапку». Установим на этой «шапке» фильтр и в столбце «Материалы» выделим пустые строки. После чего эти строки нужно будет удалить.

Рис. 10

После чего снимаем фильтр и получаем следующую таблицу:

Рис. 11

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

4) Преобразуем форматы чисел, чтобы можно было работать с формулами.

Выделяем ячейки «Кол-во» и «Цена», в левом верхнем углу должен появиться значок с восклицательным знаком.

Рис. 12

Нажимаем на значок с восклицательным знаком и выбираем пункт «Преобразовать в число». После этого с цифрами в столбцах «Кол-во» и «Цена» можно будет работать: складывать, делить, умножать и использовать более сложные формулы.

Столбец «Сумма» требует больших преобразований.

Сначала убираем знак рубля в столбце «Сумма». Выделим его в тексте и нажмем скопировать.

Рис. 13

После чего удаляем символ рубля через диалог «Найти и заменить». В строку «Найти» скопированный символ вставляем через Ctrl + V. В пункт «Заменить на» ничего не ставим, так как значение нужно просто удалить.

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

Рис. 14

После чего получает такая таблица:

Рис. 15

На рисунке не видно, но данные в столбце «Сумма» – не числа, так как между разрядами стоит пробел. Просто преобразовать в число как со столбцами «Кол-во» и «Цена» не получается.

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

Как выделить и удалить пробел, показано на рисунке ниже:

Рис. 16

Потом его копируем и вставляем в диалог «Найти и заменить». Вставлять лучше через сочетание клавиш Ctrl + V.

Рис. 17

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

Рис. 18

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

2. Как сравнить два списка данных при помощи сводной таблицы

Сверка данных из разных отчетов, шаблонов, выгрузок порой занимает много времени и часто ставит в тупик, навевает тоску и безысходность. Хорошим помощником в сверке данных является Excel.

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

При выводе в Excel отчет из 1С выглядит как на рисунке.

Рис. 19

Для красоты формата некоторые столбцы объединены (например, нужные нам столбцы 1 и 2, 7 и 8). Для дальнейшей работы нам нужно снять объединение ячеек, то есть выделить эти столбцы или весь лист, нажав на область, выделенную синим прямоугольником. Когда нужное будет выделено, просто нажать на кнопку по красной стрелке.

В примере мы будем сравнивать июнь и июль. Для дальнейшей работы нам нужны:

• по ведомости за июнь столбцы Контрагенты и Дебетовое сальдо на конец;

• по ведомости за июль столбы Контрагенты и Дебетовое сальдо на начало.

Все остальные ненужные столбцы удаляем, а из нужных столбцов делаем новую таблицу:

Рис. 20

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

2. Столбец «Примечание» обязательный, так как он позволит разделить ваши данные на то, что сверяется, и на то, с чем сверяется.

Так как мы сверяем только два периода, то в «Примечание» только два признака «на конец» и на «на начало». Делаем сводную таблицу.

Рис. 21

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

2) На закладке «Вставка» нажимаем пиктограмму «Сводная таблица».

3) Выскакивает диалоговое окно «Создание сводной таблицы», наша подготовительная таблица при этом выделяется пунктирной линией (она должна выделиться полностью). Если в диалоговом окне все устраивает, нажимаем кнопку ОК.

4) Появляется диалог по настройке сводной таблицы. Делаем настройки как в блоке 5.

5) При этом в левом верхнем углу будет отображаться макет сводной таблицы и данные, которые туда попадут.

6) Формат значений настраивается в блоке 6.

7) В результате получим такую таблицу:

Рис. 22

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

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

Быстро выявить отклонения можно двумя способами:

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