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
С полученной таблицей можно работать: устанавливать фильтры, вводить любые формулы, делать сводные таблицы. Вот некоторые примеры сводных таблиц.