Excel или аналитик: как заставить цифры работать на вашу компанию каждый день

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

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

[expert_bq id=»1570″]Компания собрала данные со своих автомобилей, подключенных к интернету, и на их основе определила, как именно люди нажимают на педали. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] *деятельность компании Meta Platforms Inc., которой принадлежит Инстаграм / Фейсбук, запрещена на территории РФ в части реализации данной (-ых) социальной (-ых) сети (-ей) на основании осуществления ею экстремистской деятельности

Big Data: что это и где применяется? – Блог SkillFactory

• Для многих компаний более актуальна стратегическая аналитика, к которой относится создание финмодели, иначе говоря, моделирование ближайшего финансового будущего предприятия. Это позволяет планировать открытие нового направления с минимальными рисками и оценить возможность достижения успеха. Моделирование подходит, например, для интернет-проекта при намерении использовать он-лайн каналы сбыта, для рекламной кампании, найма дополнительных сотрудников отдела продаж.
• Одна из актуальных задач — выгодно ли делать бесплатную доставку? В ряд регионов «цена курьера» может оказаться выше прибыли. С помощью анализа данных можно подобрать и оценить несколько вариантов решения. Например, разделить регионы на 3 типа: бесплатная доставка при определенном чеке; области, где можно оптимизировать тарифы за перевозку – выбрав другую компанию-перевозчика; отказаться от бесплатной доставки.

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

Формулы для больших таблиц

Дано. Таблица с заявками, которые обработали менеджеры. В столбце А номер заявки, В — фамилия менеджера, С — определенное количество заявок, D — цена. Цифры в каждой строке обозначают одну заявку.

Выбор формулы: = в ячейке → выбрать формулу курсором из выпадающего списка →
подтвердить Tab

1. Формула СЧЕТ(COUNT) → выделить столбец С.
2. Формула СЧЕТЕСЛИ (COUNTIF) → ⨏ открыть диалоговое окно мастера → диапазон — весь столбец с менеджерами → критерий — Бумажкин М. → переместить курсор в поле диапазон при первом подсчете.
3. Формула ВПР(VLOOKUP) → ⨏ открыть диалоговое окно мастера → искомое значение — ссылка на ячейку с номером заявки → таблица — выделить все столбцы таблицы → номер столбца — указать номер столбца с менеджерами → интервальный просмотр — 0 или ложь.

1. Формула СУММЕСЛИ(SUMIF) → ⨏ открыть диалоговое окно мастера → диапазон — выделить столбец с ценами → критерий — >0 → диапазон суммирования — выделить столбец с ценами.
2. Формула ВПР(VLOOKUP) → ⨏ открыть диалоговое окно мастера → искомое значение — номер ячейки с номером заявки и дописать &»» → таблица — выделить всю таблицу → номер столбца — номер столбца с менеджерами → интервальный просмотр — 0 или ложь.

[expert_bq id=»1570″]Объясняем простыми словами вместе с экспертом SkillFactory ведущим автором курса по машинному обучению, старшим аналитиком в КиноПоиске Александром Кондрашкиным. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] На листе вы сразу же увидите сформированную таблицу с группировками по столбцам, которые можно сворачивать, если их отображение в текущий момент не требуется. Видно, что таблица имеет свое оформление и точно заданные границы. В будущем вам может потребоваться увеличение или сокращение таблицы, поэтому вы можете редактировать ее параметры на вкладке «‎Конструктор».

13 функций Excel, которые должен знать каждый специалист — Блог SF Education

1. Сделать вспомогательную таблицу. Выделить столбцы с данными менеджеров в основной таблице → создать вспомогательную таблицу → вставить данные из основной во вспомогательную
2. Настроить отображение вкладки Разработчик. ПКМ на любой вкладке → настройка ленты → включить отображение вкладки Разработчик.
3. Добавить строку с именами менеджеров и флажками. Вставить — элементы управления — флажок → над вспомогательной таблицей прописать имя первого менеджера, флажок подтянется автоматически → ПКМ на имени менеджера → формат элемента управления → в окне Связь с ячейкой указать ячейку с именем менеджера. Сделать также для других менеджеров.
4. Настроить отображение графика. Формула ЕСЛИ → ⨏ открыть диалоговое окно мастера → Лог выражения — указать номер ячейки, где будет отображаться Истина или Ложь → ⨏ открыть диалоговое окно мастера → Значение_если_истина — данные по первому менеджеру → Значение_если_ложь — 0. Сделать также для всех менеджеров.

В 2024 году большие данные измеряют в петабайтах. Один петабайт равен миллиону гигабайт. Трехчасовой фильм в формате 4K «весит» 60‒90 гигабайт, а весь YouTube — 5 петабайт или 67 тысяч таких фильмов. 1 млн петабайт — это 1 зеттабайт.

Как работает технология Big Data?

Источники сбора больших данных делятся на три типа:

Все, что человек делает в сети, — источник социальных больших данных. Каждую секунду пользователи загружают в Инстаграм* 1 тыс. фото и отправляют более 3 млн электронных писем. Ежесекундный личный вклад каждого человека — в среднем 1,7 мегабайта.

Другие примеры социальных источников Big Data — статистики стран и городов, данные о перемещениях людей, регистрации смертей и рождений и медицинские записи.

Большие данные также генерируются машинами, датчиками и «интернетом вещей». Информацию получают от смартфонов, умных колонок, лампочек и систем умного дома, видеокамер на улицах, метеоспутников.

Транзакционные данные возникают при покупках, переводах денег, поставках товаров и операциях с банкоматами.

Как обрабатывают большие данные?

Массивы Big Data настолько большие, что простой Excel с ними не справится. Поэтому для работы с ними используют специальное ПО.

Его называют «‎горизонтально масштабируемым‎‎»‎, потому что оно распределяет задачи между несколькими компьютерами, одновременно обрабатывающими информацию. Чем больше машин задействовано в работе, тем выше производительность процесса.

Такое ПО основано на MapReduce, модели параллельных вычислений. Модель работает так:

• сначала данные фильтруются по условиям, которые задает исследователь, сортируются и распределяются между отдельными компьютерами (узлами);
• затем узлы параллельно рассчитывают свои блоки данных и передают результат вычислений на следующую итерацию.

MapReduce — не конкретная программа, а скорее алгоритм, с помощью которого можно решить большинство задач обработки больших данных.

Специалисты по большим данным используют оба инструмента: Hadoop для создания инфраструктуры данных и Spark для обработки потоковой информации в реальном времени.

[expert_bq id=»1570″]Выборка данных состоит в процедуре отбора из общего массива тех результатов, которые удовлетворяют заданным условиям, с последующим выводом их на листе отдельным списком или в исходном диапазоне. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Выделяем пустой столбец и кликаем правой кнопкой мыши, вызывая контекстное меню. В группе инструментов «Параметры вставки» выбираем пункт «Значения», изображенный в виде пиктограммы с цифрами.

Как в экселе сделать выборку из таблицы

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

В открывшемся окне форматирования открываем вкладку «Число». В блоке «Числовые форматы» выделяем значение «Дата». В правой части окна можно выбрать желаемый тип отображения даты. После того, как настройки выставлены, жмем на кнопку «OK».

Дашборд помогает решать задачи менеджерам по продажам, HR-специалистам, бухгалтерам, маркетологам, руководителям

Дашборд ― динамический отчёт, который состоит из структурированного набора данных и их визуализации на основе диаграмм, графиков и таблиц.

• представить набор данных максимально наглядным и понятным;
• держать под контролем ключевые бизнес―показатели;
• находить взаимосвязи, выявлять негативные и положительные тенденции, находить слабые места в организации рабочих процессов;
• давать оперативную сводку в режиме реального времени.

Построение дашбордов ― такой же hard skill, как владение формулами в Excel. По статистике, пользователь Excel среднего уровня может освоить этот навык за 20 часов обучения и практики.

Для специалистов, которые работают с отчётами, навык построения дашбордов стал необходимостью, а не дополнительным преимуществом.

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

Главное и самое интересное в дашборде ― интерактивность.

Настроить интерактивность можно с помощью следующих приёмов:

• срезы и временные шкалы в сводных таблицах ― эти инструменты упрощают фильтрацию данных и позволяют управлять дашбордом: например, можно более детально посмотреть данные по конкретному менеджеру или заказчику за определённый период времени или в разрезе каналов продаж.
• выпадающие списки, формулы и условное форматирование — использование таких приёмов удобно, когда много разных таблиц и построить сводные таблицы невозможно;
• спарклайны, мини-диаграммы в ячейках, тепловые карты в аналитических таблицах — такой способ чаще всего подходит для тактических целей специалистов или аналитиков, а не для стратегических целей руководителя.

[expert_bq id=»1570″]Рассмотрим на реальных кейсах как эффективнее решать управленческие задачи, используя анализ данных в Excel, и какие остаются подходы, когда его становится недостаточно. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] На листе Excel необходимо выделить область, среди данных, которых и нужно осуществить выборку. Во вкладке «Главная» нажимаете «Сортировка и фильтр» (находится в блоке настроек «Редактирование»). Далее нажимаете на фильтр.

Аналитика данных: как построить дашборд в Excel | Медиа Нетологии

Выделяем все пустые ячейки первой колонки новой таблицы. Устанавливаем курсор в строку формул. Как раз сюда будет заноситься формула, производящая выборку по указанным критериям. Отберем строчки, сумма выручки в которых превышает 15000 рублей. В нашем конкретном примере, вводимая формула будет выглядеть следующим образом:

Выполнение выборки

Выборка данных состоит в процедуре отбора из общего массива тех результатов, которые удовлетворяют заданным условиям, с последующим выводом их на листе отдельным списком или в исходном диапазоне.

Способ 1: применение расширенного автофильтра

Наиболее простым способом произвести отбор является применение расширенного автофильтра. Рассмотрим, как это сделать на конкретном примере.

1. Выделяем область на листе, среди данных которой нужно произвести выборку. Во вкладке «Главная» щелкаем по кнопке «Сортировка и фильтр». Она размещается в блоке настроек «Редактирование». В открывшемся после этого списка выполняем щелчок по кнопке «Фильтр».

Есть возможность поступить и по-другому. Для этого после выделения области на листе перемещаемся во вкладку «Данные». Щелкаем по кнопке «Фильтр», которая размещена на ленте в группе «Сортировка и фильтр».

После этого действия в шапке таблицы появляются пиктограммы для запуска фильтрования в виде перевернутых острием вниз небольших треугольников на правом краю ячеек. Кликаем по данному значку в заглавии того столбца, по которому желаем произвести выборку. В запустившемся меню переходим по пункту «Текстовые фильтры». Далее выбираем позицию «Настраиваемый фильтр…».

Давайте в качестве примера зададим условие так, чтобы отобрать только значения, по которым сумма выручки превышает 10000 рублей. Устанавливаем переключатель в позицию «Больше». В правое поле вписываем значение «10000». Чтобы произвести выполнение действия, щелкаем по кнопке «OK».

Как видим, после фильтрации остались только строчки, в которых сумма выручки превышает 10000 рублей.

Теперь в таблице остались только строчки, в которых сумма выручки не меньше 10000 рублей, но не превышает 15000 рублей.

Как видим, наш список ещё больше сократился. Теперь в нем оставлены только строчки, в которых сумма выручки варьируется от 10000 до 15000 рублей за период с 04.05 по 06.05.2016 включительно.

Мы можем сбросить фильтрацию в одном из столбцов. Сделаем это для значений выручки. Кликаем по значку автофильтра в соответствующем столбце. В выпадающем списке щелкаем по пункту «Удалить фильтр».

Как видим, после этих действий, выборка по сумме выручки будет отключена, а останется только отбор по датам (с 04.05.2016 по 06.05.2016).

В данной таблице имеется ещё одна колонка – «Наименование». В ней содержатся данные в текстовом формате. Посмотрим, как сформировать выборку с помощью фильтрации по этим значениям.

Кликаем по значку фильтра в наименовании столбца. Последовательно переходим по наименованиям списка «Текстовые фильтры» и «Настраиваемый фильтр…».

Как видим, в новой выборке существуют ограничения по дате (с 04.05.2016 по 06.05.2016) и по наименованию (картофель и мясо). По сумме выручки ограничений нет.

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

Второй вариант предполагает переход во вкладку «Главная». Там выполняем щелчок на ленте по кнопке «Сортировка и фильтр» в блоке «Редактирование». В активировавшемся списке нажимаем на кнопку «Фильтр».

При использовании любого из двух вышеуказанных методов фильтрация будет удалена, а результаты выборки – очищены. То есть, в таблице будет показан весь массив данных, которыми она располагает.

Способ 2: применение формулы массива

Сделать отбор можно также применив сложную формулу массива. В отличие от предыдущего варианта, данный метод предусматривает вывод результата в отдельную таблицу.

Выделяем все пустые ячейки первой колонки новой таблицы. Устанавливаем курсор в строку формул. Как раз сюда будет заноситься формула, производящая выборку по указанным критериям. Отберем строчки, сумма выручки в которых превышает 15000 рублей. В нашем конкретном примере, вводимая формула будет выглядеть следующим образом:

Естественно, в каждом конкретном случае адрес ячеек и диапазонов будет свой. На данном примере можно сопоставить формулу с координатами на иллюстрации и приспособить её для своих нужд.

Так как это формула массива, то для того, чтобы применить её в действии, нужно нажимать не кнопку Enter, а сочетание клавиш Ctrl+Shift+Enter. Делаем это.

Выделив второй столбец с датами и установив курсор в строку формул, вводим следующее выражение:

Аналогичным образом в столбец с выручкой вписываем формулу следующего содержания:

Во всех трех случаях меняется только первое значение координат, а в остальном формулы полностью идентичны.

В открывшемся окне форматирования открываем вкладку «Число». В блоке «Числовые форматы» выделяем значение «Дата». В правой части окна можно выбрать желаемый тип отображения даты. После того, как настройки выставлены, жмем на кнопку «OK».

В открывшемся окне выбираем тип правила «Форматировать только ячейки, которые содержат». В первом поле под надписью «Форматировать только ячейки, для которых выполняется следующее условие» выбираем позицию «Ошибки». Далее жмем по кнопке «Формат…».

В запустившемся окне форматирования переходим во вкладку «Шрифт» и в соответствующем поле выбираем белый цвет. После этих действий щелкаем по кнопке «OK».

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

Способ 3: выборка по нескольким условиям с помощью формулы

Как и в предыдущем способе, поочередно выделяем пустые столбцы новой таблицы и вписываем в них соответствующие три формулы. В первый столбец вносим следующее выражение:

В последующие колонки вписываем точно такие же формулы, только изменив координаты сразу после наименования оператора ИНДЕКС на соответствующие нужным нам столбцам, по аналогии с предыдущим способом.

Каждый раз после ввода не забываем набирать сочетание клавиш Ctrl+Shift+Enter.

Способ 4: случайная выборка

В Экселе с помощью специальной формулы СЛЧИС можно также применять случайный отбор. Его требуется производить в некоторых случаях при работе с большим объемом данных, когда нужно представить общую картину без комплексного анализа всех данных массива.

Эта функция выводит на экран случайное число. Для того, чтобы её активировать, жмем на кнопку ENTER.

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

Теперь у нас имеется диапазон ячеек, заполненный случайными числами. Но, он содержит в себе формулу СЛЧИС. Нам же нужно работать с чистыми значениями. Для этого следует выполнить копирование в пустой столбец справа. Выделяем диапазон ячеек со случайными числами. Расположившись во вкладке «Главная», щелкаем по иконке «Копировать» на ленте.

Выделяем пустой столбец и кликаем правой кнопкой мыши, вызывая контекстное меню. В группе инструментов «Параметры вставки» выбираем пункт «Значения», изображенный в виде пиктограммы с цифрами.

После этого, находясь во вкладке «Главная», кликаем по уже знакомому нам значку «Сортировка и фильтр». В выпадающем списке останавливаем выбор на пункте «Настраиваемая сортировка».

Как видим, выборку в таблице Excel можно произвести, как с помощью автофильтра, так и применив специальные формулы. В первом случае результат будет выводиться в исходную таблицу, а во втором – в отдельную область. Имеется возможность производить отбор, как по одному условию, так и по нескольким. Кроме того, можно осуществлять случайную выборку, использовав функцию СЛЧИС.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Первый способ: Применение расширенного автофильтра

На листе Excel необходимо выделить область, среди данных, которых и нужно осуществить выборку. Во вкладке «Главная» нажимаете «Сортировка и фильтр» (находится в блоке настроек «Редактирование»). Далее нажимаете на фильтр.

Можно сделать иначе, после выделения области переходите во вкладку «Данные» и нажимаете на «Фильтр», размещенной в группе «Сортировка и фильтр».

Когда эти действия выполнены, в шапке таблицы должны появиться пиктограммы для запуска фильтрования. Они будут отображены острием вниз небольшими треугольниками в правом крае ячеек. Нажимаете на этот значок в начале того столбца, по которому и собираетесь сделать выборку. Запуститься меню, в нем переходите в «Текстовые фильтры» и выбираете «Настраиваемый фильтр…».

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

После фильтрации остаются только те строчки, в которых сумма выручки превышает значение 10000 (как пример).

В этом же столбце есть возможность добавить и второе условие. Нужно снова вернуться в окно пользовательской фильтрации, а в его нижней части установить другую границу отбора. Переключатель выставляете в позицию «Меньше», а в поле справа вписываете «15000».

В таблице останутся только те строки, в которых сумма выручки не меньше 10000, но и не больше 15000.

В других столбцах выборка настраивается по аналогии. В нужном столбце нажимаете по значку фильтрации, а дальше последовательно кликаете по пунктам списка «Фильтр по дате» и «Настраиваемый фильтр».

Список теперь должен сократиться еще больше, потому что останутся только строки, в которых сумма выручки варьируется от 10000 до 15000 и это за период с 04.05 по 06.05.2016 включительно.

В одном из столбцов при желании можно сбросить фильтрацию. К примеру, можно сделать это для значений выручки. Нажимаете на значок автофильтра в соответствующем столбце. Выбираете «Удалить фильтр».

Выборка по сумме выручки отключится и останется только отбор по датам (с 04.05.2016 по 06.05.2016).

В таблице есть и еще одна колонка под названием «Наименование». В ней расположенные данные в текстовом формате. По этим значениям тоже можно сформировать выборку. В наименовании столбца нажмите на значок фильтра. Переходите на «Текстовые фильтры», а затем «Настраиваемый фильтр…».

В новой выборке выставлены ограничения по дате (с 04.05.2016 по 06.05.2016) и по наименованию (Мясо и Картофель). Ограничений нет только по сумме выручки.

Можно фильтр удалить полностью и делается это теми же способами, которые применялись для его выставления. Для того чтобы сбросить фильтрацию во вкладке «Данные» нажмите на «Фильтр» в группе «Сортировка и фильтр».

Во втором варианте можно перейти во вкладку «Главная» и нажать там на «Сортировка и фильтр» в «Редактирование». Далее кликаете на «Фильтр».

Если использовать любой из указанных методов, то таблица удалится, а результаты выборки очистятся. То есть таблица будет отображать все ранее внесенные в нее данные.

На том же листе Excel создаете пустую таблицу с теми же наименованиями столбцов в шапке, которые имеются у исходника.

Все пустые ячейки необходимо выделить в первой колонке новой таблицы. В строку формул устанавливаете курсор, чтобы занести формулу — =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000

[expert_bq id=»1570″]Кстати, на нашем курсе Финансовое моделирование мы рассказываем обо всех инструментах Excel, которые упрощают процесс построения финансовых моделей. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] 1. ВПР(А6; А1:С4; 3;0) Формула ищет значение «Иванов» в первом столбце таблицы А1;С4 и возвращает значение в строке 3 столбца. Результат функции – 22
2. ВПР(А6; А1:С4; 2;0) Формула ищет значение «Петров» в первом столбце таблицы А1;С4 и возвращает значение в строке 2 столбца. Результат функции – ВШЭ

Как научиться работать в Microsoft Excel самостоятельно

С помощью анализа больших данных в медиа измеряют аудиторию. В этом случае Big Data может даже повлиять на политику редакции. Так, издание Huffington Post использует систему, которая в режиме реального времени показывает статистику посещений, комментариев и других действий пользователей, а также готовит аналитические отчеты.