Добрый день, коллеги!
В сегодняшнем видео я хочу познакомить вас с самой необходимой функцией Excel для менеджеров по закупкам, логистике и категорийных менеджеров – это функция ВПР. Microsoft Excel – это программа для работы с электронными таблицами. Excel используется ежедневно и для расчетов, и для проведения анализа, и для сборов данных необходимых в удобном виде. Но вот интересный факт, при том что этот инструмент используется ежедневно, не так уж и много специалистов, которые могут сказать, что они очень хорошо владеют этим инструментом. Причин этому много, и, возможно в других материалах, я буду об этом рассказывать, а сегодня я хочу познакомить вас с самой необходимой функцией Excel – это функция ВПР.
Чем же она полезна?
Для тех, кто хочет стать мастером Excel и овладеть всеми необходимыми функции Excel для своей работы, повысить качество и скорость своей работы я подготовила практический онлайн-курс
Сила Excel для менеджеров по закупкам, логистике и категорийных менеджеров».
Онлайн-курс - это
16 занятий в формате видеоуроков по 20-30 минут.
Примечание: Вы можете скачать таблицу Excel из видеоурока. Оставьте в форме свой электронный адрес и получите таблицу с примером.
С ЭТОЙ СТАТЬЁЙ ЧИТАЮТ:
Расчет заказа поставщику по интуиции: добро или зло? Для тех, кто хочет быстро и качественно выполнять расчет заказа поставщикам, учитывая будущие продажи, срок выполнения заказа, периодичность размещения заказа, остатки на складе, остатки в пути я подготовила практический онлайн-курс “Управление за...
Планирование рабочего времени тайм-менеджмент (англ. time management ), эффективное организация и управление своим рабочим временем
Существует много разных подходов в теме планирования рабочего времени и тайм-менеджмента в excel , на бумаге или в специальных программах ,
ниже приведен один из таких подходов, успешно опробованный на практике и внедренный в повседневную жизнь. Для того, чтобы понять подходит он именно вам, протестируйте его на своих делах и задачах.
Тайм менеджмент в Excel и на обычном листе бумаги, а так же бесплатный таймер на ваш ноутбук или для вашего смартфорна это всё, что вам потребуется для эффективной организации своего времени, и всё это вы сможете найти, посмотреть и скачать на нашем сайте, все нужные ссылки есть на этой странице, начнем подробнее как организовать тайм менеджмент в excel.
Тайм-менеджмент в Excel файл заносим дела на неделю,
а дела на день смотрим в экселе и их удобнее выписывать в блокнот
За основу планирования рабочего времени и всех дел взята неделя , т.е. гибкое планирование на основе недели. Неделя является ключевым показателем в планировании рабочего времени : год-месяц-неделя -день.
Начнем с месяца:
В последний выходной месяца составляется список результатов (назовем каждый результат Проектом). Это желаемые результаты по итогам месяца, которых вы намерены достичь в течении нового месяца. (Определите для себя 3-5 главных результатов месяца).
Необходимо учесть достигнутые результаты по целям и допущенные ошибки за прошедший месяц при планировании дел в новом месяце, скорректируйте, улучшите цели и задачи при планировании нового месяца с учетом требуемых улучшений, назовем эти действия по улучшению КД (Корректировки к Действию) . Например: нужно было прочитать книгу, книгу прочитать не получилось, забывали, читали не каждый день. КД по этой задаче задаем: читаю каждый день по 10 страниц.
Список результатов на ближайший месяц - это ваши цели на месяц, когда вы их задали для себя и сформулировали, можно приступать к планированию их достижения.
Итак, раз в неделю, например в каждое воскресенье, составляется список задач в рамках каждого Проекта - это планирование недели . Эти задачи это элементарные шаги-действия на следующую неделю. Эти шаги-действия раскидывается по дням недели в файле эксель.
При планировании дел предстоящей недели так же нужно определить 3-5 главных результатов недели, это самые важные дела недели, по ним в конце недели вы поймете насколько эффективно прошла ваша неделя.
ведется в файле эксель планирование работы на неделю .
Каждый вечер перед уходом с работы открывается этот файл эксель и корректируется-дополняется план на следующий день (это планирование дня
).
После этого, задачи на предстоящий день из файла эксель выписывается на бумагу (лист планирование дел на день), это ваш утвержденный список дел на завтра.
Планирование рабочего времени - Тайм-менеджмент в excel и на бумаге - важные моменты:
1. Планирование рабочего времени тайм-менеджмент на основе недели - это гибкое планирование, можно и нужно корректировать-переносить задачи в рамках одной недели, это не наносит ущерба плана и ожидаемым результатам, это удобно делать в в excel файле.
2. Составлять план следующего дня накануне вечером - ключевой момент в организации времени. Утром не надо думать что делать все расписано, отключи голову, просто делай. Если нет четкого плана утром - обычно из-за думания что делать утром весь день скомкан и ничего не сделано. План следующего дня удобно выписывать из excel плана недели на обычный лист бумаги.
3. Выписывать задачи на предстоящий день на лист бумаги очень желательно, это ваш подписанный контракт с самим собой, ваше материальное обещание самому себе, да и потом - вычеркивать сделанные дела на листе бумаги это очень приятно.
4. При планировании дня, недели, месяца, года расставляйте приоритеты , выделите из всех планируемых задач три самых важных из них: запланируйте три самых важных результата дня, три самых важных результата недели, три самых важных результата месяца, три самых важных результата года. Это ваши самые важные цели, к которым вы движетесь.
При планировании самых важных результатов (дня, недели, месяца, года) используйте следующие вопросы:
- ДЛЯ ЧЕГО надо достичь результата?
- ЧТО ЗНАЧИТ УСПЕШНЫЙ РЕЗУЛЬТАТ (какие критерии успешного результата)
- КАК ДОСТИЧЬ (пошаговый план: делай раз, делай два, делай три)
Ожидаемый результат: критерии успешности вашего тайм-менеджмента
Сами задачи мы выполняем, чтобы достичь какого-то конкретного результата, иначе задачи это бессмыслица.
Результат лучше начинать планировать с «критериев успешного достижения результата».
Например, ожидаемый результат без критериев успешности достижения результата: прочитать книгу "Книга" , тогда задача: читать по 30 страниц в день
И при такой постановке задач ожидаемый результат может быть достигнут (книга будет прочитана), но качество результата возможно не утроит.
Поэтому на стадии планирования закладываем критерии успешности желаемого результата
:
1. книга прочитана до конца месяца
2. после прочтения составлена рецензия со списком идей для внедрения
3. полезные идеи внедрены в жизнь
задача к п.1. чтение под проект, найти минимум 3 новые идеи по вопросу "поиска новых клиентов"
задача к п.1 во время прочтения маркером выделять полезные идеи
задача к п.1. читать по 30 страниц в день
задача к п.2 проанализировать маркерные выжимки, составить рецензию на 1 лист
Excel - не самая дружелюбная программа на свете. Обычный пользователь использует лишь 5% её возможностей и плохо представляет, какие сокровища скрывают её недра. H&F почитал советы Excel -гуру и научился сравнивать прайс-листы, прятать секретную информацию от чужих глаз и составлять аналитические отчёты в пару кликов. (О"кей, иногда этих кликов 15.)
Импорт курса валют
В Excel можно настроить постоянно обновляющийся курс валют.
Выберите в меню вкладку «Данные».
Нажмите на кнопку «Из веба».
В появившемся окне в строку «Адрес» введите http://www.cbr.ru и нажмите Enter.
Когда страница загрузится, то на таблицах, которые Excel может импортировать, появятся чёрно-жёлтые стрелки. Щелчок по такой стрелке помечает таблицу для импорта (картинка 1).
Пометьте таблицу с курсом валют и нажмите кнопку «Импорт».
Курс появится в ячейках на вашем листе.
Кликните на любую из этих ячеек правой кнопкой мыши и выберите в меню команду «Свойства диапазона» (картинка 2).
В появившемся окне выберите частоту обновления курса и нажмите «ОК».
Супертайный лист
Допустим, вы хотите скрыть часть листов в Excel от других пользователей, работающих над книгой. Если сделать это классическим способом - кликнуть правой кнопкой по ярлычку листа и нажать на «Скрыть» (картинка 1), то имя скрытого листа всё равно будет видно другому человеку. Чтобы сделать его абсолютно невидимым, нужно действовать так:
Нажмите ALT+F11.
Слева у вас появится вытянутое окно (картинка 2).
В верхней части окна выберите номер листа, который хотите скрыть.
- В нижней части в самом конце списка найдите свойство «Visible» и сделайте его «xlSheetVeryHidden» (картинка 3). Теперь об этом листе никто, кроме вас, не узнает.
Запрет на изменения задним числом
Перед нами таблица (картинка 1) с незаполненными полями «Дата» и «Кол-во». Менеджер Вася сегодня укажет, сколько морковки за день он продал. Как сделать так, чтобы в будущем он не смог внести изменения в эту таблицу задним числом?
Поставьте курсор на ячейку с датой и выберите в меню пункт «Данные».
Нажмите на кнопку «Проверка данных». Появится таблица.
В выпадающем списке «Тип данных» выбираем «Другой».
В графе «Формула» пишем =А2=СЕГОДНЯ().
Убираем галочку с «Игнорировать пустые ячейки» (картинка 2).
Нажимаем кнопку «ОК». Теперь, если человек захочет ввести другую дату, появится предупреждающая надпись (картинка 3).
Также можно запретить изменять цифры в столбце «Кол-во». Ставим курсор на ячейку с количеством и повторяем алгоритм действий.
Запрет на ввод дублей
Вы хотите ввести список товаров в прайс-лист так, чтобы они не повторялись. Вы можете установить запрет на такой повтор. В примере указана формула для столбца из 10 ячеек, но их, конечно, может быть любое количество.
Выделяем ячейки А1:А10, на которые будет распространяться запрет.
Во вкладке «Данные» нажимаем кнопку «Проверка данных».
Во вкладке «Параметры» из выпадающего списка «Тип данных» выбираем вариант «Другой» (картинка 1).
В графе «Формула» вбиваем =СЧЁТЕСЛИ($A$1:$A$10;A1)<=1.
В этом же окне переходим на вкладку «Сообщение об ошибке» и там вводим текст, который будет появляться при попытке ввести дубликаты (картинка 2).
Нажимаем «ОК».
Выборочное суммирование
Перед вами таблица, из которой видно, что разные заказчики несколько раз покупали у вас разные товары на определённые суммы. Вы хотите узнать, на какую общую сумму заказчик по имени ANTON купил у вас крабового мяса (Boston Crab Meat).
В ячейку G4 вы вводите имя заказчика ANTON.
В ячейку G5 - название продукта Boston Crab Meat.
Встаёте на ячейку G7, где у вас будет подсчитана сумма, и пишете для неё формулу {=СУММ((С3:С21=G4)*(B3:B21=G5)*D3:D21)}. Сначала она пугает своими объёмами, но если писать постепенно, то её смысл становится понятен.
Сначала вводим {=СУММ и открываем скобки, в которых будет три множителя.
Первый множитель (С3:С21=G4) ищет в указанном списке клиентов упоминания ANTON.
Второй множитель (B3:B21=G5) делает то же самое с Boston Crab Meat.
Третий множитель D3:D21 отвечает за столбец стоимости, после него мы закрываем скобки.
Сводная таблица
У вас есть таблица (картинка 1), где указано, какой товар, какому заказчику, на какую сумму продал конкретный менеджер. Когда она разрастается, выбирать отдельные данные из неё очень сложно. Например, вы хотите понять, на какую сумму продано моркови или кто из менеджеров выполнил больше всего заказов. Для решения таких проблем в Excel существуют сводные таблицы. Чтобы её создать, вам нужно:
Во вкладке «Вставка» нажать кнопку «Сводная таблица».
В появившемся окне нажать «ОК» (картинка 2).
Появится окошко, в котором вы можете сформировать новую таблицу, используя только интересующие вас данные (картинка 3).
Товарный чек
Чтобы посчитать общую сумму заказа, можно поступить как обычно: добавить столбец, в котором нужно перемножить цену и количество, а потом посчитать сумму по этому столбцу (картинка 1). Если же перестать бояться формул, можно сделать это более изящно.
Выделяем ячейку C7.
Вводим =СУММ(.
Выделяем диапазон B2:B5.
Вводим звёздочку, которая в Excel - знак умножения.
Выделяем диапазон C2:C5 и закрываем скобку (картинка 2).
Вместо Enter при написании формул в Excel нужно вводить Ctrl + Shift + Enter.
Сравнение прайсов
Это пример для продвинутых пользователей Excel. Допустим, у вас есть два прайса, и вы хотите сравнить их цены. На 1-й и 2-й картинке у нас прайсы от 4 и от 11 мая 2010 года. Часть товаров в них не совпадает - вот как узнать, что это за товары.
Создаём в книге ещё один лист и копируем в него списки товаров и из первого, и из второго прайса (картинка 3).
Чтобы избавиться от дублей товаров, выделяем весь список товаров, включая его название.
В меню выбираем «Данные» - «Фильтр» - «Расширенный фильтр» (картинка 4).
В появившемся окне отмечаем три вещи: а) скопировать результат в другое место; б) поместить результат в диапазон - выберите место, куда хотите записать результат, в примере это ячейка D4; в) поставьте галочку на «Только уникальные записи» (картинка 5).
Нажимаем кнопку «ОК» и, начиная с ячейки D4, получаем список без дублей (картинка 6).
Удаляем первоначальный список товаров.
Вводим в колонку сравнения формулу =D5-C5, которая будет вычислять разницу (картинка 7).
Осталось автоматически загрузить в колонки «4 мая» и «11 мая» значения из прайсов. Для этого используем функцию: =ВПР(искомое_значение; таблица; номер_столбца; интервальный _просмотр).
- «Искомое_значение» - это строчка, которую мы будем искать в таблице прайса. Легче всего искать товары по их наименованию (картинка 8).
- «Таблица» - это массив данных, в котором мы будем искать нужное нам значение. Он должен ссылаться на таблицу, содержащую прайс от 4-го числа(картинка 9).
- «Номер_столбца» - это порядковый номер столбца в диапазоне, который мы задали для поиска данных. Для поиска мы определили таблицу из двух столбцов. Цена содержится во втором из них (картинка 10).
Интервальный_просмотр. Если таблица, в которой вы ищете значение, отсортирована по возрастанию или по убыванию, надо ставить значение ИСТИНА, если не отсортирована - пишете ЛОЖЬ.
Протяните формулу вниз, не забыв закрепить диапазоны. Для этого поставьте перед буквой столбца и перед номером строки значок доллара (это можно сделать, выделив нужный диапазон и нажав клавишу F4).
В итоговом столбце отражается разница в ценах по тем позициям, которые есть и в том и в другом прайсе. Если в итоговом столбце отражается #Н/Д, это значит, что указанный товар есть только в одном из прайсов, а следовательно, разницу вычислить невозможно.
Оценка инвестиций
В Excel можно посчитать чистый дисконтированный доход (NPV), то есть сумму дисконтированных значений потока платежей на сегодняшний день. В примере рассчитана величина NPV на основе одного периода инвестиций и четырёх периодов получения доходов (строка 3 «Денежный поток»).
Формула в ячейке B6 вычисляет NPV с помощью финансовой функции: =ЧПС($B$4;$C$3:$E$3)+B3 (картинка 1).
В пятой строке расчёт дисконтированного потока в каждом периоде находится с помощью двух разных формул.
В ячейке С5 результат получен благодаря формуле =C3/((1+$B$4)^C2) (картинка 2).
В ячейке C6 тот же результат получен через формулу {=СУММ(B3:E3/((1+$B$4)^B2:E2))} (картинка 3).
Сравнение инвестиционных предложений
В Excel можно сравнить, какое из двух предложений об инвестировании выгоднее. Для этого нужно выписать в два столбца требуемый объём инвестиций и суммы их поэтапного возврата, а также отдельно указать учётную ставку инвестирования в процентах. С помощью этих данных можно вычислить чистую приведённую стоимость (NPV).
В свободную ячейку нужно ввести формулу =npv(b3/12,A8:A12)+A7, где b3 - учётная ставка, 12 - число месяцев в году, A8:A12 - столбец с цифрами поэтапного возврата инвестиций, A7 - необходимая сумма вложений.
По точно такой же формуле рассчитывается чистая приведённая стоимость другого инвестпроекта.
Теперь их можно сравнить: у кого больше NPV, тот проект выгоднее.
Мне очень часто задают вопрос о том, какие навыки работы с Excel являются наиболее важными для маркетолога. Я выделила из огромного списка пять навыков, которые должен знать абсолютно каждый маркетолог и представила их ниже в возрастающем по важности порядке.
Форматирование таблиц
Я считаю, что форматирование таблиц является базовым навыком, которым должен владеть каждый маркетолог. На Search Engine Land я создала пост o том, как использовать форматирование таблиц , а также рассмотрела некоторые вопросы, почему это полезно знать маркетологам.
Все выглядит очень просто, но я редко замечала, чтобы маркетологи пользовались замечательными инструментами и услугами, которые предоставляет Excel. Подробнее об этом можно почитать тут - Microsoft для Excel 2010 для ПК и 2011 для Mac .
Диаграммы
Слишком часто я вижу, что маркетинговые данные представляют в основном в виде таблиц, когда на самом деле они должны быть представлены визуально.
Даже с большим набором данных, которые нелегко начертить, можно использовать условное форматирование, или по крайней мере добавить несколько подсказок о данных в таблицы. Но если вы не имеете дело с очень громоздким набором данных, вы должны найти любую возможность, чтобы визуально представить свои данные.
Я также настоятельно рекомендую посмотреть любое видео, которое сможете найти у Майка Гирвина (AKA ExcelIsFun) или Билла Еленя (AKA MrExcel). Майк сделал отличный видео-обзор различных типов диаграмм и то, как их создавать.
Сводные таблицы
Ох, человек, ты даже не хочешь вовлечь меня в разговор на тему сводных таблиц. Обычные таблицы сами по себе потрясающие - я даже не касаюсь набора данных без форматирования их в виде таблицы. Тем не менее, если вы хотите провести оценку огромного количества данных (которыми мы, маркетологи, каждый день просто завалены), вы должны обязательно научиться мастерству создавать сводные таблицы.
В случае, если вы никогда не создавали сводных таблиц или до смерти боитесь их, я рекомендую начать с просмотра моих видео-обзоров, которые я выложила в блоге. По сути, я беру тебя за руку и объясняю основы работы таблиц для Mac и PC в течение всего 13 мин.
Если вы хотите скачать файл Excel, с которым я работала в видео, то можете найти его на странице поста .
После того как вы начнете немного разбираться в этом, то можете найти в другом моем посте более подробную информацию на тему сводных таблиц, где также приложен файл Excel. Помимо этого, у Майка Гирвина есть отличное видео с 15-ю примерами создания сводных таблиц . Его видео применительно только для ПК и объяснения его ближе к сфере финансов, чем к маркетингу, но их легко можно применить во всех областях.
Функции
Итак, сделайте глубокий вдох…
В своем постах я писала о том, как писать сложные формулы Excel . Я боялась работать со сложными формулами в течение многих лет, но этот подход стал спасением для меня. Благодаря этому я выгляжу умнее, чем являюсь на самом деле просто потому, что работаю с этими действительно замечательными формулами маленькими детскими шагами.
Признаюсь, у меня были некоторые сомнения по поводу написания этого поста, потому что он раскроет все секреты по работе с этими длинными формулами, которые делают меня в глазах других людей легендой. Это можно сравнить с эпизодом из "Волшебника из страны Оз", когда открывается занавес и вы видите, что сам волшебник совсем не такой, каким вы представляли и его магия не такая уж сложная вещь. Но эта жертва стоит того, чтобы научить маркетологов навыкам решения сложных задач.
Расширенные фильтры
Самой большой моей жалобой в работе с Excel была невозможность использования в автофильтрах регулярных выражений (aka regex). Этот факт очень расстраивал меня, потому что я могла использовать regex в Word , но не в Excel. Однако позже я успокоилась, когда открыла силу расширенных фильтров Excel. Я не думала, что доживу до того дня, когда скажу это, но на самом деле они являются даже более мощными, чем регулярные выражения (regex).
Вы фактически можете фильтровать ваши данные, используя формулы в дополнение к текстовым фильтрам. И они гораздо легче в изучении, чем регулярные выражения.
Тем не менее, некоторые до сих пор боятся начать ими пользоваться, поэтому я написала пост о том, как использовать расширенные фильтры с большим количеством примеров из практики, ориентированных на маркетинг.
Расширенные фильтры поистине являются настоящей находкой при работе с большим набором данных. Раньше мне приходилось использовать сводные таблицы, составленные из всего набора данных, и затем просто фильтровать данные сводной таблицы. Проблема состоит в том, что сводные таблицы требуют много процессорного времени, и если у вас есть несколько сводных таблиц в книге, это может значительно замедлить работу Excel. Плюс ко всему, сводные таблицы дают вам очень низкие фильтрующие способности.
Теперь я создаю новые, отфильтрованные наборы данных (с помощью расширенных фильтров) и создаю из них сводные таблицы. Они становятся намного, намного быстрей!
Хотя усвоение этих навыков потребует некоторого времени, даже просто беглое знакомство с ними позволит вам выжать гораздо больше знаний из тех данных, с которыми вы работаете.
В программе обучения в формате курса:
Что такое эффективное управление асортиментом?
Как определить проблемные товарные группы, ранжировать поставщиков с помощью анализа товарных групп?
Что такое анализ и прогнозирование спроса?
Как увязать прогноз спроса, контроль остатков и календарь поставок?
Как провести расчет оптимального товарного запаса?
Как сделать в Excel простой план поставок?
Как сократить излишние запасы, снизить дефицит, обеспечивая наличие обязательного ассортимента?
Как провести АВС- анализ и сгруппировать по степени важности для компании большое количество позиций?
Как ввести в Excel план оплат?Автоматизированная аналитическая интерпретация АВС-анализа по Парето для Excel.
Автоматизированная аналитическая интерпретация АВСD-анализа методом касательных для Excel.
По какому алгоритму можно сделать расчет перемещения продукции из одного филиала в другой для увеличения прибыльности?
Программа курса повышения квалификации "Excel для логистов " изучается в рамках курса "Управление ассортиментом и поставшиками в рознице" и рассчитана на 20 часов.
В формате семинара изучается только часть вопросов, которые наиболее интересуют участников. Проблемные вопросы выявляются при предварительном анкетировании участников семинара.
Стоимость курса 9500 руб (20 час) . Курс проводится, как правило, в корпоративном формате для сотрудников одной компании. Дата по выбору заказчика.
Стоимость обучения на курсе 9000 руб
Программа практического семинара:
"Прогнозирование запасов в программе Excel для менеджеров по закупкам "
1.Анализ и прогнозирование спроса.
Краткосрочный прогноз на период от 1 до 14 дней, долгосрочный - до 12 месяцев. Прогноз спроса по конкретному товару или товарной группе, по одному складу или группе складов.
2.Расчет оптимального товарного запаса.
Использование методов математического моделирования для точного расчета оптимального товарного запаса учитывая: риски дефицита, сроки годности, стоимость хранения, распродажи, сезонность и тренды, праздничные дни, стоимость доставки, ограниченность финансовых ресурсов и другие критерии. С излишние запасы и снижает дефицит, при этом обеспечивает наличие обязательного ассортимента. Сокращение запасов, высвобождение оборотных средств, снижение дефицита и как следствие увеличение продаж.
3.Автоматическое формирование заказов.
Заказы на торговую точку и распределительный центр. Расчет оптимальных перемещений товаров между складами с учетом затрат на логистику и времени перевозки. Автоматическое формирование календаря заказов.
Прогнозирование запасов с учетом аналогов товаров, периодов отсутствия товара на складе, аномальных продаж, отсутствия товара на полке и других факторы.
4.Эффективное управление ассортиментом.
Выявление топовых позиций по заданным критериям на основе кросс-ABC-XYZ-анализа;
Нахождение проблемных товарных групп, ранжирование поставщиков с помощью анализа товарных групп и срезов;
Поиск неликвидов, выбывшего ассортимента, сверх запасов с использованием анализа ассортимента.
Стоимость участия в открытом семинаре 5500 руб (8 час)