Отчего-то сложилось мнение, что Excel слишком сложный и неудобный инструмент. Но мы точно знаем, почему работа с таблицами вам понравится. Функции, существующие в Excel, ускоряют и упрощают рабочий процесс офисного сотрудника, помогают обрабатывать массивы данных и цифр, сопоставлять и находить нужную информацию
В статье приведены ТОП-10 функций Excel, описана задача и уникальность каждого.
10 супер-полезных функций, без которых работа была бы рутинной
ВПР
С помощью функции вертикального просмотра можно с легкостью найти и перенести данные текстовой строки или же в заданном диапазоне ячеек с одной таблицы в другую.
Функция ВПР имеет вот какую формулу: =ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр]), которая вмещает в себе 4 аргумента: “искомое значение”, которое находится в первом столбце диапазона и может выступать как в качестве значения, так и ссылкой на ячейку. Следующий аргумент ― “таблица”, в ней выполняется поиск искомого значения исключительно в первом столбце. В свою очередь возвращаемое значение может располагаться где угодно. Вслед за “таблицей” в формуле размещается аргумент под названием “номер столбца”, из которого будет копироваться информация. И “интервальный просмотр” ― этот аргумент не обязателен, объясняется как логическое выражение, необходимое для определения процента точности найденной функции. При наличии этого условия выделяют две функции:
- Истина ― вводится по умолчанию, выполняет поиск приближенного к искомому значению.
- Ложь ― поиск точного значения в первом столбце.
Пример
Как это выглядит на практике? На изображении размещены две таблицы, иногда встречается такое, что таблицы находятся на разных листах или даже в разных файлах. В данном случае задача состоит в том, чтобы скопировать столбец с ценами с одной таблицы и внести их во вторую. В примере показан уже результат функции ВПР. Для того чтобы выполнить задачу, необходимо в формуле ввести все необходимые данные. В данном случае формула выглядит так: =ВПР(А2;$F$2:$G$4;2;0), где:
- А2- искомое значение (яблоки);
- F2:G4 ― таблица, из которой будут переноситься данные. С F2 начинается таблица на G4 таблица заканчивается (обязательно между значениями таблицы писать знак доллар ($), иначе в дальнейшем, чтобы применить эту функцию ко всем наименованиям и протянуть результат по всем строкам не получится и все сместится);
- 2 ― номер столбца, из которого будет копироваться информация;
- 0 ― означает истинное (точное) совпадение по заданному запросу( для выбора приблизительного пишется 1)
ЕСЛИ
С помощью функции ЕСЛИ задаются определенные условия, на основе которых вычисляются два возможных результата: 1) сравнение истинно; 2) сравнение ложно.
При составлении формулы функции используют три аргумента.
Собственно, сама формула:
=ЕСЛИ(логическое_выражение;«значение_если_истина»;«значение_если_ложь»)
А теперь, аргументы. Первый аргумент “логическое выражение” ― условие, на основе которого будет вычисляться результат. Второй аргумент “значение если истина”, содержит в себе полное соответствие заданному условию. По логике вещей, если есть аргумент, соответствующий условию, должен быть и такой, который ему противоречит, в данном случае таким аргументом выступает “значение если ложь”.
Пример
Как используется обычная функция ЕСЛИ? Данная функция заключается в том, что при определенном условии она принимает определенное значение. В таблице примера есть список фруктов, их цена и количество продаж. Исходя из записей в столбце продаж, необходимо вычислить были те или иные фрукты проданы или нет.
Формула выглядит следующим образом:
=ЕСЛИ (С2=””;”не продано”;”продано”), где:
С2=””- логическое выражение (ячейка столбца продаж, где двойные кавычки означают что та самая ячейка пустая);
“не продано” ― значение если истина, то есть соответствует логическому выражению, что ячейка пустая, а значит продаж не было;
“продано” ― значение если ложь, если в ячейке стоит символ, указывающий на количество продаж.
Функция ЕСЛИФункция ЕСЛИТакже функция ЕСЛИ неплохо работает в тандеме с другими. Например, с функциями СУММЕСЛИ, СЧЁТЕСЛИ, ЕСЛИОШИБКА, СРЕСЛИ.
СУММЕСЛИ
С помощью функции СУММЕСЛИ можно суммировать данные , прописав в формуле условия и диапазон местонахождения данных.
Функция состоит из 3 аргументов, а ее формула выглядит следующим образом:
=СУММЕСЛИ(диапазон;условие;[диапазон_суммирования]). Первый аргумент “диапазон” отвечает за место, в котором происходит поиск необходимого элемента. “Условие” ― аргумент, который определяет участвующие в процессе суммированные ячейки. Важно помнить, что условия с текстом и математическими знаками следует писать в кавычках. Последний аргумент “диапазон суммирования” необязателен, он указывает на ячейку и данные, которые необходимо суммировать, если их значения отличаются от ячеек, входящих в диапазон.
Пример
Ниже есть таблица с шестью показателями, первый столбец в данном случае не пригодится. Итак, есть продавец, цена на товар, количество продаж и выручка. Задача состоит в том, чтобы подсчитать общую выручку одного из продавцов, в данном случае – Пети.
Формула функции выглядит так:
=СУММЕСЛИ(В:В;В2:В3;Е:Е), где:
В:В ― диапазон, то есть столбец, в котором функция будет искать нужного нам продавца;
В2:В3 ― ссылки на ячейки, где обозначен нужный нам продавец;
Е:Е ― диапазон суммирования, столбец с выручкой, в котором функция будет рассчитывать прибыль Пети.
И в результате подсчета получаем ― 7128, это и есть общая выручка продавца Пети от всех продаж.
ЕСЛИОШИБКА
Если вычисление по формуле выдает ошибочный результат, данная функция возвращает указанное значение или же помогает в том, чтобы та самая ошибка не отражалась.
Формула функции: =ЕСЛИОШИБКА(значение;значение_если_ошибка). Первый аргумент ”значение” представлен формулой, проверяемой на наличие или отсутствие ошибок. Второй аргумент “значение_если_ошибка” – значение, выскакивающее в ячейке при наличии ошибки.
Пример
На первом рисунке видно, что функция выдает ошибку в столбце D (так как деление на ноль невозможно) и плюс ко всему портит внешний вид таблицы. Для того чтобы избежать этого, необходимо воспользоваться функцией ЕСЛИОШИБКА. В чем заключается ее суть? На примере деления на ноль, разберем, что делать, чтобы функция не выдавала ошибку.
Формула будет выглядеть следующим образом:
=ЕСЛИОШИБКА(D1/D2;””), где:
D1/D2 ― значение, те ячейки, которые необходимо проверить на наличие или отсутствие ошибок;
“”- значение если ошибка, собственно то действие, которое необходимо применить, если вдруг возникает ошибка, а именно оставить ячейку пустой.
Ниже на рисунке уже можно увидеть вариант с уже исправленной ошибкой и ячейка абсолютно пустая, как этого требовала формула.
ЛЕВСИМВ
Функция ЛЕВСМВ выделяет необходимое количество знаков с левой стороны строки, с возможностью дальнейшего их перемещения.
Формула будет такой:
=ЛЕВСИМВ(текст;[число_знаков]), где первый аргумент “текст” – текстовая строка с символами, которые необходимо извлечь. Второй аргумент ― “число знаков” ― количество знаков, которые будут извлекаться.
Пример
Допустим, есть штрихкоды определенных товаров, из которых нужно извлечь первые 5 знаков. Как это сделать с помощью функции ЛЕВСИМВ.
Формула выглядит так: =ЛЕВСИМВ(А2;5), где:
А2 ― ячейка, из которой будут извлекаться символы;
5 ― необходимое количество символов для извлечения.
Функция сама по себе очень проста и удобна в применении.
ПОИСКПОЗ
Точнее, поиск позиций элемента. Предназначена данная функция для поиска в группе ячеек указанного элемента и определения его положения.
Формула этой функции:
=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;тип_сопоставления).
В данном случае обязательными аргументами будут “искомое значение” и “просматриваемый массив”. Аргумент “тип сопоставления” определяет, как именно происходит сопоставление между собой найденного и искомого значений и является необязательным.
1 ― значение не выше искомого;
0 ― точное совпадение;
-1 ― больше или равно искомому.
Пример
Формула: =ПОИСКПОЗ(А5;А:А;0), где:
А5- искомое значение, штрихкод, позицию которого необходимо определить;
А:А- просматриваемый массив, столбец, в котором осуществляется поиск;
0- тип сопоставления, в данном случае происходит поиск точного совпадения с запросом.
СЦЕПИТЬ
Задача функции состоит в том, чтобы объединить несколько текстовых элементов в одну строку. Формула содержит как номера так и сам текст. Можно указывать до 255 элементов и до 8192 символов.
Формула: =СЦЕПИТЬ(текст1;текст2;текст3);
Аргументом выступает “текст”- текстовый документ или ссылка на ячейку, который необходимо объединить.
Пример
Работает эта функция вот по какой формуле: = СЦЕПИТЬ(А1;” “;А2;” “;А3;), где:
А1,А2,А3 ― ссылки на ячейки, которые необходимо объединить.
Двойные кавычки с пробелом используются для того, чтобы после объединения слова не выглядели одним сплошным текстом и были читабельны, то есть в качестве пробела.
НАЙТИ
Польза этой функции заключается в том, что с ее помощью можно найти любой текст или его часть внутри текстовой строки, с указанием на его начальную позицию.
Формула: =НАЙТИ(искомый_текст;просматриваемый_текст;[начальная_позиция]).
Необязательным аргументом в строке является “начальная позиция” ― обозначает символ, с которого нужно начинать поиск. “Искомый текст” ― тот символ или слово, которое необходимо найти, а “просматриваемый текст” ― место, в котором происходит тот самый поиск.
В данном примере происходит поиск маленькой буквы “б”, в заданой строке и формула показывает какой по счету она стоит в слове.
По формуле: =НАЙТИ(“б”;А2), видно, что буква “б” в данном случае выступает как “искомый текст”, А2 выступает в роли “просматриваемого текста”, то есть текста в котором, собственно, и происходит поиск той самой буквы.
ИНДЕКС
С помощью данной функции можно вернуть искомое значение.
Формула выглядит так: =ИНДЕКС(массив; номер_строки; [номер_столбца]), где:
“Номер столбца” ― необязательный аргумент.
Пример
В данном случае необходимо извлечь цену на бананы, для этого приминима вот какая формула; =ИНДЕКС(С2:С4;3). С2 и С4 выступаю массивом, тот столбец в котором будет происходить поиск нужного значения. 3 ― номер строки, в котором расположена извлекаемая цена. 3 потому что подсчет строк в столбце начинается не С1, а с С2, которая считается начальной строкой поиска.
ПСТР
Чтобы извлечь нужное число знаков, начиная с определенной позиции, смело можно использовать функцию ПСТР.
Формула состоит из 3 аргументов:
=ПСТР(текст;начальная_позиция;число_знаков). Аргумент “текст” ― это строка со знаками, необходимыми для извлечения. Второй аргумент “начальная позиция” отвечает за указанное место, с которого начинается извлекаемый текст, и в третьем аргументе “число знаков” указывается количество знаков, которые будут извлекаться.
Пример
Функция ПСТР возвращает фрагмент текста с указанных позиций. Допустим, есть определенный штрихкод и необходимо извлечь из него определенные символы.
Формула выглядит так: =ПСТР(А2;3;7). А2 ― строка, в которой находится нужный штрихкод, 3 ― позиция цифры, с которой будет начинаться извлекаемый текст, 7 ― количество извлекаемых знаков. В результате, с заданного штрихкода 457816821 были извлечены следующие символы ― 7816821.
Стоит ли еще сомневаться в пользе функций Exсel? Нет! Даже невзирая на кажущуюся, на первый взгляд, простоту, каждая функция обладает своими особенностями и будет полезна как для обычного пользователя, так и для интернет-маркетолога. Как только Вы столкнетесь с анализом текста или же возникнут проблемы с подсчетом числа знаков в тайтле, появится необходимость придать тексту читабельный вид и с легкостью находить нужный элемент в таблице, “скорой помощью” в этом непростом деле станут функции Exсel.
