какую функцию выбрать в excel
Какую функцию выбрать в excel
Таблицы Excel — очень мощный инструмент. В них больше 470 скрытых функций. Поначалу это пугает: кажется, на то, чтобы разобраться со всем, уйдут годы. На самом деле это не так. Всего десятка функций и горячих клавиш уже хватит для того, чтобы сильно упростить себе жизнь. Расскажем о некоторых из них (скоро стартует второй поток курса «Магия Excel»).
Интерфейс
Настраиваем панель быстрого доступа
Начнем с самого простого — добавления самых часто используемых опций на панель быстрого доступа. Чтобы сделать это, заходите в параметры Excel — «Настроить ленту» — и ищите в параметрах «Панель быстрого доступа».
Опции, перенесенные на панель быстрого доступа, будут доступны при работе со всеми вашими книгами Excel (хотя можно ее настроить и отдельно для любой книги). Так что если пользуетесь какими-то командами и инструментами постоянно — добавляйте их туда.
Другой вариант — просто щелкнуть по инструменту на ленте правой кнопкой мыши и нажать «Добавить…»:
Перемещаемся по ленте без мышки
Нажмите на Alt. На ленте инструментов появились цифры и буквы — у каждого инструмента на панели быстрого доступа и у каждой вкладки на ленте соответственно:
Нажмите на клавиатуре любую из букв — попадете на соответствующую вкладку на ленте, а там каждый инструмент в свою очередь тоже будет подписан. Так можно быстро вызвать нужные опции, не трогая мышку.
Ввод данных
Теперь давайте рассмотрим несколько инструментов для быстрого ввода данных.
Автозамена
Если вам часто нужно вводить какое-то словосочетание, адрес, емейл и так далее — придумайте для него короткое обозначение и добавьте в список автозамены в Параметрах:
Прогрессия
Если нужно заполнить столбец или строку последовательностью чисел или дат, введите в ячейку первое значение и затем воспользуйтесь этим инструментом:
Протягивание
Представьте, что вам нужно извлечь какие-то данные из целого столбца или переписать их в другом виде (например, фамилию с инициалами вместо полных ФИО). Задайте Excel одну ячейку с образцом — что хотите получить:
Выделите все ячейки, которые хотите заполнить по образцу, — и нажмите Ctrl+E. И магия случится (ну, в большинстве случаев).
Проверка ошибок
Проверка данных позволяет избежать ошибок при вводе информации в ячейки.
Какие бывают типовые ошибки в Excel?
Инструмент проверки данных
Чтобы использовать инструмент проверки данных, нужно выделить ячейки, к которым хотите его применить, выбрать на ленте «Данные» → «Проверка данных» и настроить параметры проверки в диалоговом окне:
Если в графе «Сообщение об ошибке» вы выбрали вариант «Остановка», то после проверки в ячейки нельзя будет ввести значения, не соответствующие заданному правилу.
Если же вы выбрали «Предупреждение» или «Сообщение», то при попытке ввести неверные данные будет появляться предупреждение, но его можно будет проигнорировать и все равно ввести что угодно.
Еще неверные данные можно обвести, чтобы точно увидеть, где есть ошибки:
Удаление пробелов
Для удаления лишних пробелов (в начале, в конце и всех кроме одного между слов) используйте функцию СЖПРОБЕЛЫ / TRIM. Ее единственный аргумент — текст (ссылка на ячейку с текстом, как правило).
Если после очистки данных функцией СЖПРОБЕЛЫ или другой обработки вам не нужен исходный столбец, вставьте данные, полученные в отдельном столбце с помощью функций, как значения на место исходных данных, а столбец с формулой удалите:
Дата и время
За любой датой в Excel скрывается целое число. Датой его делает формат.
Аналогично со временем: одна единица — это день, а часть единицы (число от 0 до 1) — время, то есть часть дня.
Это не значит, что так имеет смысл вводить даты и время в ячейки, вводите их в любом из стандартных форматов — Excel сразу отформатирует их как даты:
Вычесть из одной даты другую, чтобы получить разницу в днях (результатом вычитания будет число — количество дней.
Прибавить к дате число — и получить дату, которая наступит через соответствующее количество дней.
Поиск и подстановка значений
Функция ВПР / VLOOKUP
Функция ВПР / VLOOKUP (вертикальный просмотр) нужна, чтобы связать несколько таблиц — «подтянуть» данные из одной в другую по какому-то ключу (например, названию товара или бренда, фамилии сотрудника или клиента, номеру транзакции).
=ВПР (что ищем; таблица с данными, где «что ищем» должно быть в первом столбце; номер столбца таблицы, из которого нужны данные; [интервальный просмотр])
У нее есть два режима работы: интервальный просмотр и точный поиск.
Интервальный просмотр — это поиск интервала, в который попадает число. Если у вас прогрессивная шкала налога или скидок, нужно конвертировать оценку из одной системы в другую и так далее — используется именно этот режим. Для интервального просмотра нужно пропустить последний аргумент ВПР или задать его равным единице (или ИСТИНА).
В большинстве случаев мы связываем таблицы по текстовым ключам — в таком случае нужно обязательно явным образом указывать последний аргумент «интервальный_просмотр» равным нулю (или ЛОЖЬ). Только тогда функция будет корректно работать с текстовыми значениями.
Функции ПОИСКПОЗ / MATCH и ИНДЕКС / INDEX
У ВПР есть существенный недостаток: ключ (искомое значение) обязан быть в первом столбце таблицы с данными. Все, что левее этого столбца, через ВПР «подтянуть» невозможно.
Функция ПОИСКПОЗ / MATCH определяет порядковый номер значения в диапазоне. Ее синтаксис:
=ПОИСКПОЗ (что ищем; где ищем ; 0)
На выходе — число (номер строки или столбца в рамках диапазона, в котором находится искомое значение).
ИНДЕКС / INDEX выполняет другую задачу — возвращает элемент по его номеру.
=ИНДЕКС(диапазон, из которого нужны данные; порядковый номер элемента)
Соответственно, мы можем определить номер строки, в котором находится искомое значение, с помощью ПОИСКПОЗ. А затем подставить этот номер в ИНДЕКС на место второго аргумента, чтобы получить данные из любого нужного нам столбца.
Получается следующая конструкция:
=ИНДЕКС(диапазон, из которого нужны данные; ПОИСКПОЗ (что ищем; где ищем ; 0))
Оформление
Нужно оформить ячейки в книге Excel в едином стиле? Для этого есть одноименный инструмент — «Стили».
На ленте инструментов нажмите на «Стили ячеек» и выберите подходящий. Он будет применен к выделенным ячейкам:
А самое главное — если вы применили стиль ко многим ячейкам (например, ко всем заголовкам на 20 листах книги Excel) и захотели что-то переделать, щелкните правой кнопкой мыши и нажмите «Изменить». Изменения будут применены ко всем нужным ячейкам в документе.
На курсе «Магия Excel» будет два модуля — для новичков и продвинутых. Записывайтесь →
21 полезная функция Excel для интернет-маркетологов
Excel – программа, которой мы пользуемся практически каждый день, и о том, как она облегчает жизнь большинству пользователей, можно даже не говорить. Но чем же она полезна для интернет-маркетологов? Мы рассмотрим 21 функцию Excel и попробуем ответить на этот вопрос.
Прежде чем приступить к обзору, рассмотрим значения определений, которые встретятся вам в этой статье.
Синтаксис – это формула функции, которая начинается со знака равенства и состоит из 2 частей: названия функции и аргументов, имеющих определенную последовательность и заключенных в круглые скобки.
Аргументы функции могут быть представлены как текстовыми, числовыми или логическими значениями, так и ссылками на ячейки или диапазон ячеек. Между собой аргументы разделяются точкой с запятой.
1) ВПР
Функция ВПР позволяет найти данные в текстовой строке таблицы или диапазоне ячеек и добавить их в другую таблицу. Аббревиатура ВПР расшифровывается как «вертикальный просмотр».
Синтаксис
Данная функция состоит из 4 аргументов и представлена следующей формулой:
Рассмотрим каждый из аргументов:
Примеры
Рассмотрим несколько примеров использования функции ВПР. Ниже приведен пример того, как можно использовать функцию для анализа данных о статистике по запросам. Предположим, что нам нужно найти в данной таблице количество просмотров по запросу «купить планшет».
Функции нужно найти данные, соответствующие значению «планшет», которое указано в отдельной ячейке (С3) и выступает в роли искомого значения. Аргумент «таблица» здесь – диапазон поиска от A1:B6; номер столбца, содержащий возвращаемое значение – «2». В итоге получаем следующую формулу: =ВПР(С3;А1:B6;2). Результат – 31325 просмотров в месяц.
В следующих двух примерах применен интервальный просмотр с двумя вариантами функций: ИСТИНА и ЛОЖЬ.
Функция ВПР является одной из самых популярных функций Excel, достаточно сложной для понимания, но чрезвычайно полезной.
2) ЕСЛИ
Функция ЕСЛИ выполняет проверку заданных условий, выбирая один из двух возможных результатов: 1) Если сравнение истинно; 2) Если сравнение ложно.
Синтаксис
Формула функции состоит из трех аргументов и выглядит следующим образом:
Примеры
Рассмотрим пример использования обычной функции ЕСЛИ.
Для того чтобы узнать, кто из продавцов выполнил план, а кто нет, нужно ввести следующую формулу:
=ЕСЛИ(B2>30000;«План выполнен»;«План не выполнен»)
Логическое выражение здесь – формула «B2>30000».
«Значение если истина» – «План выполнен».
«Значение если ложь» – «План не выполнен».
Вложенные функции ЕСЛИ
Помимо обычной функции ЕСЛИ, которая выдает всего 2 результата – «истина» и «ложь», существуют вложенные функции ЕСЛИ, выдающие от 3 до 64 результатов. В данном случае формула может вмещать в себя несколько функций.
Вложенные функции довольно сложны в использовании и часто выдают всевозможные ошибки в формуле, поэтому рекомендую пользоваться ими в самых исключительных случаях.
Существует еще один способ использования функции ЕСЛИ – для проверки, пуста ячейка или нет. Для этого ее можно использовать вместе с функцией ЕПУСТО.
В этом случае формула будет такой: =ЕСЛИ(ЕПУСТО(номер ячейки);«Пустая»;«Не пустая».
Вместо функции ЕПУСТО также можно использовать другую формулу: «номер ячейки=«» (ничего).
ЕСЛИ – одна из самых популярных функций в Excel, простая и удобная в использовании. Она помогает определить истинность тех или иных значений, получить результаты по разным данным и выявить пустые ячейки, к тому же ее можно использовать в сочетании с другими функциями.
Функция ЕСЛИ является основой других формул: СУММЕСЛИ, СЧЁТЕСЛИ, ЕСЛИОШИБКА, СРЕСЛИ. Мы рассмотрим три из них – СУММЕСЛИ, СЧЁТЕСЛИ и ЕСЛИОШИБКА.
3) СУММЕСЛИ и СУММЕСЛИМН
Функция СУММЕСЛИ позволяет суммировать данные, соответствующие определенному условию, находящиеся в указанном диапазоне.
Синтаксис
Функция состоит из 3 аргументов и имеет формулу:
«Условие» – аргумент, определяющий какие именно ячейки нужно суммировать. Это может быть текст, число, ссылка на ячейку или функция. Обратите внимание на то, что условия с текстом и математическими знаками необходимо заключать в кавычки.
«Диапазон суммирования» – необязательный аргумент, который позволяет указать на ячейки, данные которых нужно суммировать, если они отличаются от ячеек, входящих в диапазон.
Пример
В приведенном ниже примере функция суммировала данные запросов, количество переходов по которым больше 100000.
Если нужно суммировать ячейки в соответствии с несколькими условиями, можно воспользоваться функцией СУММЕСЛИМН.
Синтаксис
Формула данной функции имеет следующий вид:
=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)
«Диапазон условия 1» и «условие 1» – обязательные аргументы, остальные – необязательные.
4) СЧЁТЕСЛИ и СЧЁТЕСЛИМН
Функция СЧЁТЕСЛИ считает количество непустых ячеек, соответствующих заданному условию внутри указанного диапазона.
Синтаксис
«Диапазон» – группа ячеек, которые нужно подсчитать.
«Критерий» – условие, согласно которому выбираются ячейки для подсчета.
Пример
В приведенном примере функция подсчитала количество ключей, число переходов по которым больше 100000, – в итоге получилось 3 ключа.
В функции СЧЁТЕСЛИ можно использовать только один критерий. Если же нужно сделать подсчет по нескольким условиям, можно применить функцию СЧЁТЕСЛИМН.
Синтаксис
Функция позволяет подсчитать количество ячеек, соответствующих нескольким заданным условиям. Каждому условию соответствует один вариант диапазона ячеек.
«Диапазон условия 1» и «условие 1» – обязательные аргументы, остальные же аргументы необязательны. Можно использовать до 127 пар диапазонов и условий.
5) ЕСЛИОШИБКА
Данная функция возвращает указанное значение, если вычисление по формуле дает ошибочный результат, правильный же результат формулы она оставляет.
Синтаксис
Функция имеет 2 аргумента и представлена формулой: =ЕСЛИОШИБКА(значение;значение_если_ошибка), где:
Примеры
Предположим, что у вас сломался счетчик аналитики, и в ячейке, в которой нужно указать число посетителей, стоит ноль, а число покупок – 32. Как такое может быть? Функция в данном случае указывает на ошибку и вводит значение, соответствующее ей – «перепроверить».
6) ЛЕВСИМВ
Функция ЛЕВСИМВ позволяет выделить необходимое количество знаков с левой стороны строки.
Синтаксис
Функция состоит из 2 аргументов и представлена формулой: =ЛЕВСИМВ(текст;[число_знаков]), где:
Пример
Использование данной функции позволяет посмотреть, как будут выглядеть тайтлы к страницам сайта или статьям.
К примеру, если вы хотите, чтобы тайтлы были максимально лаконичными и состояли из 60 знаков, функция отсчитает первые 60 символов и покажет, как будет выглядеть тот или иной тайтл. Для этого необходимо составить формулу: =ЛЕВСИМВ(А5;60), где А5 – адрес рассматриваемой ячейки, «60» – число извлекаемых символов.
7) ПСТР
Функция ПСТР позволяет извлечь необходимое количество символов внутри текста, начиная с указанной позиции.
Синтаксис
Формула функции состоит из 3 аргументов:
«Текст» – строка, содержащая символы, которые нужно извлечь.
«Начальная позиция» – позиция знака, с которого начинается извлекаемый текст.
«Число знаков» – количество извлекаемых символов.
Пример
Данную функцию можно применять для того, чтобы упростить названия тайтлов, убрав стоящие в их начале слова.
8) ПРОПИСН
Функция ПРОПИСН делает все буквы в тексте прописными.
Синтаксис
«Текст» здесь – текстовый элемент или ссылка на ячейку.
Пример
9) СТРОЧН
Функция СТРОЧН делает все буквы в тексте строчными.
Синтаксис
Аргумент «текст» – текстовый элемент или адрес ячейки.
Пример
10) ПОИСКПОЗ
Функция ПОИСКПОЗ помогает найти указанный элемент в массиве ячеек и определяет его положение.
Синтаксис
«Искомое значение» и «просматриваемый массив» – обязательные аргументы, «тип сопоставления» – необязательный.
Рассмотрим подробнее аргумент «тип сопоставления». Он указывает, каким образом сопоставляется найденное значение с искомым. Существует 3 типа сопоставления:
1 – значение меньше или равно искомому (при указании данного типа нужно учитывать, что просматриваемый массив должен быть упорядочен по возрастанию);
0 – точное совпадение;
-1 – наименьшее значение, которое больше или равно искомому.
Примеры
Рассмотрим следующий пример. Здесь я попыталась узнать, какой из запросов в приведенной таблице имеет количество переходов, которое равно или меньше 900.
Формула функции здесь: =ПОИСКПОЗ(900;B2:B6;1). 900 – искомое значение, B2:B6 – просматриваемый массив, 1 – тип сопоставления (меньше или равно искомому). Результат – «3», то есть третья позиция в указанном диапазоне.
11) ДЛСТР
Функция ДЛСТР позволяет определить длину текста, содержащегося в указанной ячейке.
Синтаксис
Формула функции имеет всего один аргумент – текст (номер ячейки):
Пример
Данную функцию можно использовать для проверки длины символов в description.
12) СЦЕПИТЬ
Функция СЦЕПИТЬ позволяет объединить несколько текстовых элементов в одну строку. В формуле для объединения элементов указываются как номера ячеек, содержащих текст, так и сам текст. Можно указать до 255 элементов и до 8192 символов.
Синтаксис
Для того чтобы объединить текстовые элементы без пробелов, используются следующие формулы:
Аргумент «текст» – текстовый элемент или ссылка на ячейку.
Примеры
В приведенном ниже примере введена следующая формула: =СЦЕПИТЬ(А2;B2;С2)
Для того же, чтобы слова в строке разделялись пробелами, в формулу необходимо вставить знаки пробелов в кавычках:
=СЦЕПИТЬ(текст1;« »;текст2;« »;текст3;« »)
В следующем примере функция представлена формулой: =СЦЕПИТЬ(A2;» «;B2;» «;C2)
Существует и другой вариант добавления пробелов в формулу функции – введение слов, заключенных в кавычки вместе с пробелами:
=СЦЕПИТЬ(«текст1 »;«текст2 »;«текст3 »)
13) ПРОПНАЧ
Функция ПРОПНАЧ преобразует заглавные буквы всех слов в тексте в прописные (верхний регистр), а все остальные буквы – в строчные (нижний регистр).
Синтаксис
Функция очень проста в использовании и представлена короткой формулой, имеющей всего один аргумент:
Пример
Рассмотрим пример, в котором представлены образцы с различными вариантами написания букв. Функция быстро привела их в читабельное состояние.
Эту функцию очень удобно использовать при составлении списков с именами собственными, для преобразования текстовых элементов, напечатанных строчными, прописными или различными по размеру буквами.
14) ПЕЧСИМВ
Функция ПЕЧСИМВ позволяет удалить все непечатаемые знаки из текста.
Синтаксис
Формула данной функции:
Пример
В приведенном примере текст в ячейке A1 содержит непечатаемые знаки конца абзаца.
Эту функцию нужно использовать в тех случаях, когда текст переносится в таблицу из других приложений, имеющих знаки, печать которых невозможна в Excel.
15) СЖПРОБЕЛЫ
Данная функция удаляет все лишние пробелы между словами.
Синтаксис
Формула функции проста: =СЖПРОБЕЛЫ(номер_ячейки)
Пример
Функция простая и полезная. Единственный минус состоит в том, что она не различает границ слов, и если внутри него стоят пробелы, функция этого не поймет и не удалит их.
16) НАЙТИ
Функция НАЙТИ позволяет обнаружить искомый текст внутри текстовой строки и указывает на начальную позицию этого текста относительно начала просматриваемой строки.
Синтаксис
Функция НАЙТИ состоит из 3 аргументов и представлена формулой:
«Начальная позиция» – необязательный аргумент, обозначающий символ, с которого нужно начать поиск.
В первом случае функция находит начальную позицию символа, с которого начинается искомый текст, а во втором начальная позиция определяется указанным количеством байтов.
Пример
В данном примере функция представлена следующей формулой: =НАЙТИ(«чай»;A4)
17) ИНДЕКС
Функция ИНДЕКС позволяет возвращать искомое значение.
Синтаксис
Формула функции ИНДЕКС имеет следующий вид:
=ИНДЕКС(массив; номер_строки; [номер_столбца])
«Номер столбца» – необязательный аргумент.
Пример
Функцию ИНДЕКС можно использовать вместе с функцией ПОИСКПОЗ с целью замены функции ВПР.
18) СОВПАД
Данная функция проверяет идентичность двух текстов, и, если они совпадают, выдает значение ИСТИНА, если же различаются – значение ЛОЖЬ.
Синтаксис
Формула функции: =СОВПАД(текст1;текст2)
Пример
Пары слов из строк 1 (A1 и B1) и 2 (A2 и B2) различны по написанию, поэтому функция выдает значение ЛОЖЬ, а слова из 3-й строки абсолютно идентичны, поэтому определяются как ИСТИНА.
Использование функции будет полезно при анализе большого объема информации с целью выявления случаев разного написания одних и тех же слов.
19) ИЛИ
Логическая функция ИЛИ возвращает значение ИСТИНА, если хотя бы один аргумент в формуле имеет значение ИСТИНА, и значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.
Синтаксис
Здесь «логическое значение1» – обязательный аргумент, остальные аргументы – необязательные. В формулу можно добавлять от 1 до 255 логических значений.
Пример
Формула в данном примере выдает значение ИСТИНА, так как 2 из 3 аргументов имеют значение ИСТИНА.
Функция И возвращает значение ИСТИНА, если все аргументы в формуле имеют значение ИСТИНА, и значение ЛОЖЬ, если хотя бы один из аргументов имеет значение ЛОЖЬ.
Синтаксис
Функция может содержать множество аргументов и имеет формулу:
«Логическое_значение1» – обязательный аргумент, остальные аргументы – необязательные.
Пример
В этом примере все аргументы имеют значение ИСТИНА, поэтому и результат ее соответствующий.
Функции И и ИЛИ очень просты в использовании, но если сочетать их вместе или в комбинации с другими функциями (ЕСЛИ и НЕ), можно вывести более сложные и интересные формулы.
21) СМЕЩ
Функция СМЕЩ возвращает ссылку на диапазон, отстоящий от ячейки или группы ячеек на указанное число строк и столбцов.
Синтаксис
Функция состоит из 5-ти аргументов и представлена следующей формулой:
Рассмотрим каждый из аргументов:
Пример
Рассмотрим пример использования функции СМЕЩ, имеющую следующую формулу: =СМЕЩ(А4;-2;2).
В данной формуле A4 – ссылка на ячейку, от которой вычисляется смещение, С2 – ячейка, на которую ссылается ячейка А4, а в ячейке E2 введена формула с результатом «27» – возвращаемая ссылка.
Зачем интернет-маркетологу функции Excel?
Мы рассмотрели самые интересные и популярные функции Excel. Могут ли они быть полезны интернет-маркетологу? Безусловно. Они помогут при анализе данных страниц сайта, подсчете количества символов в тайтле и description, преобразовании текста, поиске различных элементов в таблице. Несмотря на то, что некоторые из представленных функций очень просты и понятны, это не умаляет их ценности ни для обычного пользователя, ни для интернет-маркетолога.
Вы хотите стать специалистом с хорошей зарплатой и возможностью карьерного роста? Приходите на онлайн-курсы TeachLine от «Текстерры» за новой профессией и новыми перспективами.
В Google и «Яндексе», соцсетях, рассылках, на видеоплатформах, у блогеров