Сейчас практически любое приложение для учета и хранения данных выдает отчеты в формате Excel, но часто форма представления данных этих отчетов не удобна для работы.
Например, выгружает отчет в таком виде:
Проблемы формата с которыми трудно работать:
1) формат чисел не позволяет проводить с ними вычисления;
2) данные сгруппированы по Поставщику и Накладной, использовать фильтры Excel для группировки и анализа данных мы не можем, так как есть объединенные ячейки.
Для тоже, чтобы с данными можно было работать в Excel нужно сделать следующие преобразования:
1) Уберем объединение ячеек. Для этого выделим таблицу и нажмем кнопку "Объединить" по красной стрелке
В результате у нас получится так
2) Чтобы с этой таблицей можно было работать, нужно чтобы каждому поставщику в строке соответствовала накладная и материал, т.е. нам нужно заполнить пустые ячейки в первом и втором столбце.
Предварительно перенесем Поставщика 1 в строку с Накладной 1, выделим два первых столбца и нажмем клавишу на клавиатуре "F5" и кнопку "Выделить..."
В открывшемся диалоге выберем пункт "пустые ячейки"
После всего проделанного получится следующее:
Далее, в ячейке под "Накладная 1" ставим знак "=" нажимаем на клавишу на клавиатуре стрелка вверх (в строке формул будет отражаться ссылка на ячейку, содержащую "Накладная 1" )
Нажимаем сочетание клавиш "Ctrl+Ввод". В результате получаем таблицу.
Формулы в столбцах А и В нужно заменить на значения, для этого выделяем эти столбы полностью, нажимаем правую кнопку мыши выбираем пункт "Специальная вставка" и пункт "значения"
3) Убираем из таблицы ненужные строки. На этом этапе уже можно сделать для будущей таблицы "шапку". Далее установим на этой "шапке" фильтр" и в столбце "Материалы" выделим пусты строки. После чего эти строки нужно будет удалить.
После чего снимаем фильтр и получаем следующую таблицу.
Если получается такая таблица, значит в столбцах "Поставщик" и "Накладная" у вас остались формулы и нужно из заменить на значения.
3) Преобразуем форматы чисел, чтобы можно было работать с формулами
Выделяем ячейки "Кол-во" и "Цена", в левом верхнем углу должен появиться значок с восклицательным знаком
Нажимаем на значок с восклицательным знаком и выбираем пункт преобразовать в число. После этого, с цифрами в столбцах "Кол-во" и "Цена" можно будет работать (складывать, делить , умножать).
Убираем знак рубля в столбце "Сумма". Выделим его в тексте и нажмем скопировать
После чего удаляем символ рубля через диалог "Найти и заменить" В строку найти скопированный символ вставляем через "Ctrl+V"
Точка на запятую меняется также в диалоге "Найти и заменить", только предварительно нужно выделить столбец в котором будут проводится замены, так как точки и запятые могут использоваться в названиях номенклатуры в других столбцах.
После чего получает такая таблица
На рисунке не видно, но данные в столбце сумма - не числа, так как между разрядами стоит пробел. Просто преобразовать в число как со столбцами "Кол-во" и "Цена" не получается.
Выход следующий. Нужно скопировать пробел, как символ рубля в примере выше и заменить его через диалог "Найти и заменить". Предварительно нужно выделить этот столбец, чтобы использовать кнопку "Заменить всё" (если этого не сделать удаляться все пробелы в столбцах Поставщик, Номенклатура и Материалы)
Пробел выделяется так.
Потом его копируем и вставляем в диалог "Найти и заменить". Вставлять лучше через сочетание клавиш "Ctrl+V"
В итого получаем столбец с числами, остается только поменять формат на более красивый и удобный.
С полученной таблицей можно работать: устанавливать фильтры, вводить любые формулы, делать сводные таблицы. Вот некоторые примеры сводных таблиц
Для более быстрого поиска нужной статьи я сделала подборки:
Бюджетирование
Excel для работы с данными
Себестоимость и цены
Учет закупок и расчетов в Excel
Примеры в Excel и файлы к статьям можно найти по этой ссылке
