Таблицы excel формулы: Функции Excel (по категориям)

Содержание

Полезные формулы Excel и «Google Таблиц» для маркетолога

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

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

Формулы Excel и Google Таблиц для работы с рекламой

Заменить значение в большом количестве объявлений

Представьте, вы настроили рекламу на тысячу ключей, например, для санатория, расписали в ней преимущества продукта и успешно запустили. Но через пару недель руководство говорит, что теперь программ в санатории не 24, а 30 — добавили шесть новых.

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

Для Excel и Google Таблиц: =ПОДСТАВИТЬ(text_to_search;search_for;replace_with)
text_to_search — строка или адрес ячейки, где нужно заменить одно слово на другое;
search_for — фрагмент или слово, которые нужно найти и заменить;
replace_with — слово или фрагмент, на которые меняем search_for.

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

Убрать лишние пробелы в объявлениях и минус-словах

Лишние пробелы — зло в любом виде текста, будь то статья, пост в соцсетях или текст на баннере. Но, когда вы создаете объявления, лишние пробелы мешают дважды: во-первых, они съедают драгоценное место, а во-вторых, «Директ Коммандер» ругается, если загрузить минус-слова с лишними пробелами.

Чтобы быстро удалить пробелы, можно использовать функцию «Сжать пробелы» — =СЖПРОБЕЛЫ(). В скобках указывается номер ячейки, из которой нужно удалить лишние пробелы.

Для Excel и Google Таблиц =СЖПРОБЕЛЫ(диапазон)

Показать клиенту сразу все варианты объявлений для РСЯ и КМС

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

=IMAGE(ссылка; [режим]; [высота]; [ширина])
Функция IMAGE есть только в Google Таблицах и только на английском языке.

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

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

Этот способ можно использовать не только в объявлениях. Так удобно согласовывать изображения для каталога товаров или для постов в блоге.

Формулы Excel и Google Таблиц для работы с семантикой

Убрать мусорные запросы из семантики

Собрать семантическое ядро для обладателя Macbook целая проблема. Есть ощущение, что SEOшники не любят яблочников, потому что ни SlovoEb, ни KeyCollector под MAC не адаптированы.

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

В статье про мегаэффективное семантическое ядро моя коллега Екатерина Чикулаева рассказывала, что при сегментировании семантики можно воспользоваться фильтром. Так же можно удалить и мусорные запросы, но это занимает очень-ооочень-оооочень много времени.

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

Для Excel и Google Таблиц:
=НАЙТИ(искать; текст; [начиная с])
=ПОИСК(искать; текст; [начиная с])
Искать — текст, который необходимо найти; Текст — где искать; Начиная с — необязательный атрибут, который нужен, если вы хотите пропустить часть символов в строке.

Функции отличаются тем, что «Найти» учитывает регистр, а «Поиск» — нет.

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

После того как вы добавите формулу во все ячейки, нажмите «Сортировать лист А → Я», и все строки, содержащие это слово, окажутся сверху. Теперь их нужно просто удалить. Очередность ключей при этом не нарушится, но если вдруг — то просто отсортируйте этот столбец от Я до А, то есть от большего к меньшему.

Убрать дубли ключей

Собирать ключи можно с помощью разных сервисов: SlovoEb, KeyCollector, а еще можно посмотреть семантику конкурентов. Их данные могут совпадать, а могут отличаться. Чтобы привести все к единому виду без долгого и нудного ручного труда, можно использовать функцию UNIQUE.

Для Excel: Вкладка «Данные» > Удалить Дубликаты
Для Google Таблиц: =UNIQUE(диапазон)

Проверить длину мета-описания

Рекомендуемая длина Title — 70-80 символов, а Description — 160-180. Если вы работаете над мета-тегами для большого количества страниц, то уследить за этими параметрами вручную можно, но сложно. Гораздо проще автоматизировать процесс с помощью формулы «Длина строки».

Для Excel и Google Таблиц: =ДЛСТР(диапазон)

Не буду подробно описывать, как она работает: это видно на примере ниже. То же самое можно проделать и с Description.

Какие мета-теги подходят, а какие требуют доработки, можно также быстро определить с помощью функции «Условное форматирование». Чтобы ее найти, нажмите в верхнем меню кнопку «Формат» и в открывшемся меню — «Условное форматирование».

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

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

Формулы Excel и «Google Таблиц» для работы с рассылками

Добавить данные о подписчиках

Представим, что у вас есть база клиентов, которую вы хотите добавить в сервис почтовых рассылок, например, MailChimp. Вы знаете не только e-mail клиента, но и его ФИО. Чтобы сервис точно знал, что из введенных данных имя, что фамилия, а что отчество, их нужно разделить на три столбца и при выгрузке задать значение для каждой группы.

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

Для Google Таблиц: =SPLIT(диапазон; «разделитель»)
Для Excel: Данные — Текст по столбцам — С разделителями

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

Какие полезные формулы знаете вы? Расскажите в комментариях. Давайте поможем друг другу оптимизировать работу.

Формулы сбиваются при использовании файла Excel на разных компьютерах

При использовании встроенных в надстройку FillDocuments формул для склонения, фамилий и текста прописью, в таблицу Excel вставляются формулы вида =Пропись_Число(D12)

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

=’D:\ПРОЕКТЫ\Папка\заполнение документов\FillDocuments.xla’!Пропись_Число(D12)

Кроме того, Excel может отобразить предупреждение о том, что автоматическое обновление ссылок отключено:

Разумеется, формулы после этого не работают, так как ссылаются на отсутствующий по данному пути файл.

Есть 4 варианта решения проблемы, выберите любой из них.
При постоянном использовании файла с разных компьютеров, рекомендую способ №1.
Если нужно исправить проблему разово (на прежнем компе файл больше не будет использоваться, или надстройка перемещена в другую папку), то выберите вариант 2, 3 или 4.

Способ 1 применяется при использовании файла с формулами с разных компов.

Расположите файл надстройки FillDocuments.xla на всех компьютерах по одному пути (и убедитесь, что имя файла надстройки везде одинаково)
Например, можно расположить файл в корне диска С, чтобы путь к файлу был C:\FillDocuments.xla
Или можно разместить в общей сетевой папке, по пути типа \\server\надстройки\FillDocuments.xla

ВАЖНО: для перемещения файла надстройки, нужно выполнить следующее:

отключить автозапуск программы в О ПРОГРАММЕ — ОПЦИИ
закрыть Excel (или закрыть надстройку FillDocuments)
переместить файл в новое расположение
запустить надстройку из нового места
включить автозапуск программы в О ПРОГРАММЕ — ОПЦИИ (если это нужно)

После этого, выполните описанное в одном из других способов, для восстановления формул.

Способ 2: вручную исправляем ссылки, через поиск и замену

Копируем из ячейки «лишний» путь к файлу, начиная с кавычки, и заканчивая восклицательным знаком:
‘D:\ПРОЕКТЫ\Папка\заполнение документов\FillDocuments.xla’!

Нажимаем Ctrl + H для вывода диалогового окна «Найти и заменить», в поле «Найти» вставляем скопированный путь,
а поле «Заменить на» оставляем пустым.
Осталось нажать кнопку , и формулы приведутся к исходному варианту написания: =Пропись_Число(D12)

Способ 3: исправляем ссылки через меню надстройки FillDocuments

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

Нажимаем кнопку , идём на вкладку «Дополнительно», и включаем там опцию «Отображать подменю ДОПОЛНИТЕЛЬНО на панели инструментов программы»:

После этого, на панели инструментов появится новое подменю , в котором нам нужно выбрать пункт :

Способ 4: исправляем ссылки на файл штатными средствами Excel

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

Нажимаем кнопку Изменить, и в диалоговом окне указываем, где теперь расположен файл надстройки FillDocuments.xla

После того, как файл выбран, в графе Состояние будет написано ОК:

Теперь отстаётся запустить надстройку FillDocuments (если она до этого не была запущена), и Excel автоматически внесёт изменения в формулы во всём файле.

Пошаговое руководство, обзор и примеры

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

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

Давайте продолжим и разберемся, что такое ВПР в Excel.

Что такое ВПР в Excel?

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

Приведенная ниже формула ВПР ищет название компании с идентификатором компании 3.

В следующем разделе вы поймете, как использовать функцию ВПР.

ВПР Формула

Мы можем использовать функцию ВПР с помощью простого синтаксиса. Синтаксис для ВПР:

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

Где,

lookup_value: указывает значение, которое вы хотите найти в наших данных.
table_array: это место, где представлены значения в Excel.
col_index_number: указывает номер столбца, из которого нам нужно вернуть значение.
range_lookup: два варианта; если установлено значение FALSE, это означает, что мы ищем точное совпадение. Если значение TRUE, то ищем приблизительное совпадение.

Как найти точное совпадение с помощью функции ВПР?

VLOOKUP упрощает поиск точного совпадения в таблице. Давайте посмотрим, как это сделать на примере:

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

Во втором аргументе указываем расположение таблицы. Как видите, расположение таблицы — A2:F11.
Третий аргумент указывает номер индекса столбца. Это говорит нам, какое значение должно быть возвращено из строки, которую мы ищем. В примере столбец продукта равен 3,9.0036
Последний аргумент — логическое выражение. Здесь для значения установлено значение FALSE, чтобы функция ВПР возвращала точное совпадение со значением. Если точное значение не найдено, отображается ошибка N/A.

С помощью ВПР в Excel мы можем искать и приблизительное совпадение. Вы узнаете об этом в следующем разделе.

Как найти приблизительное совпадение с помощью функции ВПР?

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

В приведенном ниже примере мы используем функцию ВПР, чтобы узнать, какой объем оперативной памяти имеет спецификация ноутбука стоимостью 1300 евро. Кроме того, мы знаем, что этого значения нет в таблице. Итак, давайте воспользуемся приблизительным совпадением, чтобы найти решение. Нам нужно отсортировать первый столбец в порядке возрастания. В противном случае функция ВПР вернет неверные значения.

Сначала скопируйте информацию о цене и оперативной памяти в новое место и укажите значение для поиска. Здесь искомое значение составляет 1300 долларов.

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

Отфильтровать сведения о цене в порядке возрастания. Нажмите «ОК».

Теперь введите формулу ВПР. Первым аргументом будет значение ВПР. Второй аргумент указывает диапазон таблицы. В третьем аргументе мы указываем номер столбца, чтобы возвращались значения для этого столбца. Наконец, последний аргумент устанавливается в значение TRUE. Это позволит функции ВПР найти приблизительное совпадение значения.

После ввода формулы нажмите Enter. Возвращаемое значение в этом случае составляет 8 ГБ по цене 1300 долларов.

Давайте перейдем к следующему разделу и поймем, как использовать функцию ВПР для нескольких критериев.

Как использовать функцию ВПР для нескольких критериев?

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

Например, если нам нужно найти цену Apple MacBook Pro, где название компании и название продукта находятся в двух разных столбцах, мы используем вспомогательный столбец. В этом столбце будут храниться конкатенированные значения обоих столбцов.

Выполните следующие действия, чтобы выполнить ВПР с несколькими критериями.

Сначала щелкните правой кнопкой мыши заголовок столбца и выберите «Вставить». Это поможет вам вставить столбец слева от столбца «Компания». Назовите его «Компания и продукт».

При создании вспомогательного столбца введите формулу =C2&”-”&D2. Затем перетащите формулу в остальные ячейки столбца.

После успешного создания объединенного столбца теперь можно искать значение. Здесь мы ищем цену Apple MacBook Pro.
Итак, вводим формулу =ВПР(B15&”-”&C15,B1:G11,5,ЛОЖЬ)

Первый параметр помогает объединить искомое значение, т. е. B15&»-» &C15. Мы также можем использовать формулу конкатенации, т. е. СЦЕПИТЬ (B15, «-«, C15). Второй параметр — это диапазон таблицы. Третий параметр указывает индекс столбца, который возвращает значение из этого столбца. Последний параметр равен FALSE, так как мы ищем точное совпадение. При нажатии на ввод цена будет возвращена следующим образом.

Советы по эффективному использованию функции ВПР

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

Если вы не укажете range_lookup в функции ВПР, она также будет оценивать неточные совпадения. Однако он будет использовать точное совпадение, если оно существует в ваших данных.
Если в столбце подстановки есть повторяющиеся значения, будет выбрано только первое значение.
Функция ВПР не чувствительна к регистру.
Добавление столбца после применения формулы ВПР покажет ошибку, поскольку жестко запрограммированные значения индекса не изменяются автоматически при добавлении или удалении столбцов.

Распространенные ошибки в функции ВПР

#Н/Д! ошибка — возникает, если функция ВПР не может найти совпадение с предоставленным искомым_значением.
#ССЫЛКА! ошибка — возникает, если аргумент col_index_num > количество столбцов в предоставленном table_array; или формула пытается ссылаться на несуществующие ячейки.
#ЗНАЧ! ошибка – возникает, если: аргумент col_index_num меньше 1 или не распознан как числовое значение; или Аргумент range_lookup не распознан как одно из логических значений TRUE или FALSE.

Это все, что вам нужно знать о функции ВПР в Excel.

Получите опыт работы с новейшими инструментами и методами бизнес-аналитики с помощью магистерской программы для бизнес-аналитиков. Зарегистрируйтесь сейчас!

Заключение

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

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

Вы также можете пройти лишнюю милю и улучшить свои навыки работы с Excel, записавшись на бесплатный курс Business Analytics with Excel, предлагаемый Simplilearn.

Памятка по формулам Excel (примеры)

Памятка по формулам Excel (оглавление)

Шпаргалка по формулам Excel
Текстовые функции в Excel
СТАТИСТИЧЕСКИЕ Функции в Excel
Функции ДАТЫ И ВРЕМЕНИ в Excel
МАТЕМАТИЧЕСКИЕ Функции в Excel

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

Расчеты, используемые в шпаргалке по формулам Excel

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

Функции ТЕКСТА в Excel

Перейдите на вкладку ФОРМУЛЫ.
Нажмите на функции ТЕКСТ. Откроется раскрывающийся список. Обратитесь к скриншоту ниже.

Как мы видим здесь, перечислены несколько строковых функций. Наиболее часто используемые текстовые функции: ВЛЕВО, ВПРАВО, СРЕДНЯЯ, СЦЕПИТЬ, НИЖЕ, ДЛСТР и т. д.

Как работает функция ТЕКСТ?

Давайте рассмотрим пример, чтобы понять работу текстовых функций.

Вы можете скачать этот шаблон формул для шпаргалок Excel здесь – Шаблон формул для шпаргалок Excel

Пример №1

Мы берем несколько имен и применим к ним текстовую функцию.

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

СТАТИСТИЧЕСКИЕ Функции в Excel

Перейдите на вкладку ФОРМУЛА.
Нажмите на опцию «Дополнительные функции». Выберите категорию Статистические функции.

Откроется раскрывающийся список функций.

Наиболее часто используемыми статистическими функциями являются MIN, MAX, COUNT, AVERAGE, MEDIAN и т. д.

Пример #2

Ниже приведены некоторые числовые значения.

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

ДАТА И ВРЕМЯ Функции в Excel

Перейдите на вкладку ФОРМУЛЫ.
Щелкните категорию функций даты и времени. Откроется раскрывающийся список функций, как показано на скриншоте ниже.

Наиболее часто используемые функции ДАТА И ВРЕМЯ: СЕЙЧАС, ДАТА, ДЕНЬ НЕД, СЕГОДНЯ, РАБДЕНЬ и т. д.

Давайте разберемся с этим на некоторых примерах на снимке экрана ниже.

Пример #3

Ниже приведены некоторые данные.

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

МАТЕМАТИЧЕСКИЕ Функции в Excel

Перейдите на вкладку ФОРМУЛЫ.
Щелкните категорию функций Math & Trig. Откроется раскрывающийся список функций, как показано на скриншоте ниже.

Наиболее часто используемыми математическими функциями являются СУММА, ПРОМЕЖУТОЧНЫЙ ИТОГ, СЛУЧМЕЖДУ, ПРОИЗВЕДЕНИЕ, КОРЕНЬ-КВОРОТ и т. д.

Рассмотрим работу этой функции на примере.

Пример #4

Возьмем ниже числовую серию.