Эксель полезные функции: 21 полезная функция Excel и «Google Таблиц» для интернет-маркетологов

Полезные функции и фишки в Экселе для SEO и арбитража

В работе арбитражника и SEO-специалиста приходится иметь дело с огромными объемами данных. Чем больше их получено, обработано и проанализировано, тем выше вероятность успеха в рекламной кампании или продвижении сайта. А самой удобной, доступной и надежной программой для работы с большими массивами данных остается Эксель.

Мы решили собрать полезные функции и фишки Excel, которые могут сильно облегчить вам жизнь и упростить работу. 

Разумеется, мы не сможем перечислить их все. Их попросту слишком много. Например. при помощи этой программы вы можете создать изображения, например, для креативов. 

Звучит фантастически, но это действительно создано в Экселе!

Так что давайте остановимся на вещах более призёмленных и полезных в ваших рутинных задачах.

Полезные функции Эксель

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

Разберем это на конкретном примере. Скажем, нам нужно посчитать некую сумму.

Мы выделяем свободную ячейку и жмем кнопку вызова функции.

Выбираем саму функцию. В нашем случае, это СУММ.

Выбираем ячейки, к которым будет применена функция. В нашем случае от B42 до B47.

Жмем ОК. И всё, Эксель посчитал для нас эту сумму.

Посчитать сумму

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

Выделяйте нужный вам диапазон + одну ячейку снизу, жмите вот сюда.

И в этой свободной ячейке Эксель вам выдаст сумму.

Помимо нее на этой кнопке расположены: расчет среднего значения, поиск максимального и минимального, а также счетчик (т.е. сколько всего значений вы выделили).

Узнать, сколько значений выше какого-то параметра

Например, вы загрузили в Эксель большой массив ключевиков. И вам необходимо выяснить, сколько из них, например, больше 100.

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

Например, во втором столбце у нас идет количество запросов по ключевику. В функции мы задаем диапазон — то есть весь второй столбец. И критерий — больше 100.

Эксель нам показывает, что всего лишь 8 запросов подходят под такой критерий.

Выявление дубликатов

При работе с большими объемами, а также при сведении нескольких таблиц в одну может возникнуть проблема появления дублирующихся значений. Для этого у Экселя есть удобная функция — перейдите в раздел «Главная», нажмите «Условное форматирование», далее «Правила выделения ячеек» и «Повторяющиеся значения…».

Так вот — все повторяющиеся значения Эксель вам сразу же подсветит.

Найти значения выше определенного параметра

Например, вы хотите работать только с СЧ запросами. И вам нужно быстро выбрать только те графы, в которых число больше 1000. 

У Экселя для этого есть полезная вещь. Выделяйте все ячейки, и справа снизу у вас появится меню.

В нем выбирайте кнопку «Больше», задавайте диапазон — в  нашем случае, больше 1000. И Эксель сам подсветит нужные графы.

Фишки Excel

Заменить слово во всем документе

Предположим товарка, на которую вы лили трафик, поменяла свое название. И вам просто очень лень переписывать все тексты, которые вы уже составили под нее. Но у Excel есть весьма полезная фишка: массовая замена слов.

Для этого вам просто нужно будет нажать сочетание клавиш Ctrl+H (либо Cmd+H на маке).

И просто вводите в поля то, что заменить и то, на что заменить. 

Здесь важно помнить, что заменять он будет только по точному вхождению слов. Условно, если вы пишите только слово «эксель», то он и поменяет слово “эксель”, а например, “экселем” не тронет. 

Сделать буквы строчными или прописными

Раньше эта функция работала во всех программах от Майкрософт — нужно было просто нажать Shift+F3. Однако сейчас, чтобы заменить регистр букв придется повозиться.

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

Объединение ячеек

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

Вы можете объединить их при помощи функции СЦЕПИТЬ. Эта фишка позволит сгруппировать данные из нескольких ячеек в одну.

Расширить ячейку

На самом деле, для многих действительно головная боль, когда ячейки в Excel  выглядят вот так:

Однако справиться с этим невероятно просто: нужно всего лишь потянуть за границу столбца и увеличить его.

Вставить текст в Excel 

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

Визуализация данных

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

Для этого выделите те области таблицы, которые нужно визуализировать, нажмите «Вставка» и выберите удобной для вас формат диаграммы. Выглядеть это будет вот так.

Закрепить области

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

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

Горячие клавиши в Эксель

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

• F9 — пересчет формул во всей книге 
• SHIFT+F9 — пересчет формул на текущем листе 
•  F2 — редактировать активную ячейку 
• SHIFT+F2 — редактировать примечание к ячейке 
• CTRL+Delete — удалить текст от курсора до конца строки 
• CTRL+ знак плюс —  добавить выделенное количество строк или столбцов
• CTRL+ знак минус — удалить выделенные строки или столбцы 
• CTRL+H — «Найти и заменить» 
• SHIFT+F11 — вставить пустой лист 
• CTRL+Z — отменить последнее действие
• CTRL+Y — повторить отмененное действие
• ALT+SHIFT+ «→» — сгруппировать выделенные строки/столбцы
• ALT+SHIFT+ «←» — разгруппировать выделенные строки/столбцы 
• CTRL+1 — «Формат ячеек»
• CTRL+SHIFT+~ — общий формат 
• CTRL+SHIFT+$ — денежный формат 
• CTRL+SHIFT+% — процентный формат 
• CTRL+SHIFT+# — формат даты 
• CTRL+SHIFT+@ — формат времени 
• CTRL+SHIFT+! — числовой формат 
• CTRL+B — полужирный 
• CTRL+I — курсив 
• CTRL+U — подчеркивание 
• CTRL+5 — зачеркнутый текст 
• CTRL+Enter — ввод сразу во все выделенные ячейки 
• ALT+Enter — перенос строки внутри ячейки (при вводе) 
• CTRL+SHIFT+4 — вставить текущую дату 
• CTRL+SHIFT+; — вставить текущее время 
• CTRL+Backspace — вернуться к активной ячейке (прокрутка) 
• CTRL+PgUp/PgDown — переключение между листами 
• CTRL+Tab — Переключение между открытыми книгами 
• CTRL+пробел — выделить весь столбец 
• SHIFT+пробел — выделить всю строку 
• CTRL+A — выделить текущую область 
• CTRL+SHIFT+End — выделить все до последней ячейки 
• CTRL+стрелки — перейти до конца/начала строки/столбца 
• CTRL+SHIFT+стрелки — выделить до конца/начала строки/столбца
• CTRL+N — создать новую книгу
• CTRL+S — сохранить 
• CTRL+O — открыть файл 
• F4 — повтор последнего действия
• CTRL+K — вставить гиперссылку 

Заключение

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

Эта программа действительно прекрасно подходит для создания таблиц и, что очень важно, для глубокой аналитики. А именно это просто необходимо для успешной работы любому SEO-специалисту и арбитражнику.

А какими полезными вещами и фишками в программе Эксель пользуетесь вы? Поделитесь в комментариях.

Полезные функции Excel, о которых многие даже не догадываются

Фото носит иллюстративный характер, источник: pixabay.com Фото носит иллюстративный характер, источник: pixabay.com

07:00 25.08.2021

10725

• Кошелек

Содержание

• Excel для домашней бухгалтерии
• Excel в более продвинутом варианте – для небольшого бизнеса
• Что еще стоит знать?

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

Excel для домашней бухгалтерии

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

В этой форме почти все ячейки суммируют цифры многих операций. Это, конечно, можно делать по старинке на калькуляторе, но проще ввести в ячейку знак «=», потом набрать цифру и поставить знак «+», после еще одну и так далее, а в конце нажать Enter. В верхней суммирующей строке будет текст такого типа «=5,50+23,50+11,20».

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

Кроме этого с помощью Excel можно высчитывать какие-то средние величины (например, среднюю зарплату, средние расходы на продукты в день и так далее): для этого нужно кликнуть на треугольник у знака суммы в меню. Также готовые формулы есть в соответствующем разделе панели программы.

Excel в более продвинутом варианте – для небольшого бизнеса

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

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

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

Чтобы построить диаграмму достаточно выделить диапазоны данных и клинуть на верхней панели «Вставить», потом выбираете вставку диаграммы и получаете такое предложение, где нужно выбрать нужную вам диаграмму.

Что еще стоит знать?

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

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

ДоходыФинансы

Еще больше новостей – в нашем Telegram-канале

Подписаться на канал

Поделитесь своей новостью или «денежной» историей через @myfinby_bot

Оцените статью:

Свежие новости по теме

Выплаты и пособия

В Беларуси вырастут пособия по уходу за ребенком – когда и на сколько? В ближайшее время в нашей стране вырастут размеры пособия по уходу за ребенком в…

Вчера, 20:40

1998

Чтение: 1 мин.

Выплаты и пособия

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

Вчера, 17:38

516

Чтение: 1 мин.

Мои деньги

«Те, кто обещает доходность 20% в месяц просто врут»: как белорус зарабатывает на криптовалютах Константин Костюченко – серийный предприниматель, запускал несколько бизнесов,. ..

Вчера, 10:00

7726

Чтение: 10 мин.

Экономика

Глава Минфина России предсказал сверхплановый дефицит бюджета и недобор по нефтегазовым доходам в 2023 году В интервью министр финансов России Антон Силуанов допустил, что дефицит федерального…

Вчера, 09:41

1440

Чтение: 1 мин.

Главное сегодня

Недвижимость

Нынешний год может стать рекордным по продажам квартир. Что с ценами в Минске и регионах? Как рассказали в Национальном кадастровом агентстве, во II квартале активность на…

Вчера, 15:14

1308

Чтение: 2 мин.

Налоги

Нужно ли платить налоги с вознаграждения за находку? Разбираемся на реальных примерах Возвращая найденную вещь или питомца хозяину, большинство из нас получают удовольствие…

Вчера, 07:00

3114

Чтение: 3 мин.

Репортаж

Запрещено смотреть на голодный желудок: как белорусы провели фестиваль Vulitsa Ezha В минувшие выходные в Минске прошел второй за лето городской пикник Vulitsa Ezha. И если…

Вчера, 15:17

1096

Чтение: 7 мин.

Обзоры

Допвалюты для кредитов и вкладов, снижение комиссий за снятие наличных – обзор банковских изменений В традиционном обзоре – в этот раз довольно кратком – уделяем основное внимание…

Вчера, 16:44

942

Чтение: 2 мин.

Отправить редакции myfin.by сообщение об ошибке в тексте

Текст:

Комментарий

15 ФУНКЦИЙ АНАЛИЗА ДАННЫХ EXCEL, КОТОРЫЕ НУЖНО ЗНАТЬ

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

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

 Независимо от того, балуетесь ли вы Excel или интенсивно используете его в своей работе, в этом списке найдется функция для каждого.

 1. СЦЕПИТЬ

 =СЦЕПИТЬ — одна из самых простых в освоении, но наиболее эффективных формул при проведении анализа данных. Объединяйте текст, числа, даты и многое другое из нескольких ячеек в одну. Это отличная функция для создания конечных точек API, артикулов продуктов и запросов Java.

 Формула:

= СЦЕПИТЬ (ВЫБЕРИТЕ ЯЧЕЙКИ, КОТОРЫЕ ХОТИТЕ ОБЪЕДИНИТЬ)

 2. ДЛСТР

 =ДЛСТР быстро предоставляет количество символов в данной ячейке. Как и в приведенном выше примере, вы можете определить два разных типа товарных складских единиц (SKU), используя формулу =LEN, чтобы узнать, сколько символов содержит ячейка. LEN особенно полезен при попытке определить различия между различными уникальными идентификаторами (UID), которые часто бывают длинными и расположены не в правильном порядке.

Формула:

=ДЛСТР(ВЫБРАТЬ ЯЧЕЙКУ)

3. COUNTA

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

 Формула:

=СЧЁТ(ВЫБЕРИТЕ ЯЧЕЙКУ)

4. ДНИ/ЧИСТРАБДНИ

 =ДНИ – это именно то, что подразумевается. Эта функция определяет количество календарных дней между двумя датами. Это полезный инструмент для оценки жизненного цикла продуктов, контрактов и дохода от рейтинга пробега в зависимости от продолжительности обслуживания — анализ данных необходим.

 =ЧИСТРАБДНИ немного надежнее и удобнее. Эта формула определяет количество «рабочих дней» между двумя датами, а также возможность учета праздников. Даже трудоголикам время от времени нужен перерыв! Использование этих двух формул для сравнения временных рамок особенно полезно для управления проектами.

5. СУММЕСЛИМН

=СУММЕСЛИМН — это одна из формул, которую должен знать аналитик данных. Обычно используется формула =СУММ, но что, если вам нужно суммировать значения на основе нескольких критериев? СУММЕСЛИ — это. В приведенном ниже примере функция СУММЕСЛИМН используется для определения вклада каждого продукта в общий доход.

 Формула:

=СУММЕСЛИ(ДИАПАЗОН,КРИТЕРИИ,[сумма_диапазон])

6. СРЗНАЧСЛИМН

 Как и СУММЕСЛИМН, СРЗНАЧСЛИМН позволяет получить среднее значение на основе одного или нескольких критериев.

 Формула:

=СРЗНАЧЕСЛИ(ВЫБЕРИТЕ ЯЧЕЙКУ, КРИТЕРИИ,[СРЕДНИЙ_ДИАПАЗОН])

примечание: [средний_диапазон] не является обязательным

7.

ВПР

ВПР UP — одна из самых полезных и узнаваемых функций анализа данных. Как пользователь Excel, вам, вероятно, в какой-то момент понадобится «объединить» данные. Например, отдел дебиторской задолженности может знать, сколько стоит каждый продукт, но отдел доставки может предоставить только отгруженные единицы. Это идеальный вариант использования ВПР.

 На изображении ниже мы используем справочные данные (A2) в сочетании с таблицей цен, чтобы Excel искал критерии соответствия в первом столбце и возвращал соседнее значение.

Формула:

=VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COL_INDEX_NUM, [RANGE_LOOKUP])

8. НАЙТИ/ПОИСК

=ПОИСК/=ПОИСК — это мощные функции для выделения определенного текста в наборе данных. Оба перечислены здесь, потому что =НАЙТИ вернет совпадение с учетом регистра, т. е. если вы используете НАЙТИ для запроса «Большой», вы вернете только результаты «Большой=истина». Но =ПОИСК для «Большой» будет соответствовать Большому или большому, делая запрос немного шире. Это особенно полезно для поиска аномалий или уникальных идентификаторов.

 Формула:

=НАЙТИ(ТЕКСТ,WITHIN_TEXT,[START_NUMBER]) ИЛИ =ПОИСК(ТЕКСТ,WITHIN_TEXT,[START_NUMBER])

примечание: [start_number] не является обязательным и используется для указания начальной ячейки в тексте для поиска 9006 4

9. ЕСЛИОШИБКА

 =ЕСЛИОШИБКА — это то, чем должен воспользоваться любой аналитик, активно представляющий данные. Используя предыдущий пример, поиск определенного текста/значений в наборе данных не даст совпадения. Это вызывает ошибку #ЗНАЧ, и, хотя это безвредно, это отвлекает внимание и мозолит глаза.

 Используйте =ЕСЛИОШИБКА, чтобы заменить ошибки #ЗНАЧ любым текстом/значением. В приведенном выше примере ячейка пуста, поэтому потребители данных могут легко выбрать, какие строки вернули совпадающее значение.

Формула:

=ЕСЛИОШИБКА(НАЙТИ»ЗНАЧЕНИЕ»,ВЫБРАТЬ ЯЧЕЙКУ,ЗНАЧЕНИЕ_ЕСЛИ_ОШИБКА)

10. СЧЁТЕСЧЕСЛИМН

=СЧЁТЕСЛИМН — самый простой способ подсчитать количество экземпляров набора данных, удовлетворяющих набору критериев. В приведенном выше примере название продукта используется для определения того, какой продукт был самым продаваемым. СЧЁТЕСЛИМН мощна из-за безграничных критериев, которые вы можете ввести.

Формула:

=СЧЁТЕСЛИМН(ДИАПАЗОН,КРИТЕРИИ)

11. ВЛЕВО/ВПРАВО

=ЛЕВО, =ПРАВО — это эффективные и простые методы извлечения статических данных из ячеек. =LEFT вернет «x» символов от начала ячейки, а =right вернет «x» символов от конца ячейки. В приведенном ниже примере =LEFT используется для извлечения кода города потребителя из его номера телефона, а =RIGHT используется для извлечения последних четырех цифр.

 Формула:

=ЛЕВО(ВЫБЕРИТЕ ЯЧЕЙКУ,ЧИСЛО)

ИЛИ

=ПРАВО(ВЫБЕРИТЕ ЯЧЕЙКУ,ЧИСЛО)

12. РАНГ

=РАНГ – древняя функция Excel, но она не преуменьшает его эффективность для анализа данных. =RANK позволяет быстро определить ранжирование значений в наборе данных в порядке возрастания или убывания. В примере RANK используется для определения того, какие клиенты заказывают больше всего товаров.

 Формула:

=РАНГ(ВЫБРАТЬ ЯЧЕЙКУ,ДИАПАЗОН_ДО_РАНГ_ПРОТИВ,[ПОРЯДОК])

примечание: [порядок] не является обязательным.

13. MINIFS

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

 Формула:

=MINIFS(RANGE1,CRITERIA1,RANGE2)

14. MAXIFS

 =MAXIFS, как и его аналог minifs, позволяет сопоставлять критерии, но на этот раз он ищет максимальное число.

Формула:

=МАКСЕСЛИ(ДИАПАЗОН1, КРИТЕРИИ1, ДИАПАЗОН2)

15. СУММПРОИЗВ

 =СУММПРОИЗВ – отличная функция для расчета средней доходности, ценовых пунктов и маржи. СУММПРОИЗВ умножает один диапазон значений на соответствующие ему аналоги строки. Это золото анализа данных. В приведенном ниже примере мы рассчитываем среднюю цену продажи всех наших продуктов, используя сумму продукта, умноженную на цену на количество, а затем делим на общий проданный объем.

 Формула:

=СУММПРОИЗВ(ДИАПАЗОН1,ДИАПАЗОН2)/ВЫБЕРИТЕ ЯЧЕЙКУ

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

 Если вы хотите узнать больше об Excel, свяжитесь с нами по телефону

, мобильному телефону : +2348062468296

Электронная почта: : [email protected] | [email protected]

Веб-сайт : www.leadleapconsult.com

Десять основных функций Excel для аналитиков данных

Опубликовано в

·

7 мин чтения

·

9017 2 23 февраля 2021 г.

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

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

Необработанные данные могут быть беспорядочными, а иногда могут содержать пропущенные значения, особенно когда эти данные собираются в реальном мире. В Python мы можем использовать такой метод, как isna(), для обнаружения пропущенных значений. Однако это также возможно в Excel с помощью функции СЧИТАТЬПУСТОТЫ.

СЧИТАТЬПУСТОТЫ будет подсчитывать количество пустых ячеек в заданном диапазоне.

Использование функции СЧИТАТЬПУСТОТЫ для проверки пропущенных значений. Изображение автора.

Формула для этой функции Excel:

=СЧИТАТЬПУСТОТЫ(диапазон)

Одной из самых известных и основных функций Excel является функция СУММ. Иногда мы можем захотеть использовать SUM, но также исключить ячейки из нашего суммирования, используя критерий. Здесь на помощь приходит СУММЕСЛИМН.

СУММЕСЛИМН позволяет нам суммировать значения в заданном диапазоне, но суммирует только те значения, которые соответствуют заданным критериям. Этой функции можно задать сколько угодно критериев.

Использование функции СУММЕСЛИМН для подсчета сотрудников в разных местах. Изображение автора.

Формула для этой функции Excel:

=СУММЕСЛИМН(диапазон_суммы, диапазон_критериев1, критерий1, …)

Функцию РАНГ можно использовать для возврата ранга числового значения по сравнению со списком других числовых значений. Ранжирование может быть очень важным в анализе данных, чтобы знать, где конкретное значение окажется в упорядоченном массиве. Также существует аналогичная функция, известная как PERCENTRANK, которая возвращает рейтинг в процентах от диапазона набора данных.

Важно отметить, что если значение не может быть найдено в заданном списке или массиве, Excel вернет ошибку #Н/Д. Это неверно для PERCENTRANK.

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

Формула для этой функции Excel:

=РАНГ(число, список)

ВПР — одна из самых важных функций, которые должен знать любой аналитик данных. Его можно использовать для извлечения или поиска данных в таблице, организованной по вертикали. Это невероятно полезно, так как его можно использовать для автоматического поиска данных в другой электронной таблице, если каждая строка имеет идентификатор.

Столбец ID для таблицы должен быть первым столбцом. Затем третий параметр функции ВПР можно использовать для ссылки на столбец, содержащий данные, которые вы хотите получить. Важно отметить, что этот параметр в Excel отсчитывается от 1. Это означает, что значение 2 получит второй столбец, значение 3 — третий столбец и так далее. Это отличается от индексации списка в Python, которая основана на 0.

Два других параметра функции ВПР относятся к искомому значению (или идентификатору строки, которую вы извлекаете) и диапазону самой таблицы.

Использование функции ВПР для поиска данных в разных местах. Изображение автора.

Формула для этой функции Excel:

=ВПР(искомое_значение, таблица_массив, столбец_номер_индекса)

Если вы представляете свои электронные таблицы Excel другим людям в вашем бизнесе, может быть полезно иметь значения по умолчанию в ячейках, когда в ваших функциях возникают ошибки. Это может быть особенно полезно, когда другие пользователи не понимают смысла ошибок, или значение ячейки было бы известно, если бы произошла ошибка в вычислениях (например, значение по умолчанию равно 0).

Функция ЕСЛИОШИБКА может сделать именно это. Эта функция просто принимает два параметра, первый — это функция, а второй — значение по умолчанию, если функция выдает ошибку. Если функция не выдает ошибку, то значение будет выведено как обычно.

Использование функции ЕСЛИОШИБКА для обработки ошибок Excel. Изображение автора.

Формула для этой функции Excel:

=ЕСЛИОШИБКА(значение, значение_если_ошибка)

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

Функция ДНЕЙ принимает в качестве параметров две даты и возвращает количество дней между ними в виде целого числа.

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

Использование функции ДНЕЙ для расчета дней доставки продукта. Изображение автора.

Формула для этой функции Excel:

=ДНЕЙ(дата_конца, дата_начала)

Поиск максимального значения, которое может принимать переменная, чрезвычайно важен в бизнесе. От знания самого загруженного дня в году до максимальной суммы прибыли, полученной бизнесом за день — есть много причин для расчета максимума.

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

В приведенном ниже примере мы используем MAXIFS, чтобы найти максимальное количество дней для доставки каждого продукта. Ограничение накладывается на продукт в каждой строке нашего набора данных.

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

Формула для этой функции Excel:

=МАКСЕСЛИМН(максимальный_диапазон, критерий_диапазон_1, критерий_1, …)

Ранее мы исследовали использование СУММЕСЛИМН для получения отфильтрованных сумм наших данных. Мы также рассмотрели MAXIFS, чтобы сделать то же самое, но с максимальными значениями. К настоящему времени вы, возможно, поняли, что существует множество различных функций ЕСЛИ, которые можно использовать в Excel.

Некоторые другие полезные функции ЕСЛИ включают СЧЁТЕСЛИМН, МИНИМСЛИМН и старую простую функцию ЕСЛИ. Одна из часто встречающихся функций — это функция СРЗНАЧЕСЛИМН, которая используется для вычисления средних значений.

В приведенном ниже примере мы используем функцию СРЗНАЧЕСЛИМН для расчета среднего времени доставки для каждого продукта.

Использование функции СРЗНАЧЕСЛИМН для получения среднего времени доставки для каждого продукта. Изображение автора.

Формула для этой функции Excel:

=СРЗНАЧСЛИМН(средний_диапазон, критерий_диапазон_1, критерий_1, …)

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

Функция ПОИСКПОЗ используется для определения позиции значения в заданном массиве. Важно помнить, что этот результат будет относиться к началу массива, поэтому результат 4 не обязательно означает, что искомое значение находится в 4-м столбце или строке электронной таблицы; это будет на 4-й позиции данного диапазона.

Использование функции ПОИСКПОЗ для получения определенной строки в наборе данных. Изображение автора.

Формула для этой функции Excel:

=ПОИСКПОЗ(значение, массив)

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

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

В приведенном ниже примере мы видим, что функция ПОИСКПОЗ используется для получения строки, в которой продукт будет доставлен через 11 дней, а затем функция ИНДЕКС используется для получения описания продукта в этой строке.

Объединение функций ПОИСКПОЗ и ИНДЕКС для поиска.