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

Логические функции

Категория Логические содержит семь функций, в том числе функции ЕСЛИ и ЕСЛИОШИБКА. Использование логических функций делает формулы более гибкими, а использование функции ЕСЛИ наделяет формулу способностью «принимать решения». Благодаря этому функция ЕСЛИ стала самой используемой логической функцией. Функция ЕСЛИОШИБКА имеется в библиотеке встроенных функций только в Excel 2010 (2007). Об этом необходимо помнить, если ваши рабочие книги используются в разных версиях Excel.

Текстовые функции

Текстовые функции предназначены для обработки текста, например если создаете информационную базу про бесплатные программы скачать . Например, с помощью функций ПРОПНАЧ или ДЛСТР можно изменить регистр или определить длину текстовой строки. Используя текстовые функции, можно объединить несколько строк в одну или, наоборот, разделить одну текстовую строку на несколько строк. Например, формула =СЦЕПИТЬ(A1;A2) объединяет две текстовые строки, содержащиеся в ячейках A1 и A2, в одну.

Функции категории Проверка свойств и значений

Функции этой категории часто называют информационными. Функция ЯЧЕЙКА этой категории позволяет получить информацию о ячейке. Другие информационные функции проверяют выполнение какоголибо условия и, в зависимости от результата, возвращают значение ИСТИНА или ЛОЖЬ (или числовое значение). Например, с помощью функции ЕЧИСЛО можно проверить, данные какого типа содержит ячейка. Если в ячейке содержится число, функция ЕЧИСЛО возвращает логическое значение ИСТИНА, в противном случае функция возвращает логическое значение ЛОЖЬ.

Функции Дата и время

Функции, принадлежащие к этой категории, предназначены для работы со значениями даты и времени. По сути, эти функции работают с числовыми значениями, потому что дата и время в Excel являются числами, к которым применен один из числовых форматов даты и времени. С помощью функции этой категории можно вычислить количество рабочих дней между двумя датами (функция ЧИСТРАБДНИ), преобразовать дату в год (функция ГОД), месяц (функция МЕСЯЦ) или день недели (функция ДЕНЬНЕД) и т.п.

Математические функции

Математические функции позволяют выполнять простые и сложные вычисления. В категорию Математические входят тригонометрические функции, например SIN, COS, ACOS; функции, выполняющие арифметические действия, например СУММ, ПРОИЗВЕД, ЧАСТНОЕ; и многие другие функции. К этой же категории относятся функции, позволяющие работать с массивами значений или матрицами, - МУМНОЖ, МОПРЕД и МОБР, а также функции АГРЕГАТ и ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которые используются для получения итоговых значений (суммы, среднего арифметического, минимального или максимального значений и т.п.) в массивах данных или списках. Функция АГРЕГАТ доступна только в Excel 2010!

Microsoft Excel, программное обеспечение, электронные таблицы, содержит богатый набор формул и функций для вычислений и другие работы в рабочих листах. Функции и формулы являются двумя важными понятиями в Excel:

• Функция Excel представляет собой встроенную программу, которая выполняет определенную операцию на множестве заданных значений. Примерами функций Excel являются SUM, SUMPRODUCT , ВПР , СРЕДНЯЯ и т.д.
• Формулы Excel являются выражения, которые используют функции Excel , чтобы сделать расчеты на основе заданных параметров.

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

Понятие формулы и функции

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

• Ячейка A1 = 23
• Ячейка A2 = 57
• Мы хотим, чтобы сумма этих двух в ячейке B4

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

= SUM (A1, A2)

Когда вы будете писать эту формулу в ячейке B4 и нажмите клавишу ВВОД, сумму (23 + 57 = 80) появится в B4:

Итак, что же мы узнаем из этой простой формулы, например?

• Вы можете написать формулу Excel в любой ячейке или в строке формул
• Формула Excel начинается с равным подписать (=)
• После того, как равный знак приходит функцию Excel (например, SUM)
• После имени функции, вам нужно написать параметры в скобках
• При нажатии клавиши ввода, формула выполняет, и результат показан в ячейке

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

Типы функций Excel

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

1. Математика и тригонометрии: Эти функции выполняют математические и тригонометрические вычисления. Например: SUM, ПОЛ, LOG, РАУНД, SQRT, ASIN, ATAN и т.д.
2. Статистические функции: выполнять задачи, связанные с расчетами статистики: Например: МИРОВЫЕ, COUNT, TRED, ДИСПА и т.д.
3. Логические функции: дают результаты, основанные на логических конструкций, таких как IF, AND, OR, NOT и т.д.
4. Текстовые функции: выполнять задачи, связанные с манипуляций со строками. Например, CONCAT, CHAR, НИЖНИЙ, ВЕРХНИЙ, TRIM и т.д.
5. Функции даты и времени: выполнять вычисления значений даты и времени. Например, DATEDIFF, сейчас, сегодня, год и т.д.
6. Функции базы данных: дать результаты, доступ к базе данных

Для получения полного списка формул категорий вы можете увидеть веб - сайт Microsoft Office .

Вложенные функции

Excel позволяет использовать функции в вложенной моде. Функции Уплотненный Excel позволяют передавать результат одна функция в качестве параметра другой функции. Следовательно, Excel упрощает использование формулы. Сначала вы можете почувствовать, что вложенность функций делает формула усложняется -Но скоро вы поймете, что на самом деле вложенности делает вашу жизнь проще!

Ниже приведен пример вложенных функций:

= ЕСЛИ (СРЗНАЧ (F2: F5)> 50, SUM (G2: G5), 0)

Приведенная выше формула выглядит сложным, но это не так. Эта формула может быть легко читается слева направо. Он говорит, что если среднее значение диапазона ячеек (F2: F5) оказывается больше, чем 50, то вычислить сумму диапазона ячеек (G2: G5) в противном случае возвращают нулевое значение.

Здесь выход функции СРЗНАЧ становится входом для функции IF. Таким образом, мы говорим, что функция СРЗНАЧ вложен в IF функция.

Функциональные параметры

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

• Литеральные параметры: Когда вы предоставляете цифры или текстовую строку в качестве параметра. Например, формула = SUM (10,20) даст вам 30 , как результат.
• Ссылка на ячейку параметры: вместо буквального номера / текста, мы предоставляем адрес ячейки, которая содержит значение, на котором мы хотим работать функция. Например, = SUM (A1, A2)

Это был основной учебник о формулах и функциях Excel. Мы надеемся, что учащиеся смогут легко понять. Если у вас есть какие-либо вопросы по этому поводу, пожалуйста, не стесняйтесь обратиться к нам в разделе комментариев. Благодарим Вас за использование TechWelkin!

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

Объединение текстовых значений

Для объединения ячеек с текстовым значением можно использовать разные формулы, однако они имеют свои нюансы. Например, команда =СЦЕПИТЬ(D4;E4) успешно объединит две ячейки, равно как и более простая функция =D4&E4, однако никакого разделителя между словами добавлено не будет – они отобразятся слитно.

Избежать данного недочета можно добавляя пробелы, либо в конце текста каждой ячейки, что вряд ли можно назвать оптимальным решением, либо непосредственно в самой формуле, куда в любое место можно вставить набор символов в кавычках, в том числе и пробел. В нашем случае формула =СЦЕПИТЬ(D4;E4) получит вид =СЦЕПИТЬ(D4;” “;E4). Впрочем, если вы объединяете большое количество текстовых ячеек, то аналогичным образом пробел вручную придется прописывать после адреса каждой ячейки.

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

Другой типовой формулой для склеивания ячеек с текстом является команда ОБЪЕДИНИТЬ. По своему синтаксису она по умолчанию содержит два дополнительных параметра – сначала идет конкретный символ разделения, затем команда ИСТИНА или ЛОЖЬ (в первом случае пустые ячейки из указанного интервала будут игнорироваться, во втором – нет), и потом уже список или интервал ячеек. Между ячейками также можно использовать и обычные текстовые значения в кавычках. Например, формула =ОБЪЕДИНИТЬ(” “;ИСТИНА;D4:F4) склеит три ячейки, пропустив пустые, если таковые имеется, и добавит между словами по пробелу.

Применение: Данная опция часто используется для склеивания ФИО, когда отдельные составные части находятся в разных колонках и есть общая сводная колонка с полным именем человека.

Выполнение условия ИЛИ

Простой оператор ИЛИ определяет выполнение заданного в скобках условия и на выходе возвращает одно из значений ИСТИНА или ЛОЖЬ. В дальнейшем данная формула может использоваться в качестве составного элемента более сложных условий, когда в зависимости от того, что выдаст значение ИЛИ будет выполняться то или иное действие.

При этом сравниваться могут как численные показатели, применяя знаки >, <, =, так и поиск конкретного значения для ячейки, которое может быть текстовым. В частности, для поиска слова «Сдал» в конкретных ячейках будет использоваться формула =ИЛИ(D4= “Сдал”; E4= “Сдал”; F4= “Сдал”)

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

Поиск и использование значения

По горизонтали

Используя функцию ГПР мы можем задать поиск по конкретной строке таблицы, а на выходе получить значение из другой ячейки того же столбца (на одну или несколько строк ниже), что соответствует заданному условию. Причем поиск задается либо на точное значение (используется оператор ЛОЖЬ), либо на приблизительное (с оператором ИСТИНА), что позволяет использовать интервалы. Синтаксис =ГПР(искомое_значение; таблица; номер_строки; интервальный_просмотр)

Применение: Для расчета бонуса конкретному сотруднику можно задать интервалы, начиная от которых действует тот или иной процент от прибыли. Скажем, формула =ГПР(E5;$D$1:$G$2;2;ИСТИНА) будет искать в первой строке таблицы из интервала D1:G2 значение, приблизительно схожее со значением из ячейки E5, а результатом формулы станет вывод ячейки со второй строки соответствующего столбца.

По вертикали

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

То есть для формулы =ВПР(E4;$I$3:$J$6;2;ИСТИНА) будет сравниваться значение ячейки E4 с ячейками столбца I из таблицы интервала I3:J6, а значение будет выдаваться из соседней ячейки столбца J.

Выполнение условия ЕСЛИ

При использовании данной функции задается конкретное условие, а следом два результата – один для случаев, если условия выполнено, а другое – наоборот. Скажем для сравнения денежных средств из двух колонок может использоваться такая формула =ЕСЛИ(C2>B2; “Превышение бюджета”; “В пределах бюджета”).

Кроме того, в качестве условия может использоваться другая функция, например, условие ИЛИ и даже еще одно условие ЕСЛИ. При этом у воженных функций ЕСЛИ может быть от 3 до 64 возможных результатов). Как пример, =ЕСЛИ(D4=1; “ДА”;ЕСЛИ(D4=2; “Нет”; “Возможно”)).

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

Формула ранжирования

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

Для безопасности своих документов не лишним бывает установить на них персональный пароль.

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

Применение: Для таблицы с доходами по месяцам можно добавить столбец с ранжированием, а в дальнейшем по этому столбцу сделать сортировку.

Максимум из выбранных значений

Простая, но очень полезная формула МАКС выдает наибольшее значение из списка значений. Сам список может состоять как из ячеек и/или их диапазона, так и вручную введенных чисел. Всего максимальное значение можно искать среди списка из 255 чисел.

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

Минимум из выбранных значений

Аналогичным образом действует формула поиска минимальных значений. Идентичный синтаксис, обратный результат на выходе.

Среднее из выбранных значений

Для получения среднего арифметического из выбранного списка значений также есть своя формула. Однако написание ее в русском языке не столь очевидно. Звучит она как СРЗНАЧ, после чего в скобках указываются либо конкретные значения, либо ссылки на ячейки.

Сумма выбранных значений

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

Куда более интересным вариантом является суммирование ячеек, отвечающих конкретным критериям. Для этого используется оператор СУММЕСЛИ с аргументами диапазон, условие, диапазон суммирования.

Применение: Например, есть список школьников, согласившихся поехать на экскурсию. У каждого есть статус – оплатил он мероприятие или нет. Таким образом, в зависимости от содержимого столбца «Оплатил» значение из столбца «Стоимость» будет считаться или нет. =СУММЕСЛИ(E5:E9; “Да”; F5:F9)

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

В этой ста­тье Вла­ди­мир Шван­ский рас­ска­зы­вает о том, как эффек­тивно исполь­зо­вать Excel в нашей seo-работе.

Когда меня впер­вые посе­тила мысль напи­сать ста­тью о связке Excel + SEO , передо мной встала дилемма: о чём писать, чтобы не про­слыть «капи­та­ном Оче­вид­ность» и в то же время не углуб­ляться в нюансы спе­ци­фи­че­ских инстру­мен­тов, кото­рые мно­гие SEO-спе­ци­а­ли­сты не исполь­зуют в прин­ципе. Я решил пойти самым вер­ным путем: опи­сать методы реше­ния с помо­щью Excel тех SEO-задач, кото­рые я сам решаю еже­дневно.

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

Одни пыта­ются найти гото­вое узко­функ­ци­о­наль­ное реше­ние для своей про­блемы: «Помо­гите найти про­грамму для услов­ного сло­же­ния зна­че­ний строк», «Под­ска­жите про­грамму, чтобы выде­лить домен со списка» и т. д. Дру­гие пишут скрипты-реше­ния для всех про­блем, с кото­рыми стал­ки­ва­ются. Тре­тьи исполь­зуют доро­гие про­фес­си­о­наль­ные про­граммы (Deductor для фор­ми­ро­ва­ния сре­зов дан­ных, TextPipe для работы со стро­ками и т.п.) для довольно-таки базо­вых опе­ра­ций.

А ведь боль­шин­ство наших про­блем решает Microsoft Excel (как и Google SpreadSheet, и LibreOffice). Далее - яркие тому дока­за­тель­ства.

Функция № 1: ДЛСТР (англ LEN )

При­ме­ня­ется для опре­де­ле­ния длины тек­сто­вого содер­жи­мого ячейки (или тек­ста, задан­ного в фор­муле). При­ме­не­ний, как вы пони­ма­ете, масса. Напри­мер, изме­ре­ние длины анко­ров или мета-тегов на пред­мет пре­вы­ше­ния лимита (для при­мера возь­мём 70 зна­ков для title)

Доба­вим услов­ное фор­ма­ти­ро­ва­ние для нагляд­но­сти:

Строки с дли­ной меньше допу­сти­мого зна­че­ния выде­ляем одним цве­том, больше - дру­гим.

И полу­чаем:

Не очень худо­же­ственно, зато наглядно. Осо­бенно когда дело каса­ется несколь­ких сотен/тысяч мета-тегов. По такому же прин­ципу можно добав­лять новые пра­вила для пара­мет­ров description.

Функция № 2: СЖПРОБЕЛЫ (TRIM )

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

На прак­тике функ­ция полезна, когда при копи­ро­ва­нии всего мас­сива тек­ста появ­ля­ются про­белы до/после/между слов, созда­ю­щие про­блемы при даль­ней­шей обра­ботке.

Функции № 3: ПРОПИСН (UPPER ), СТРОЧН (LOWER )

Транс­фор­ми­рует содер­жи­мое строки (или задан­ного фраг­мента) в про­пис­ные или строч­ные буквы.

Функция № 4: ПРОПНАЧ (PROPER )

Пре­об­ра­зует пер­вые буквы каж­дого слова в строке в про­пис­ные.

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

Как известно, при про­верке основ­ными сер­ви­сами (как след­ствие - и про­грам­мами) все буквы запроса при­во­дятся в строч­ный вид. Итог: таб­лица на несколько тысяч строк вида ЗАПРОС + КОМПАНИЯ, где назва­ние ком­па­нии при­ве­дено с малень­кой буквы. Для даль­ней­шего исполь­зо­ва­ния было необ­хо­димо при­ве­сти всё в чело­ве­че­ский вид.

1. Рас­ще­пил мас­сив по 2-м столб­цам (запрос и назва­ние) с помо­щью функ­ции Дан­ные > Текст по столб­цам .
2. При­ме­нил функ­цию ПРОПНАЧ к столбцу с назва­ни­ями ком­па­ний.
3. Про­из­вёл сцепку с пер­вым столб­цом.

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

Функция № 5: СЦЕПИТЬ (текст1;текст2;текст3…) (англ. CONCATENATE )

По-моему, это наи­бо­лее полез­ная в прак­ти­че­ском SEO функ­ция. СЦЕПИТЬ поз­во­ляет объ­еди­нить содер­жи­мое отдель­ных тек­сто­вых бло­ков в одну строку. Это может быть как про­стая сцепка 2-х ячеек, так и более слож­ный вари­ант с под­став­ле­нием тек­сто­вых бло­ков непо­сред­ственно в фор­мулу.

При­мер: допу­стим, вам нужно отпра­вить ссылки с 500 не совсем каче­ствен­ных доме­нов в инстру­мент Disavow Links . Син­так­сис инстру­мента пред­по­ла­гает фор­мат вида domain:ваш_домен.com.ua. Что делать? Про­пи­сы­вать все 500 строк руками? Конечно же, нет. Всё, что вам нужно - это напи­сать:

СЦЕПИТЬ("domain:";адрес_ячейки)

А затем рас­тя­нуть фор­мулу на весь стол­бец.

Еще один при­мер: у вас есть стол­бец с URL и стол­бец с анко­рами. Нам нужно сфор­ми­ро­вать пол­но­цен­ную ссылку сле­ду­ю­щего вида:

Это несложно, однако тут есть свои нюансы. Заклю­ча­ются они в исполь­зо­ва­нии кавы­чек в тек­сто­вом блоке, пред­ше­ству­ю­щем ссылке (и в блоке, иду­щем сразу за ней). Фор­мула из преды­ду­щего при­мера не сра­бо­тает из-за пута­ницы в одинарных/двойных кавыч­ках.

Варианты решения

1. Несе­рьез­ный (отсут­ствует про­фес­си­о­наль­ный вызов)

Делаем два допол­ни­тель­ных столбца (или ячейки) с дан­ными (см. скрин­шот ниже):

Вме­сто пер­вого тек­сто­вого блока в фор­муле исполь­зуем ссылку на первую ячейку, вме­сто вто­рого - на вто­рую. В резуль­тате полу­чаем:

СЦЕПИТЬ(адрес_ячейки_с_началом;адрес_ячейки_с_URL;адрес_замыкающей ячейки;адрес_ячейки_анкора;"")

В слу­чае, если вы ука­зы­вали кон­крет­ные ячейки, а не столбцы, не забудьте задать абсо­лют­ные адреса:

2. Серьез­ные (при­сут­ствует про­фес­си­о­наль­ный вызов)

2.1 Исполь­зуем оди­нар­ные кавычки

Хотя син­так­сис ссы­лок с оди­нар­ными кавыч­ками и явля­ется валид­ным , его при­ме­не­ние не совсем кано­нично.

2.2 Исполь­зуем сим­вол кавы­чек (chr(34), символ(34))

У двой­ных кавы­чек есть циф­ро­вой код, а зна­чит, мы можем выве­сти их с помо­щью функ­ции chr (в рус­ской вер­сии «сим­вол»).

Функция № 6: СЧЁТЕСЛИ (диапазон;критерий) (англ. COUNTIF )

Под­счи­ты­вает коли­че­ство ячеек внутри диа­па­зона, удо­вле­тво­ря­ю­щих задан­ному кри­те­рию. Напри­мер, вы хотите поверх­ностно оце­нить раз­бав­лен­ность анкор­ного листа сайта URL ’ами. Чтобы никого не оби­жать, возь­мём не реаль­ный анкор лист , а выду­ман­ный. Напри­мер:

Чтобы при­ки­нуть про­цент URL-раз­бавки анкор-листа, посчи­таем все вхож­де­ния домена нашего сайта (а именно domen.ru) в анкоры. Для этого вве­дем фор­мулу:

СЧЁТЕСЛИ(A1:A9;"domen.ru")

Странно, пока­зы­вает ноль. Хоть вроде бы вхож­де­ние домена в анко­рах встре­ча­ется. Дело в том, что, в отли­чие от функ­ции ПОИСК (о ней - далее), кри­те­рий для СЧЁТЕСЛИ необ­хо­димо зада­вать явно и чётко. В нашем слу­чае в списке нет анкора domen.ru. Для ослаб­ле­ния кри­те­риев исполь­зу­ется либо звёз­дочка (любое коли­че­ство сим­во­лов), либо знаки вопроса (одна про­из­воль­ная буква). Для наших целей больше подой­дёт звёз­дочка (она же «асте­риск»).

СЧЁТЕСЛИ(A1:A9;"*domen.ru*")

Полу­чи­лось! Ну, и раз уж мы нашли этот пока­за­тель, заодно можем посчи­тать и отно­си­тель­ный вес анко­ров с вхож­де­нием URL по отно­ше­нию к общему кол-ву анко­ров.

СЧЁТЕСЛИ(A1:A9;"*domen.ru*")/СЧЁТЗ(A1:A9)

Вни­ма­тель­ный чита­тель, конечно, заме­тит, что функ­ция СЧЁТЗ счи­тает только непу­стые ячейки. В слу­чае выгрузки с сер­виса ана­лиза беклин­ков и боль­шого анкор-листа, полу­чен­ный нами резуль­тат будет некор­рект­ным. К сча­стью, в Excel также есть функ­ция под­счёта и пустых ячеек в диа­па­зоне, нося­щая кра­си­вое назва­ние СЧИТАТЬПУСТОТЫ (англ. COUNTA ).

Итого, наш финаль­ный вари­ант:

СЧЁТЕСЛИ(A1:A9;"*domen.ru*")/(СЧЁТЗ(A1:A9)+СЧИТАТЬПУСТОТЫ(A1:A9))

Функция № 7: СУМЕСЛИ (диапазон;критерий;диапазон_для_сложения) (англ. SUMIF )

Прин­цип такой же, как и в преды­ду­щем при­мере. Глав­ное отли­чие: два пара­метра с диа­па­зо­нами. Пер­вый - для при­ме­не­ния кри­те­рия, вто­рой - для при­ме­не­ния сло­же­ния зна­че­ний.

Функции № 8: ЛЕВСИМВ (текст;количество знаков) (англ. (LEFT ), ПРАВСИМВ (текст;количество знаков) (англ. RIGHT )

Воз­вра­щают задан­ное коли­че­ство зна­ков слева (или справа). Как пра­вило, исполь­зу­ются в усто­яв­шейся связке с функ­цией ПОИСК.

Функция № 9: ПОИСК (искомый фрагмент, просматриваемый текст,начальная позиция) (англ. SEARCH )

Воз­вра­щает номер вхож­де­ния иско­мой под­строки в общую строку. Напри­мер, при­ме­не­ние сле­ду­ю­щей фор­мулы воз­вра­тит «2», так как буква «п» вхо­дит в слово «опти­ми­за­ция » на вто­рой пози­ции:

ПОИСК ("п";"оптимизация")

Оче­видно, что само по себе зна­ние о пози­ции вхож­де­ния под­строки явля­ется мало­по­лез­ным даже в SEO 🙂

В моей прак­тике исполь­зо­ва­ние связки ЛЕВСИМ + ПОИСК (или ПРАВСИМВ + ПОИСК) встре­ча­лось доста­точно редко. Более того, пока я пишу опи­са­ния и при­меры этих функ­ций, в голове то и дело мель­кает афо­ризм:

У вас есть про­блема. Вы решили исполь­зо­вать регу­ляр­ные выра­же­ния, чтобы её решить. Теперь у вас две про­блемы.

Ведь, как известно, «нет ничего более бес­по­мощ­ного, без­от­вет­ствен­ного и испор­чен­ного, чем сеош­ник , при­бег­нув­ший к функ­циям поиска по под­строке».

Тем не менее, рас­смот­рим при­мер: у нас есть спи­сок URL-ов, и нам необ­хо­димо выде­лить из них непо­сред­ственно домен.

Будем сле­до­вать такой логике: нам надо «найти» точку непо­сред­ственно на слеше после домена, после этого вырвать кусок строки слева - с нуле­вой точки до най­ден­ной нами точки конца домена. Разо­бьем задачу на под­за­дачи.

Что ищем? Слеш. Где ищем? В ячейке с URL . С какой пози­ции ищем? Как мини­мум, с вось­мой, чтобы исклю­чить началь­ные слеши.

ПОИСК("/";ячейка_с_URL;8)

Выде­лим под­строку с доме­ном: с начала строки до точки вхож­де­ния слеша.

ЛЕВСИМВ(ячейка_URL;ПОИСК("/";ячейка_URL;8))

При опре­де­лен­ной сно­ровке с тек­сто­выми функ­ци­ями Excel можно тво­рить насто­я­щие чудеса.

Функция № 10: ВПР (искомое_значение, таблица, номер_столбца, тип_совпадения) (англ. VLOOKUP )

Кратко суть функ­ции опи­сать сложно, а в офи­ци­аль­ной справке при­ве­дено абсо­лютно непо­нят­ное объ­яс­не­ние. По сути, это «состы­ковка» зна­че­ний раз­ных таб­лиц на осно­ва­нии ана­лиза дан­ных в ячей­ках. Рас­смот­рим, как это рабо­тает на оче­ред­ном вымыш­лен­ном при­мере. Пусть у нас будет спи­сок ссы­ла­ю­щихся на наш сайт доме­нов, анко­ров их ссы­лок, ТИЦ и PR этих сай­тов.

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

ВПР(A2;F2:H11;2;ЛОЖЬ)

Пер­вый пара­метр, А2, опре­де­ляет, по какому зна­че­нию мы ищем сов­па­де­ния. В нашем слу­чае нам надо «состы­ко­вать» таб­лицу по отдель­ным доме­нам.

• Вто­рой пара­метр, F2 :H11 - это таб­лица с «эта­ло­нами». То есть та, где мы ищем.
• Тре­тий пара­метр, 2 - номер столбца в этой «эта­лон­ной» таб­лице, из кото­рого мы берем зна­че­ния. Слева-направо, в слу­чае с «ТИЦ», зна­че­ние «2».
• Чет­вёр­тый пара­метр (самое важ­ное), ЛОЖЬ - тип сов­па­де­ния. Здесь таится одна из самых боль­ших слож­но­стей этой функ­ции.

ЛОЖЬ озна­чает, что мы ищем точ­ное сов­па­де­ние содер­жи­мого ячейки в таб­лице с эта­ло­нами. ИСТИНА же озна­чает, что при отсут­ствии точ­ного сов­па­де­ния будет исполь­зо­вано бли­жай­шее к нему по убы­ва­нию. Также при исполь­зо­ва­нии ИСТИНЫ реко­мен­дую про­из­во­дить сор­ти­ровку столбца по воз­рас­та­нию, иначе резуль­тат может быть некор­рект­ным. Кстати, в том слу­чае, если в эта­лон­ной ячейке иско­мая ячейка встре­ча­ется несколько раз, будет исполь­зо­вано пер­вое зна­че­ние.

Рабо­тает! Рас­тя­нем фор­мулу на весь стол­бец и дело в шляпе? Нет. Мы задали адрес таб­лицы как отно­си­тель­ный, то есть при рас­тя­ги­ва­нии фор­мулы фокус с эта­лон­ной таб­лицы будет сме­щаться вниз на пустые ячейки. Чтобы это испра­вить, исполь­зуем:

ВПР(A2;$F$2:$H$11;2;ЛОЖЬ)

Рабо­тает. Теперь для сосед­него столбца:

Готово. А теперь перей­дём непо­сред­ственно к встро­ен­ному функ­ци­о­налу про­граммы.

Здесь без­услов­ными лиде­рами по полез­но­сти для SEO-спе­ци­а­ли­ста явля­ются 2 функ­ции: очистка от дуб­лей и раз­би­тие дан­ных по столб­цам по раз­де­ли­телю.

Функция № 11: Данные > Удаление дубликатов (Data > Remove Duplicates)

Поз­во­ляет очи­стить спи­сок от дуб­лей.

Допу­стим, у нас есть спи­сок доме­нов на 1200 строк. Как вари­ант можно попро­бо­вать найти и убрать дубли «руками», можно отсор­ти­ро­вать спи­сок по алфа­виту и уда­лить «руками» с уже намного мень­шими уси­ли­ями, исполь­зо­вать мак­рос для Excel, исполь­зо­вать софт по работе с клю­че­выми сло­вами (по умол­ча­нию уда­ляет дубли), исполь­зо­вать паб­лик-скрипты или онлайн-сер­висы. Понятно, что если коли­че­ство строк боль­шое (напри­мер, более 1 048 576 строк для Excel), вари­ант со спе­ци­а­ли­зи­ро­ван­ным соф­том или скрип­тами явля­ется един­ственно воз­мож­ным. Но если строк меньше гра­нич­ного мак­си­мума, Excel рабо­тает на ура.

Итак, на старте имеем 1266 доме­нов + aweb.ua:

Кли­каем на шапке столбца, чтобы выде­лить его цели­ком (как вари­ант - тянем выде­ле­ние руками или, клик­нув на пер­вой ячейке с содер­жи­мым, нажи­маем Ctrl+A). Весь наш спи­сок дол­жен быть выде­лен.

Пере­хо­дим во вкладку «Дан­ные» и нахо­дим пункт меню «Уда­лить дуб­ли­каты».

Кли­каем «Ок».

То же самое можно сде­лать и с помо­щью абсо­лютно бес­плат­ного инстру­мента Google Docs Spreadsheet. Также возь­мём спи­сок доме­нов, часть из кото­рых дуб­ли­ру­ется. Для уда­ле­ния дуб­лей исполь­зуем функ­цию:

UNIQUE (массив)

Так как мас­сив дан­ных у нас лежит в столбце A, в ячейку сосед­него столбца вста­вим фор­мулу:

UNIQUE(A1:A841)

Готово. В стол­бец B авто­ма­ти­че­ски зальётся мас­сив уни­каль­ных строк. Фор­мулу рас­тя­ги­вать не надо, всё реа­ли­зо­вано через функ­цию CONTINUE .

Функция № 12: Данные > Текст по столбцам (Data > Text to Columns)

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

Допу­стим, у нас есть мас­сив дан­ных с раз­де­ли­те­лем вида «пайп» (вер­ти­каль­ная черта).

Нахо­дим во вкладке «Дан­ные» пункт «Текст по столб­цам». Кли­каем, пред­ва­ри­тельно выде­лив нуж­ный нам мас­сив дан­ных. Появ­ля­ется «Мастер рас­пре­де­ле­ния тек­стов по столб­цам»

На сле­ду­ю­щем шаге не забудьте выста­вить зна­че­ние в поле «Поме­стить в», иначе стол­бец с дан­ными пере­за­пи­шется (хотя в 99% слу­чаев именно это нам и нужно).

Готово! Несмотря на всю кажу­щу­юся про­стоту, раз­бивка на столбцы по задан­ному раз­де­ли­телю явля­ется одной из наи­бо­лее часто исполь­зу­е­мых и полез­ных SEO-функ­ций про­граммы.

На этом всё. В даль­ней­шем я пла­ни­рую напи­сать боль­шую ста­тью по исполь­зо­ва­нию свод­ных таб­лиц Excel в SEO - тема не менее инте­рес­ная и объ­ем­ная, чем затро­ну­тая сего­дня. А пока наде­юсь, что дан­ный мате­риал спа­сёт не один деся­ток веб-масте­ров от бес­смыс­лен­ной траты вре­мени на рутин­ные задачи и не только откроет для вас дру­же­ствен­ный мир Excel, но и вдох­но­вит на даль­ней­шие поиски реше­ний по авто­ма­ти­за­ции работы.

Использование функций в Excel

1.Функции в Excel. Мастер функций 2

2.Математические функции. 4

2.1.Задание для самостоятельной работы 1. 4

2.2.Задание для самостоятельной работы 2. 5

3.Статистические функции. 6

3.1.Задание для самостоятельной работы 3. 6

4.Логические функции. 7

4.1. Описание некоторых логических функций. Примеры. 7

4.1.1.Сложные условия. 9

4.2. Задание для самостоятельной работы 4 14

5.1.Задание для самостоятельной работы 5. 15

5.2.Задание для самостоятельной работы 6. 15

6.Печать рабочего листа Excel. 16

7.Вопросы к защите лабораторной работы. 16

1.Функции в Excel. Мастер функций

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

математические;

финансовые;

статистические;

даты и времени;

логические;

работа с базой данных;

проверки свойств и значений; ... и другие.

Любая функция имеет вид:

ИМЯ (СПИСОК АРГУМЕНТОВ)

ИМЯ- это фиксированный набор символов, выбираемый из списка функций;

СПИСОК АРГУМЕНТОВ (или только один аргумент)- это величины, над которыми функция выполняет операции. Аргументами функции могут быть адреса ячеек, константы, формулы, а также другие функции. В случае, когда аргументом является другая функция, мы имеем дело со вложенной функцией.

Например, запись СУММ(С7:C10;D7:D10) содержит функцию СУММ с двумя аргументами, каждый из которых является диапазоном ячеек, а запись КОРЕНЬ(ABS(А2)) содержит функцию КОРЕНЬ, аргументом которой является функция ABC, у которой в свою очередь аргументом является адрес ячейки А2.

Пакет Excel предоставляет удобный инструмент ввода функций- Мастер функций. Инструмент Мастер функций можно вызвать:

командой Вставить функцию во вкладке Формулы из группы Библиотека функций (Рис.1)

Рис.1 Команда Вставить функцию во вкладке Формулы

командой Вставить функцию в строке формул (Рис.2).

Рис.2 Команда Вставить функцию в строке формул

После вызова Мастера функций появляется диалоговое окно (Рис.3):

Рис.3 Диалоговое окно Мастера функций

В этом окне нужно выбрать категорию функции и в списке ниже необходимую функцию.

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

Рис.4 Окно математической функции КОРЕНЬ

Когда в качестве аргумента функции используется также функция, то функцию аргумента (т.е. вложенную, или внутреннюю, функцию) следует выбирать, раскрывая список функций слева от строки формул (Рис.5).

Рис.5 Выбор вложенной (внутренней) функции

Если в появившемся списке отсутствует требуемая функция, то следует активизировать строку «Другие функции…» и работать далее с диалоговым окном Мастер функций , как описано выше.

После ввода аргументов вложенной функции не следует щелкать на кнопке ОК, а нужно активизировать (щелкнуть мышью) имя соответствующей внешней функции в поле ввода строки формул. Т.е. нужно перейти на окно Мастера функций соответствующей внешней функции. Так следует повторять для всех вложенных функций. В формулах может быть до 64 уровней вложения функций.