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

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

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

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

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

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

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

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

Рис. 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

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

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

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