Математические функции в Excel: специфические особенности и примеры

• 21-02-2017, 20:38
• 3832

Программы

Самые простые математические функции в Эксель это сложение, вычитание, умножение и деление. Для этого нам понадобится знак «+» и функция =СУММ. При написании формулы в ячейках Excel (Эксель) мы используем знак «=». Пример: в любой ячейке пишем =5+5, нажимаем Ввод и получаем результат. Программа посчитала за нас. Можно проделать это, используя связь ячеек. Пример: если в отдельной ячейке написать формулу =СУММ и выделить 2 ячейки с числами, мы получим их сумму. Таким образом, можно сложите все необходимые вам ячейки, с результатом в одной, в которой была вставлена формула. Использование формул упрощает работу с Excel, вам не нужно постоянно использовать форму =5+5, вы просто выделяете ячейки и нажимаете Enter. Выполнение данного примера можно сделать и со знаком «+». Например: выбрав ячейку, в которой вы хотите увидеть результат сложения, начинаете писать формулу со знака равенства (=), выделяете первую ячейку с числом и соединяете со второй ячейкой знаком сложения (+). Нажав на ячейку с формулой, вы всегда можете проверить, числа из каких ячеек сложены тут. Также эта информация отображается в строке формул. Функция вычисления производится таким же образом, как и сложение, но вместо знака «+» используют знак «-». Умножение похоже на обе, вышеперечисленные функции. Только знака умножения выступает звездочка (*). Деление похоже на умножение, здесь же, для создания формулы нам понадобится косая черта (/) Рассмотрим следующий комплекс функций. Начнем с функции МИН. Она используется для выявления наименьшего значения в строке или в столбике. Итак, нам понадобится небольшая таблица, где мы проверим на примере эту функцию. В ячейке пишем =МИН(выделяем диапазон чисел) и нажимаем Ввод Из примера следует, что из списка стоимости абонементов в тренажерном зале минимальная – 80. Функция МАКС выполняет противоположное действие, находит наибольшее значение в таблице. Функция =СРЗНАЧ выражает среднее значения в выделенном диапазоне чисел. Функция ЕСЛИ часто является востребованной в любой сфере бизнеса. Рассмотрим пример с рестораном и планом продаж официанта. Допустим официанту ставиться план по продаже продукции на сумму 70000. В первом случае он не достиг этой отметки и заработал для заведения 60000. Сейчас мы узнаем, получит он бонус или нет. В нужной ячейке пишем формулу =ЕСЛИ(B2>C2;D2;0), где знак «>» – означает «больше, чем», B2 – это заработанные официантом средства, С2 – план, D2 – бонус, если официант выполнил план и 0 – если не выполнил. Во втором же случае, официант выполнил план и получил вознаграждение. Предлагаю вашему вниманию ознакомится с самыми используемыми математическими функциями в Excel: Функция ОКРУГЛ Достаточно проста в использовании, и популярная функция. Главная ее особенность — это округление значений согласно всем математическим правилам, то есть, если число от 1 до 4 то, функция ОКРУГЛ будет округлять результат до 0 вниз, а если число от 5 до 9, то округление будет производиться вверх до 10. Эта функция очень нужна, когда результат очень важен и каждая копеечка на счету, особенно это касается работы в бухгалтерском учёте. Очень часто возникает цепочка вычислений, когда число после запятой уходит в бесконечность, а вам нужно только два знака после запятой для обозначения копеек, в случае, когда вам такие значения нужно суммировать может оказаться что потеряны копейки в вычислениях. Они конечно не потеряны, а просто скрыты, но от этого вам легче не будет, а вот эта функция поможет вам в промежуточных вычислениях получить правильные результаты и как результат получить точные цифры и меньше головной боли. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ Одна из интересных функций, так как ее аналог — это итоги при форматировании из простой таблицы в умную. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ позволит вам найти промежуточные итоги по нужному вами критерию в указанном диапазоне. Но, увы, диапазон для подсчёта промежуточных итогов должен быть только вертикальным, и поскольку большинство таблиц вертикальные это не является проблемой. Функция функционально может подводить итог по 11 формулах. Что очень расширяет ее границы использования, это и среднее значение, и сумма, и минимальное с максимальным значениями, и отклонения, и многие другие полезные функции. А также данная функция может игнорировать вложенные итоги, чтобы избежать двойного суммирования. Функция СЛУЧМЕЖДУ Функция СЛУЧМЕЖДУ позволит вам случайным образом получить любое число в заданных диапазонах значений. Генерация таких чисел позволит вам тестировать формулы, заполнять диапазоны случайными числами, формировать списки вопросников, да и многое другое. Применение функции достаточно специфично и не всегда ее можно использовать, но вот при проработке новых форм и заполнений диапазонов случайными числами, это отличный инструмент, хотя для многих она хорошо и в других возможностях. Функция СУММ Это, наверное, самая популярная функция в Excel. Я совсем не представляю себе таблиц, где функция СУММ не будет использована по своему назначению. Там, где надо просуммировать два и более числа, вы всегда встретите эту функцию. Эта функция заслужено занимает список 10 самых распространённых функций Excel, а не только математического раздела. При создании таблиц для экономических или бухгалтерских расчётов, обязательно будет использована функция СУММ, так как она является основой для основ. О пользе и местах где применяется СУММ можно много говорить, так как эти данные очень огромные, но надо идти дальше по нашему списку. Функция СУММЕСЛИ Это также одна из самых полезных функций в MS Excel. Пользуются этой функцией в основном опытные пользователи, так как она соединяет в себе логическую функцию ЕСЛИ и математическую СУММ, а вот такую полезную смесь добавили в раздел «Математические функции». В этом заключается ее сложность, это соединение двух начал, но это только на первый взгляд так кажется, сама по себе функция очень проста, удобна и полезна. Необычайно полезной станет при проведении анализа, когда с массы данных нужно произвести суммирование ваших данных по нужному критерию, выбрать данные за месяц, по определённому контрагенту и прочее. Функция СУММЕСЛИ также является одной из самых полезных и нужных инструментов при работе с данными в бухгалтерской и экономической деятельности, особенно в последнем случае. Функция СУММЕСЛИМН Шестой функцией нашего списка станет функция СУММЕСЛИМН. По большому счёту, эта функция полностью копирует возможности предыдущей функции нашего списка за исключением той детали, что суммирование можно проводить не по одному критерию, а по нескольким, что значительно повышает производительность формулы, но требует специфических условий для ее применения. Если у вас много данных и вам их нужно просуммировать по группам и общим критериям, тогда СУММЕСЛИМН именно то, что сможет вам помочь. Функция СУММПРОИЗВ Это еще одна из топовых функций в арсенале MS Excel, это функция СУММПРОИЗВ. Эта функция является благодатью для экономистов, так как она совмещает в себе возможности таких функций как СУММ, ЕСЛИ, СУММЕСЛИ и СУММЕСЛИМН и является мощным инструментом при работе с диапазонами значений и массивами. И я замечу, что одновременная работа с 255 массивами, это очень сильно и мощно. Если вам функция СУММПРОИЗВ еще неизвестна, то я советую вам ее более детально изучить, так ее возможности помогут вам увеличить собственную производительность. Функция ОТБР Функция ОТРБ не предлагает точности, так как ее основная обязанность игнорировать все, что не касается целого числа, то есть дроби принудительно отбрасываются, что не очень приемлемо в работе бухгалтера, но будет полезно в работе экономиста. Я думаю, частенько вы используете возможности Excel по скрытию разрядности, так как копейки при анализе в сотни тысяч или миллионы не являются важными, а важно получить точное, целое число. При работе с плановыми и статистическими расчётами функция ОТБР будет одним из лучших помощников. Функция ОКРУГЛВВЕРХ Еще одна из функций округления, это функция ОКРУГЛВВЕРХ. Как вы уже догадались, эта функция всегда округляет до ближайшего большого числа по модулю, то есть если у вас 12, то округление будет 20, если в условиях формулы стоят другие критерия, то будет округлять и к 100 и к 1000 и так далее. Я частенько ее использую, когда есть необходимость плановые расчёты вести в тысячах и все мои полученные данные аккуратно округляются к ближайшей тысяче, что делает данные наглядными и красивыми, отнюдь не влияя негативно на полученный результат. Функция ОКРУГЛВНИЗ Следующая наша функция, это будет функция ОКРУГЛВВЕРХ, является антиподом предыдущей функции нашего списка ТОП 10 математических функций. Как видно из самого названия основное отличие ее это в вертикальной направленности округления, то было вверх, а эта функция округляет вниз. Ее применение также порадует экономистов или других специалистов финансового сектора, которые не нуждаются в абсолютно точных значениях, а существует необходимость их округления, в этом случае функция ОКРУГЛВНИЗ поможет привести ваш отчёт в красивый и функциональный документ. СУММ СУММ(Диапазон1[; Диапазон2; … ]) Вычисляет сумму содержимого ячеек указанного диапазона или нескольких диапазонов. СУММПРОИЗВ СУММПРОИЗВ(Диапазон1; Диапазон2; … Диапазон k) Вычисляет сумму произведений указанных диапазонов. Количество элементов во всех диапазонах должно быть одинаковым. Пример: СУММПРОИЗВ(C2:C6;D2:D6) ПРОИЗВЕД ПРОИЗВЕД(Диапазон1; Диапазон2; … Диапазон k) Вычисляет произведение значений указанных диапазонов. Пример: ПРОИЗВЕД(B3:B7) ОКРУГЛ ОКРУГЛ(Выражение, Разрядов) Возвращает значение, полученное путем округления числа до указанного количества разрядов после запятой. Примеры: ОКРУГЛ(B11;2) ОКРУГЛ(СУММ(В4:B10);2) Округление выполняется в соответствии с известным правилом: если значение отбрасываемого разряда больше или равно пяти, то округление выполняется путем добавления единицы в последний значащий разряд, в противном случае незначащие разряды отбрасываются. Примеры округления чисел ОКРВВЕРХ ОКРВВЕРХ(Выражение; Точность) Возвращает значение, полученное в результате округления числа в сторону увеличения с указанной точностью. Например, при округлении до десятков разряд единиц в числе обнуляется, а число десятков увеличивается на единицу (если разряд единиц исходного числа не был равен нулю). Примеры округления чисел в сторону увеличения Функция Значение =ОКРВВЕРХ(351;10) 360 =ОКРВВЕРХ(353;100) 400 =ОКРВВЕРХ(125300;1000) 126000 ОКРВНИЗ ОКРВНИЗ(Выражение; Точность) Возвращает значение, полученное в результате округления числа в сторону уменьшения с указанной точностью. Например, при округлении до десятков разряд единиц в числе обнуляется. Примеры округления чисел в сторону уменьшения Функция Значение =ОКРВНИЗ(351;10) 350 =ОКРВНИЗ(353;100) 300 =ОКРВНИЗ(125300;1000) 125000 ОСТАТ ОСТАТ(Делимое; Делитель) Вычисляет остаток от деления одного числа на другое. Формула для выделения дробной части числа, находящегося в ячейке: =ОСТАТ(A1;ЕСЛИ(A1<0;-1;1)) ЦЕЛОЕ ЦЕЛОЕ(Выражение) Возвращает целую часть числа. </div>

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

Excel: общие сведения

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

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

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

Основа рабочего листа – таблица, состоящая из строк и столбцов. Их перекрестие составляет ячейка, куда вводятся данные или формулы. Строки названы арабскими цифрами, а столбцы – латинскими буквами. Считалось, что рабочий лист бесконечен в обе стороны, однако это не так. Он содержит 65536 строк и 256 столбцов. По другим данным в рабочем листе содержатся 16384 столбцов и 1048576 строк. Каждой ячейке присваивается уникальный адрес:А5.

Использование ссылок

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

Существуют:

• простые;
• ссылки на другой лист;
• абсолютные;
• относительные ссылки.

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

• пересечение столбца и строки (А4);
• массив ячеек по столбцу А со строки 5 до 20 (А5:А20);
• диапазон клеток по строке 5 со столбца В до R (В5:R5);
• все ячейки строки (10:10);
• все клетки в диапазоне с 10 по 15 строку (10:15);
• по аналогии обозначаются и столбцы: В:В, В:К;
• все ячейки диапазона с А5 до С4 (А5:С4).

Следующий формат адресов: ссылки на другой лист. Оформляется это следующим образом: Лист2!А4:С6. Подобный адрес вставляется в любую функцию.

Абсолютные и относительные ссылки

Особого внимания требуют такие форматы адресов.

Выделяют:

• абсолютные;
• относительные;
• смешанные ссылки.

Под относительными адресами понимают указанные диапазоны или конкретные ячейки, которые при копировании формулы и ее последующей вставке изменяются автоматически. К примеру, необходимо суммировать в столбце С несколько значений. Формула будет выглядеть следующим образом: =сумм(С5:С9). Если допустить, что подобных столбцов несколько, и в каждом нужно найти сумму, проще скопировать формулу и вставить в нужные клетки. После проделанных манипуляций можно заметить, что диапазоны поменялись автоматически.

Под абсолютными ссылками понимаются ячейки, которые в процессе копирования не меняют своего вида. К примеру, имеется клетка А5. Она участвует в формуле вычисления суммы нескольких значений. Чтобы она не изменилась ни при одном копировании, перед обозначением столбца и строки ставят знак $. Абсолютная ссылка будет выглядеть следующим образом: $А$5.

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

Ссылки формата R1C1

В новых версиях Excel адреса ячеек видоизменились. Некоторые не могут понять, в чем разница между А1 и R[-1]C[-5].

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

• относительный адрес строки, расположенной на 3 позиции выше указанной: R[-3];
• абсолютная ссылка на ячейку, содержащуюся на 10-й строке 10-го столбца: R10C10;
• относительный адрес на ячейку, расположенную на 5 позиций выше в активном столбце (где прописывается формула): R[-5]C;
• абсолютная ссылка на текущую клетку, где прописывается формула: R;
• относительная ссылка на ячейку, расположенную на 8 строк правее и 5 строк ниже активной: R[5]C[8].

Категории математических функций

При выпадающем списке в программе на вкладке «Формулы» содержится 11 групп. В их число входят и математические функции в Excel, которые условно разделяются на следующие категории. Рассмотрим основные. Арифметические операции:

• СУММ: складывает необходимые значения.
• ПРОИЗВЕД: находит произведение заданных чисел или содержимого ячеек.
• ЦЕЛОЕ: необходимо для нахождения целой части.
• СТЕПЕНЬ: возводит число в заданную степень.
• КОРЕНЬ: извлекает корень из содержимого ячейки или заданного вручную числа и т. д.

Также можно выделить тригонометрические функции:

• SIN: находит синус от заданного значения.
• ASIN: необходима для вычисления арксинуса числа.
• LN: находит натуральный логарифм.
• EXP: возводит число Е в заданную пользователем степень и др.

Округление по различным критериям:

• ОКРВВЕРХ: округляет значение до ближайшего целого.
• ОКРУГЛВВЕРХ: находит число, округленное до ближайшего целого по модулю.
• НЕЧЕТ: функция, позволяющая округлить до нечетного числа. Положительное – в большую сторону, отрицательное – в меньшую.
• ОКРУГЛ: необходима для нахождения результата округления с заданием количества десятичных разрядов и др.

Для работы с векторами и матрицами:

• СУММПРОИЗВ: функция, необходимая для возвращения суммы массивов.
• СУММВРАЗН: при наличии 2 диапазонов возвращает сумму квадратов разностей.
• МОБР: функция, необходимая для получения обратной матрицы.
• МОПРЕД: требуется для нахождения определителя матрицы и т. д.

Применение математических функций

Математические функции в Excel могут вводиться тремя способами:

• вручную;
• через панель инструментов;
• посредством диалогового окна «Вставка функции».

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

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

Чтобы воспользоваться третьим вариантом, нужно нажать кнопку Fx или CTRL+F3. На панели инструментов также имеется «Мастер подстановки» на вкладке «Формулы». Послы выполнения одного из перечисленных действий появляется окно, в котором нужно выбрать соответствующую группу, а после – нужную функцию. Далее последует окно «Аргументы и функции», в котором выполняются необходимые манипуляции.

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

Использование встроенных функций

Популярны среди пользователей встроенные математические функции в Excel. Их синтаксис состоит из 2 частей: имени и аргументов. Последних может быть один или несколько. Обязательно формула начинается со знака «=». В противном случае Excel выдаст ошибку о неверном вводе функции.

К примеру, имеется формула «=СУММ(А2:М10)». В таком случае речь пойдет о суммировании всех значений диапазона А2:М10. Аргументы обязательно заключаются в скобки и указываются без пробелов. Писать имя функции с заглавной или строчной буквы – дело каждого. Для программного продукта это не играет никакой роли.

Если указана формула «=СУММ(А2;С7;М10)», это означает, что суммируются только 3 указанные ячейки. В состав функции могут входить до 30 элементов в аргументе.

Графики математических функций

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

Пример. Построить график функции для оператора sin. Шаг приращения = 0,5.

Примеры математических функций в Excel

Задание 1. Использовать функцию «суммесли». Данный оператор суммирует значения, если они удовлетворяют определенному условию и находятся в нужном диапазоне.

Задание 2. Использовать функцию «abs». Она находит модуль от заданного числа. В диалоговом окне указывают лишь один аргумент в виде одной ячейки. Диапазон участвовать в данном операторе не может.

Задание 3. Применить функцию «степень». Суть заключается в возведении заданного числа в указанную пользователем степень. У функции 2 аргумента.

Задание 4. Получить римскую запись от числа, написанного арабскими цифрами. Для этого понадобится формула «Римское».

Задание на закрепление материала

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

Здесь вы найдете больше сотни моих статей по самым лучшим, полезным и эффективным приемам, трюкам и способам работы в Microsoft Excel. Пройдитесь по полному списку или сначала выберите раздел слева и – вперед!

• ВСЕ(367)
• ТОП-10(10)
• Бизнес-анализ(32)
• Выпадающие списки(10)
• Даты и время(17)
• Диаграммы(29)
• Диапазоны(51)
• Дубликаты(16)
• Защита данных(11)
• Интернет, почта(12)
• Книги и листы(17)
• Макросы(18)
• Сводные таблицы(19)
• Текст(32)
• Форматирование(24)
• Функции(42)
• Всякое(27)

Сортировка: дата создания дата изменения просмотры комментарии Поиск последнего вхождения (инвертированный ВПР) Все стандартные функции поиска (ВПР, ГПР, ПОИСКПОЗ и т.д.) ищут только сверху-вниз и слева-направо. Что же делать, если нужно реализовать обратный поиск совпадений, т.е. искать не первое, а последнее вхождение требуемого значения в списке? Обманчивая простота функции ПОСЛЕД (SEQUENCE) Разбор на примерах возможностей новой функции ПОСЛЕД (SEQUENCE) – генератора числовых последовательностей из последнего обновления Office 365 с динамическими массивами. ВПР и числа-как-текст Как научить функцию ВПР (VLOOKUP) искать значения, когда в исходных данных встречаются “числа-как-текст”, что приводит к ошибкам #Н/Д. Самый быстрый ВПР Тест скорости 7 разных вариантов реализации поиска и подстановки данных из одной таблицы в другую: ВПР, ИНДЕКС+ПОИСКПОЗ, ПРОСМОТРХ, СУММЕСЛИ и т.д. Кто будет самым быстрым? Функция ПРОСМОТРX – наследник ВПР Подробный разбор новой функции ПРОСМОТРX (XLOOKUP), которая приходит на смену классической ВПР (VLOOKUP). Функции динамических массивов: СОРТ, ФИЛЬТР и УНИК Разбор на живых примерах трех главных функций динамических массивов в новом вычислительном движке Excel: СОРТ, ФИЛЬТР и УНИК + их сочетаний для решения практических задач. Быстрый прогноз функцией ПРЕДСКАЗ (FORECAST) Вычисление простого прогноза по линейному тренду с помощью функции ПРЕДСКАЗ (FORECAST). Превращение текста в число функцией Ч (N) Как добавить невидимый текстовый комментарий к формуле в ячейке с помощью функции преобразованиия Ч(N). Разбор функции ДВССЫЛ (INDIRECT) на примерах Подробный разбор на примерах нюансов и особенностей функции ДВССЫЛ (INDIRECT), позволяющей реализовать косвенные текстовые ссылки. Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE) Подробный разбор функции замены текста ПОДСТАВИТЬ (SUBSTITUTE) и примеров ее использования для удаления неразрывных пробелов, подсчета количества слов, извлечения первых двух слов из текста. Суммирование по множеству условий функцией БДСУММ (DSUM) Как использовать одну из малоизвестных, но крайне полезных функций БДСУММ (DSUM) для выборочного суммирования по одному или нескольким сложным условиям (еще и связанным между собой И-ИЛИ). Удаление лишних пробелов функцией СЖПРОБЕЛЫ (TRIM) и формулами Как удалить из текста лишние пробелы в начале, в конце или между словами с помощью функции СЖПРОБЕЛЫ (TRIM) или формул. Анализ топовых значений функциями НАИБОЛЬШИЙ и НАИМЕНЬШИЙ Как использовать функции НАИБОЛЬШИЙ (LARGE) и НАИМЕНЬШИЙ (SMALL) для поиска предельных значений, составления рейтингов и ТОПов, сортировки формулами и др. Частотный анализ по интервалам функцией ЧАСТОТА (FREQUENCY) Как быстро подсчитать количество значений, попадающих в заданные интервалы “от-до” с помощью функции ЧАСТОТА (FREQUENCY) и формул массива. Создание внутренних и внешних ссылок функцией ГИПЕРССЫЛКА Максимально полное описание всех возможностей функции ГИПЕРССЫЛКА, умеющей создавать ссылки на ячейки внутри книги, внешние файлы, веб-страницы и т.д. Получение элемента из набора по номеру функцией ВЫБОР (CHOOSE) Как с помощью функции ВЫБОР (CHOOSE) реализовать выборку элементов из набора, работу с массивами, динамические итоги и склейку диапазонов. Как не забивать гвозди микроскопом с функцией СУММПРОИЗВ Для чего (на самом деле!) нужна функция СУММПРОИЗВ (SUMPRODUCT) и какие замечательные вещи она умеет делать (включая выборочный подсчет из ЗАКРЫТОГО файла). Поиск точных совпадений с учетом регистра функцией СОВПАД (EXACT) Большинство функций Excel не различают строчные и прописные буквы. Если же это необходимо, то сможет помочь функция СОВПАД (EXACT), умеющая сравнивать данные с учетом регистра. Извлечение информации о ячейке функцией ЯЧЕЙКА (CELL) Как получить подробную информацию о ячейке и ее параметры с помощью функции ЯЧЕЙКА (CELL). Используя ее можно определять тип введенных в ячейку данных, ее формат, наличие защиты и т.п. Визуализация значками с функцией СИМВОЛ (CHAR) Как выводить нестандартные символы и пиктограммы с помощью функции СИМВОЛ (CHAR) для визуализации количества проданного товара, динамики роста-падения и т.п. Перехват ошибок в формулах функцией ЕСЛИОШИБКА (IFERROR) Как перехватывать любые, возникающие не по вашей вине, ошибки в формулах,  и заменять их на что-то более полезное и удобное с помощью функции ЕСЛИОШИБКА (IFERROR). Превращение текстовой даты в полноценную функцией ДАТАЗНАЧ (DATEVALUE) Как превратить в полноценную дату текстовые ее написания вида “8 мар 13”, “2017.3.8”, “09 сентябрь 2016” и т.д. с помощью функции ДАТАЗНАЧ. Суммирование по “окну” на листе функцией СМЕЩ (OFFSET) Как использовать функцию СМЕЩ (OFFSET), чтобы суммировать из динамического диапазона-“окна” на листе с заранее неизвестными размерами и положением. Поиск позиции элемента в списке с ПОИСКПОЗ (MATCH) Как использовать функцию ПОИСКПОЗ (MATCH) для поиска позиции нужного элемента в списке, первой или последней текстовой ячейки или ячеек с заданным значением в диапазоне. Левый ВПР Как реализовать в Excel аналог функции ВПР (VLOOKUP), который будет выдавать значения левее поискового столбца. 5 вариантов использования функции ИНДЕКС (INDEX) Подробный разбор всех пяти вариантов применения мегамощной функции ИНДЕКС (INDEX): от простого поиска данных в столбце, до двумерного поиска в нескольких таблицах и создания авторастягивающихся диапазонов. Поиск последней непустой ячейки в строке или столбце функцией ПРОСМОТР Как найти значение последней непустой ячейки в строке или столбце таблицы Excel с помощью функции ПРОСМОТР (LOOKUP). Поиск ближайшего рабочего дня функцией РАБДЕНЬ (WORKDAY) Что если при расчете сроков нужная вам дата выпадет на выходные? Как найти ближайший рабочий день к заданной дате, учитывая выходные и праздники? Поиск минимального или максимального значения по условию Как найти в диапазоне чисел минимальное или максимальное значение по условию с помощью функций МИНЕСЛИ и МАКСЕСЛИ, с помощью формул массива, функции ДМИН или сводной таблицы. Трехмерный поиск по нескольким листам (ВПР 3D) Как реализовать поиск по трем измерениям, т.е. нахождение нужного листа в книге, а затем вывод содержимого ячейки с пересечения заданной строки и столбца. Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ Если вы когда-нибудь пытались сослаться на ячейку сводной таблицы, то должны были встречать эту функцию (от которой большинство шарахается). Зачем она нужна на самом деле и в каких случаях она может здорово помочь? Преобразование формул в значения 6 способов преобразовать формулы в ячейках листа Excel в значения. Как для выделенного диапазона, так и сразу для всего листа или даже целой книги. Выборочные вычисления по одному или нескольким критериям Несколько способов решить одну из наиболее распространенных задач при работе в Microsoft Excel – вычислить итоги (сумму, среднее, количество и т.д.) только для тех строк в таблице, которые удовлетворяют заданному условию или набору из нескольких условий. Поиск ближайшего числа Как проверить попадание параметра в один из заданных диапазонов и, при этом, обойтись без громоздких вложенных проверок с помощью кучи функций ЕСЛИ (IF)? Использование функции ВПР (VLOOKUP) для подстановки значений Одна из самых мощных и красивых функций в Excel – функция ВПР (VLOOKUP), позволяющая автоматически находить нужные значения в таблице. О том, как ее правильно использовать – просто и понятно. Вычисление возраста или стажа функцией РАЗНДАТ (DATEDIF) Как точно вычислить возраст/стаж человека (или любой другой интервал времени между началом и концом) в полных годах, месяцах или днях с помощью недокументированной функции Excel РАЗНДАТ (DATEDIFF). 3 способа склеить текст из нескольких ячеек Как собрать текст из нескольких ячеек в одну с помощью функций СЦЕП, СЦЕПИТЬ, ОБЪЕДИНИТЬ и объединять ячейки без потери текста всех ячеек кроме верхней левой специальным макросом. Номер недели по дате функцией НОМНЕДЕЛИ Как определить номер рабочей недели для любой заданной даты (по ГОСТ, по ISO и т.д.) с помощью формул и функций НОМНЕДЕЛИ (WEEKNUM) и НОМНЕДЕЛИ.ISO (WEEKNUM.ISO) Многоразовый ВПР (VLOOKUP) Как извлечь ВСЕ данные из таблицы по заданному критерию. Функция ВПР (VLOOKUP) находит значение только по первому совпадению, а нужно вытащить все. Поможет хитрая формула массива! Подробнее… Конвертирование величин функцией ПРЕОБР (CONVERT) Сколько грамм в двух унциях и дюймов в пяти метрах? Сколько минут в неделе и грамм в столовой ложке? Статья о том как использовать малоизвестную функцию ПРЕОБР (CONVERT) для конвертации из одной системы мер в другую в Excel. ВПР (VLOOKUP) с учетом регистра Как при помощи формулы массива сделать аналог функции ВПР (VLOOKUP), который при поиске будет учитывать регистр и различать строчные и прописные символы. Двумерный поиск в таблице (ВПР 2D) Как искать и выбирать нужные данные из двумерной таблицы, т.е. производить выборку не по одному параметру (как функции ВПР или ГПР), а по двум сразу. Продвижение самостоятельно

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

Исходный файл (скачать CSV-файл, 1.5 КБ)

В качестве исходных данных рассмотрим файл типа «Распределение» в котором собраны продвигаемые поисковые запросы с указанием (Рис. 1):

• Продвигаемого URL
• Релевантного URL
• Позиции в Яндексе
• Частоты
• Позиции в Google
• Недостающих слов в теге Title
• Прочих

Рис. 1. Исходная таблица для работы.

Далее, рассмотрим, как можно быстро решить самые типовые задачи.

Сортировка по любому полю

Для этой операции будет достаточно преобразовать рабочую область таблицу с заголовками (Рис. 2). После чего будет доступна сортировка по любому из полей (Рис. 3) при нажатии на квадратик со стрелочкой справа от названия колонки.

Рис. 2. Вставка таблицы с заголовками в Excel файл для дальнейшей работы.

Рис. 3. Сортировка текстовых полей от «А до Я» и от «Я до А» в таблице в Excel. Для численных полей доступна сортировка от минимального к максимальному значению и наоборот.

Выделение дублей или уникальных значений

Часто, поисковые запросы в таблице могут дублировать друг друга или наоборот, вам требуется найти все уникальные запросы, чтобы сравнить два списка. Для этого пригодится функция «Условное форматирование» * (Рис. 4) и создание нового правила для неё. Прежде чем нажать на кнопку «Условное форматирование» требуется выделить область, с которой будет происходить дальнейшая работа по выделению/форматированию значений. В нашем случае, выделена первая колонка целиком.

Рис. 4. Создание нового правила для условного форматирования выделенной области.

После, выбираете «Форматировать только уникальные или повторяющие значения», задаете тип, на примере это «Повторяющиеся» и Формат, на примере это оранжевый цвет (Рис. 5).

Рис. 5. Задание оранжевого цвета для форматирования повторяющихся значений в выделенной области.

Удаление повторяющихся значений

После применения правила повторяющиеся значения в выделенной области будут подсвечены оранжевым цветом (Рис. 6). По данному цвету можно осуществить сортировку в таблице и проработать или удалить данные строчки.

Рис. 6. Удаление повторяющегося ключевого запроса после сортировки по оранжевому цвету в таблице.

Выделение цветов значений в диапазоне

Для цветового выделения значений в заданном диапазоне также удобным оказывается применение условного форматирования. Для этого требуется выделить интересующие нас колонки или ячейки и создать новое правило для функции «Условное форматирование», далее выбрать «Форматировать только ячейки, которые содержат» и задать значения ячейки в требуемом диапазоне, на примере это от 1 до 10 (Рис. 7).

Рис. 7. Задание форматирования зеленых цветом для ячеек между 1 и 10 через функцию условного форматирования.

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

Рис. 8. Пример выделения в таблице нужных ячеек с позициями в ТОП-10 зеленым цветом.

Поиск запросов с заданным словом

Часто, требуется быстро найти и выделить все запросы, в которых содержится заданное слово, скажем, слово «сайт». Для этого аналогично можно использовать функцию условного форматирования с заданием формата для ячеек, которые содержат текст «сайт» (Рис. 9).

Рис. 9. Пример быстрого поиска и работы с поисковыми запросами, в которых содержится слово «сайт».

Расчет значения по формуле

В таблице также удобным оказывается производить расчёт какого-либо показателя по формуле, опираясь на значения в других показателей. В частности, можно вычислить прогнозируемый бюджет как среднее значение между бюджетом из системы SeoPult и MegaIndex (Рис. 10). Для этого достаточно задать формулу для первой ячейки таблицы и значение вычиститься для всей таблицы.

Рис. 10. Расчёт ссылочного бюджета, в таблице Excel опираясь на значения от агрегаторов SeoPult и MegaIndex.

Копирование значений из колонки, вычисленной по формуле

Если вы заходите теперь скопировать на другой лист или в другой файл значения из вычисляемой колонки «На ссылки», то столкнетесь с небольшими трудностями. Так как значения вычисляются по формуле, которая «забита» в ячейке, то простое копирование CTRL+C и CTRL+V окажется некорректным (скопируется именно формула, а не числа) и вам потребуется использовать функцию «Специальная вставка». Пошагово это выглядит так (Рис. 11):

1. Выделяете значения, которые вам требуется скопировать мышкой.
2. Нажимаете CTRL+C.
3. Далее выбираете ячейку, начиная с которой вы планируете осуществить вставку.
4. Нажимаете правку кнопку мышки.
5. Выбираете «Специальная вставка».
6. Задаете «Вставить значения».

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

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

Сравнение значений в двух столбцах

Для понимания, совпадает ли продвигаемая и релевантная в выдаче страница (и ряда других задач), требуется использовать логическую функцию «ЕСЛИ». Требуется добавить колонку сравнения «Совпадает ли?» в таблицу и вставить в первую ячейку данной колонки функцию, следующей последовательностью действий: «Формулы», далее «Логические», далее «ЕСЛИ» (Рис. 12). Задать логическое выражение, скажем [@[ ПРОДВИГАЕМЫЙ URL]]=[@[ РЕЛЕВАНТНЫЙ В Я]]» и значения функции: «1» и «0». Чтобы ускорить процесс, можно сразу вставить в столбец функцию:

=ЕСЛИ([@[ ПРОДВИГАЕМЫЙ URL]]=[@[ РЕЛЕВАНТНЫЙ В Я]];1;0)

Рис. 12. Вызов функции логического «ЕСЛИ» в Excel для сравнения значений в двух столбцах.

После нажатия на кнопку «OK» столбец заполнится значениями «0» (если страницы не совпадают) и «1», если значения совпадают. Это позволит быстро найти все запросы, по которым релевантный и продвигаемый документ не совпадают, и начать анализ возможных причин данного поведения.

Использование формул: среднее значение и сумма значений в ячейках

Для вычисления среднего значения какого-либо параметра (скажем, средней позиции в Яндексе по всем запросам или средней частоты запросов), а также суммы значений (скажем, суммарная точная частота или суммарный бюджет на ссылки) требуется использовать математические функции. Наиболее популярные это: вычисление среднего, вычисление медианы, вычисление суммы значений в столбце.

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

Рис. 13. Выбор ячейки и вставка нужной математической функции ячейку.

После поиска нужной функции, требуется задать аргументы (значения с которыми будет работать функция) и нажать «OK». Если вы всё сделали верно, то значение будет вычислено и вставлено автоматически. Примеры вставки функций среднего значения (Рис. 14) и суммы значений (Рис. 15) представлены на иллюстрациях ниже.

Рис. 14. Вставка функции вычисления среднего значения ячеек для колонки «ЯНДЕКС».

Рис. 15. Вставка математической функции «Автосумма» для быстрого вычисления суммы значений в колонке.

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

• Поиск максимального и минимального значения в колонке.
• Использование логических операторов: «И», «ИЛИ», «ЕСЛИ», «НЕ».
• Работы с датой и временем, вывод текущей даты по календарю.
• Сумма, сумма значений с условием, медиана.

Задание формата ячеек

Для задания требуемого формата ячеек (числового, денежного, финансового, временного, процентного, текстового и т.д.) достаточно использовать функцию «Формат ячеек», предварительно выделив интересующую область форматирования и нажав правую кнопку мыши (Рис. 16), во всплывающем модальном окне нажать «Формат ячеек…».

Рис. 16. Пример вызова функции «Форма ячеек» для выделенной области.

После указания нужного формата значений в ячейках, нажмите «OK» (Рис. 17) и выбранный формат будет применен в выделенной области. С помощью данной функции можно избавиться от принудительного превращения некоторых значений в формат даты в Excel и задать наиболее наглядный и подходящий формат для данных (скажем, выводить вместо 0,1 в†’ 10%, добавить разрядку групп разрядов у больших значений 340339493 в†’ 340 339 493, скрыть лишние знаки после запятой 5,100015 в†’ 5,1).

Рис. 17. Задание двух различных форматов (числовой и процентный) для двух соседних колонок.

Фиксация положения одной из ячеек в формуле

Если вам требуется зафиксировать положение (ячейку) для одной из переменных в формуле, то требуется просто заменить в самой формуле значение вида =F2 на значение =$F$2 (вставить знак доллара). После чего, вы сможете В«протягиватьВ» формулы для всей строки или столбца с фиксацией одной из переменный (ячеек). Пример использования:

Значение=$C$36+F13*2,2

* Стоит отметить, что функция условного форматирования быстро работает только с небольшими и средними таблицами и плохо справляется с большими массивами данных.

Добавить в избранное Вернуться в раздел

• Поисковое пространство Как работать с поисковым пространством и что входит в его оптимизацию? Работа с выдачей в целом.
• Полный чек-лист по SEO для СМИ 70+ пунктов чек-листа для роста органического трафика по событийным и устойчивым запросам, улучшения индексации и стимулирования UGC.
• Ключевые SEO-термины Какие термины необходимо знать каждому SEO-специалисту и интернет-маркетологу? Базовый набор SEO-терминов с определениями.
• Алгоритмы ранжирования Яндекса Какие алгоритмы были и сейчас действуют у поисковой системы Яндекс? От «Версия 7» 2007 года до «Владивостока» наших дней. Основные вехи развития поиска.
• Персонализация результатов выдачи Что такое персонализация / персонификация выдачи? Как её отключить и какие факторы анализируются поисковой системой?
• Сила многословной семантики Запросы какой длины являются наиболее часто задаваемыми в поисковую систему Яндекс? Как учитывать это при сборе семантики.
• Геозависимость поисковых запросов Как проверить является запрос геозависимым или нет запрос в Яндексе? Какой код у какого города/региона?
• Новые санкции поисковых систем Разбираем все имеющиеся санкции поисковых систем Яндекс и Google для сайтов и документов. Новые фильтры, их диагностика и снятие.
• Основы работы с WordStat Рассмотрим основные операторы и приёмы для грамотной работы со статистикой поисковых запросов WordStat от Яндекса.
• Как быстро вернуть сайт в ТОП? Подробное руководство для SEO-оптимизаторов для самостоятельной диагностики проблемы выпадения сайта. Разбираются самые частые причины.

‹ › Получайте полезные письма Присылаем экспертные исследования и кейсы по SEO и интернет-маркетингу, а также спецпредложения только для подписчиков!