В данной статье продолжим знакомство с 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 должна быть читабельной. Для этого наиболее важную информацию нужно выделять, как в примере выше. Такой подход приводит к сокращению времени на осмысление и обработку данных, сокращению ошибок, к снижению усталости от работы с цифрами. Теперь мы вернулись к первоначальной задумке: создавать таблицы нужно так, чтобы они были информативными и легко читались.
Источник: https://statanaliz.info
Уважаемые коллеги, пожалуйста, присылайте ваши статьи, презентации и другие материалы для публикации на сайте BusinessForecast.by на электронный адрес info@businessforecast.by.