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

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

Практически каждый сталкивался с Excel в школе, университете, а затем продолжает использовать в профессиональной деятельности и даже личной жизни. С его помощью можно оцифровывать и упорядочивать различные данные — от финансовых отчетов до таймлайна собственной свадьбы. По данным исследования компаний Artezio и Comidware, больше 60% опрошенных россиян используют Excel в своей работе. А по данным портала Burning Glass, для 67% стартовых вакансий требуется продвинутое знание Microsoft Excel.

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

С помощью функции «СЧЁТЕСЛИ» можно подсчитать количество ячеек с одинаковыми значениями. Допустим, что из имеющейся таблицы с группами продовольствия требуется подсчитать сколько раз продавались товары из группы “Овощи”. Для этого в ячейке, в которой нужно получить искомое значение, следует записать формулу «=СЧЁТЕСЛИ($А$2/$А$20;i2)», где $А$2:$А$20 — ссылка на диапазон наименований, среди которых мы ищем совпадающие, ссылку необходимо сделать абсолютной с помощью значка $, чтобы при копировании или протягивании ссылка не менялась, а i2 — критерий (в данном случае, “Овощи”) , по которому проходит поиск.

Логическая функция Excel, которая помогает вычистить таблицу от ошибок, которые возникают при вычислениях. Допустим, известны два параметра и необходимо вычислить третий. Если записать в первой ячейке, в которой нужно получить искомое значение, формулу «=В2/А2», и протянуть ее, то в некоторых строках можно будет увидеть ошибку, так как на ноль делить нельзя. Такие ошибки могут помешать при дальнейших вычислениях.

А теперь применим функцию «ЕСЛИОШИБКА». В первой ячейке, в которой нужно получить искомое значение, следует записать формулу «=ЕСЛИОШИБКА(В2/А2;0)» и протянуть ее. Здесь В2/А2 — проверяемая формула, а 0 — значение, которым нужно заменить ошибку. Причем заменить можно и на другое значение, указав его в кавычках — на любую фразу или просто оставить ячейку пустой. В этих случаях формула будет выглядеть так: «=ЕСЛИОШИБКА(В2/А2;”нет данных”)» и «=ЕСЛИОШИБКА(В2/А2;””)». Таким образом, можно избавиться от ошибок и подготовить таблицу для дальнейших вычислений.

Текстовая функция «СЦЕПИТЬ» позволяет соединять содержимое из разных ячеек в одну. Например, есть выгрузка из базы клиентов, информация о которых распределена по столбцам. При определенных обстоятельствах было бы удобнее, чтобы информация из строки была в одной ячейке. Для этого в ячейке, в которой нужно получить искомое значение, следует записать формулу «=СЦЕПИТЬ(А2;” “;B2;” “;С2;”, “D2)», где А2, В2, С2 и D2 — значения, которые нужно соединить, а « “_“ и ",_" » — пробелы между словами и запятая с пробелом, разделяющая информацию по смыслу. Затем можно протянуть формулу.

Функция «СУММПРОИЗВ» перемножает соответствующие элементы заданных диапазонов ячеек и выдает сумму произведений. Представим, что для отчета необходимо рассчитать сумму, потраченную на налоги по нескольким позициям. Вместо того, чтобы перемножать все строки отдельно, получать сумму налога по каждому товару, а затем складывать суммы в общую в отдельной ячейке, можно просто применить «СУММПРОИЗВ». Для этого в ячейке, в которой нужно получить искомое значение, следует записать формулу «=СУММПРОИЗВ(В2:В12;С2:С12;D2:D12)», где В2:В12, С2:С12 и D2:D12 — диапазоны (столбцы) , которые должны между собой перемножаться.

Функция «ОУРУГЛ» совершать математическое округление (чисел либо без знаков после запятой, либо до одного, двух, трех и т.д. знаков после запятой. Чтобы воспользоваться функцией необходимо в ячейке, в которой нужно получить искомое значение, записать формулу «=ОКРУГЛ(А2;2)», где А2 — ячейка со значением, которое необходимо округлить, а 2 — количество знаков после запятой.

Сергей Куксенок, предприниматель, автор курса «Excel: основы анализа и визуализации данных» на платформе «Академика»:

Безусловно, выше описан далеко не весь функционал Excel. Еще с его помощью можно строить сводные таблицы и срезы, прогнозные модели, визуализировать данные.

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