Работа с таблицами в Excel

Вначале пару слов о файле Excel. Файл Excel называется книгой. В рамках книги (файла) есть листы Excel. Как в обычной бумажной книге есть листы, так и в файле Excel придумали: открываешь книгу, а там есть листы и их можно переворачивать. В целом удобно, наглядно и понятно. Каждый лист в отдельности – это здоровенная таблица. После создания файла или листа, эта исходная таблица пустая. Затем в неё вносятся некоторые данные. Это как детская мозаика – есть пустое полотно, которое заполняется нужным узором из фишек. Только в Excel вместо полотна используется пустой лист, а вместо фишек – данные, которые вносятся в ячейки таблицы.

Заполненные ячейки представляют собой диапазон данных. Сплошной диапазон данных, за пределами, которого отсутствует информация обычно и называется таблицей. У каждой ячейки есть свои координаты, то есть адрес, который представляет собой название столбца и строки, на пересечении которых и находится данная ячейка. Столбцы обычно обозначаются буквами, строки – числами. Ячейка A1 – это самая верхняя и самая левая ячейка, В1 – самая верхняя и вторая слева, C1 – самая верхняя и третья слева, A2 – вторая сверху и самая левая, A3 – третья слева и самая левая и так далее. Всё это пока бесполезная и всем очевидная информация. Но надо же с чего-то начать. Идём дальше.

Какую информацию можно поместить в ячейку? Многие считают, что цифры, буквы, формулы. Это так, но далеко не всё. Информацией, хранящейся в ячейке Excel, также являются: формат, ширина столбца, примечания и другое. Поэтому то, что видно невооруженным взглядом – это далеко не всё, что хранится в ячейке. Каждый параметр имеет свое назначение и может быть использован с большой пользой для дела. 

Итак, с помощью отдельных ячеек, в которых хранится определенная информация, строится таблица. У таблицы есть подлежащее и сказуемое, говоря по-простому, это названия строк и столбцов. Наличие данных атрибутов крайне желательно, так как это придаёт таблице смысл, то есть сообщает, о чём эти данные. Для того, чтобы информация из таблицы хорошо воспринималась, она должна соответствовать некоторым общепринятым правилам. Названия строк и столбцов обычно выделяются (цветом, шрифтом, рамкой), наличие сетки визуально помогает ориентироваться в таблице, полезно наличие итоговых строк и столбцов, размеры таблицы должны быть разумными и другое. Конкретных правил оформления нет, но создавать таблицы следует так, чтобы они легко читались, интерпретировались, распечатывались. Особенно это важно иметь в виду, когда таблицы передаются для работы третьим лицам, например, руководству, которое не любит долго разбираться, и желает, чтобы всё было предельно понятно без объяснений.

Рассказывать про таблицы словами можно очень долго и толку от этого никакого не будет. Поэтому переходим к наглядным примерам. Допустим, нам нужно проанализировать состояние товарных запасов на складе. То есть требуется определить, какие товары нужно заказать у поставщика, а каких товаров наоборот слишком много и их нужно побыстрее продать, чтобы позже не пришлось уничтожать из-за окончания сроков годности. На данном примере я покажу, как легко можно получить ценную информацию из большого массива данных и на её основе принять грамотные рациональные решения, которые принесут большую пользу (то есть много денег). Исходная информация – это, конечно, остатки товара на складе. Однако темпы продажи у разных товаров различные, поэтому данные только о количестве товара на складе не будут достаточно информативными. Один товар продается по 10 штук в месяц, другой – по 100 штук в месяц. Очевидно, что одинаковое количество товара на складе будет означать разный период продаж, на который хватит остатков. Поэтому для анализа нужно привлечь данные о темпах продаж. Обычно в этой роли выступают средние продажи за прошлый период. Их и возьмём.

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

 

Таблица исходных данных об остатках и продаж

 

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

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

 

Таблица остатков и продаж форматированная в рамку

 

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

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

Таким образом, в таблице можно выявить товары, которые следует быстрее продать, и товары, которые нужно срочно заказать у поставщика. Этот пример упрощён, в реальности объём данных и количество условий и ограничений в десятки и сотни раз больше. Ассортимент может состоять из тысяч позиций. Поэтому, даже если мы рассчитаем последний столбец, который показывает время, на которые хватит запаса, то у нас может уйти неделя на изучение состояние склада. А пока пройдёт неделя, ситуация поменяется и нужно будет снова обновлять данные. В общем, эта таблица хотя и отражает полезную информацию, но непригодна для оперативного использования. Для более точной, наглядной и быстрой работы в таблицу нужно внести дополнения. Одна из моих любимых функций Excel – условное форматирование. Её суть заключается в том, что при наступлении некоторого условия ячейка меняет свой формат (шрифт, цвет и проч.). Данная функция в автоматическом режиме обозначает критически важные значения, то есть товары, с которыми нужно срочно что-то сделать. Понятно, что все настройки и расчеты зависят от конкретной задачи. Нас, допустим, в первую очередь интересует недопущение дефицита, то есть максимально возможное наличие всего ассортимента на складе. Для этого нужно внимательно контролировать запасы, то есть не допускать, чтобы их уровень уменьшился ниже, чем 1 месяц продаж (время на пополнение).

Используя функцию условного форматирования, наша табличка может получить такой вид.

 

Таблица остатков и продаж и первое условие форматирования

 

Я ничего не раскрашивал вручную, честное слово. Только один раз задал условие и образец для раскрашивания, а Excel сам всё раскрасил. Отлично видно, что у трёх товаров наступил критический запас и дефицит неизбежен. Информация из такой таблицы воспринимается моментально, вероятность чего-то не заметить практически отсутствует. Однако тот факт, что товара меньше, чем на 1 месяц продаж, констатирует наличие уже имеющейся проблемы. Проблем же нужно стараться избегать, то есть заказывать товар следует тогда, когда его запас составляет немного больше чем 1 месяц продаж. В этом случае на момент прихода следующей партии на складе еще будет оставаться запас из предыдущей партии. Дефицита удастся избежать. Табличка заиграет новыми красками.

 

Таблица остатков и продаж с двумя условиями форматирования

 

Видно, что срочно нужно размещать заказ на 6 позиций. Причем 3 из них уже попадают в дефицит, заказ размещен слишком поздно. Но лучше, поздно, чем никогда. Яркость красок соответствует уровню опасности.

Представим теперь, что товар возится от разных поставщиков и заказы нужно планировать заранее, скажем, за 2 недели до отправки заявки. Поэтому когда запас товара меньше, чем 2 месяца продаж, но еще не достиг 1,5 месяца, нам нужно об этом знать. Легко!

 

Таблица остатков и продаж с тремя условиями форматирования

 

Зелененькие ячейки – это и есть товары, которые следует заказать в ближайшее время.

Итак, несложные манипуляции над исходной кучей "серых" однородных данных позволяют получить важную информацию о том, что нужно срочно заказать 6 позиций, и ещё 6 позиций планировать к заказу в ближайшие время.

Реальных условий и вариантов задач в сотни раз больше, но все они могут быть решены также быстро, как и в примере выше. Для этого нужно: а) знать методологию решения; б) эффективно использовать доступные инструменты в Excel.

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

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

 

Езепов Дмитрий
statanaliz.info

Перепечатка и перепостинг статьи вместе с этим текстом, указанием автора, и ссылки на первоисточник - приветствуются!