Как пользоваться в экселе формулами: Все формулы эксель

Содержание

7 формул для анализа данных

Мы перевели для вас статью Нила Пателя. Читайте и применяйте.

Для многих маркетологов Excel – жизненно необходимая программа, их хлеб и соль, так сказать. Неважно, для чего она используется – для анализа поисковых данных или подсчета данных по продажам – в какой-то момент вы все равно откроете документ Excel.

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

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

Однажды овладев этими навыками, вам больше не придется возвращаться к этому вновь и вновь.

Зачем учиться пользоваться Excel для SEO

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

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

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

Например, SEOTools для Excel идет с уже встроенными функциями специально для маркетологов.

Такие утилиты — просто находка и необходимость для тех, кому приходится пользоваться Excel без особой любви к ней.

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

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

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

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

1. Используйте формулу «=IF» для разбивки ключевых слов по категориям

Находясь в поиске ключевых слов, учишься спокойно относиться к длинным спискам. Привыкаешь к ним, так сказать. Даже пользуясь простой утилитой типа Google’s Keyword Planner, все равно получишь длинный список.

Другие утилиты вроде Ahrefs или Moz могут выдать вам списки с тысячами ключевых слов, показателей объема и данными о конкуренции. А это очень, очень много данных. Поместив эти данные в таблицу, мы сможете организовать их по рядам и колонкам. Но ведь нужно еще и как-то осмыслить все это.

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

Например, если использовать более одного семантического ядра в Keyword Planner,

то вам выдают колонку «Ad Group», куда можно экспортировать список запросов, которые отделят семантические ядра от остальных вариантов ключевых слов.

Предположим, я хочу разбить этот список только на ключевые запросы со словами «утилиты» в них. Если делать это вручную, придется продираться сквозь более чем 700 рядов. А мне не очень-то хочется это делать. Так что вместо этого я собираюсь использовать формулу Excel =IF (=ЕСЛИ), которая сделает все за меня.

Первым делом нужно удалить колонку «Ad Group», потому что я хочу искать среди всех запросов.

А потом я добавлю колонку «Category» (“Категория”) рядом с поисковым объёмом.

В первой свободной клетке я пишу слово «Tools» и затем использую формулу:

=if(isnumber(search(“tools,A12)),”Tools”)

Когда ключевое слово «tools» найдется в первой колонке (мой список ключевых слов), то поместится в категорию «Tools».

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

Если хотите только ключевые запросы со словом «local», можно создать категорию «Local». Удивительно нужная штука для организации всех ваших ключевых запросов для быстрого поиска.

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

У Moz есть хороший пример того, как можно сделать это для всех данных во всех таблицах.

Чтобы сделать это для всех рядов и колонок, используйте мульти-клеточную формулу ARRAY (МАССИВ).

Это освободит вас от необходимости вставлять одну и ту же сложную формулу в каждую клетку таблицы. И даже если у вас не 700+ клеток, с которыми нужно иметь дело, формула все равно пригодится.

2. Создавайте сводные таблицы для обнаружения выброса данных

Анализ данных в таблицах может быть довольно хаотичным, если данные не организованы.

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

Чтобы создать такую, выделите клетку таблицы и нажмите Insert > Pivot Table (Вставить > Сводная Таблица).

Затем вы увидите диалоговое окно, в котором вас попросят выбрать данные для анализа и куда поместить сводную таблицу.

Ваша таблица будет автоматически помещена в выделенную клетку, но также можно создать новую таблицу или поместить ее куда-то еще.

Когда нажмете «OK», то увидите поле, соответствующее вашим колонкам.

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

Используйте эти таблицы для таких вещей как организация ссылок или URL, группировка внутренних ссылок по DA/PA, поиск ключевых слов в домене или создание всплывающих окон для определенных колонок.

Вот пример функциональной сводной таблицы:

Такая таблица не просто организует ваши данные в более читабельном виде, но и дает несколько опций для категоризации. В примере выше можно сразу заметить большие или маленькие цифровые значения CPA («cost-per-action», цена-за-действие) для определенных ключевых слов.

Это делается при помощи условного форматирования в рамках сводной таблицы (вы можете заметить цветное кодирование). Сводные таблицы типа этой могут оказаться полезными, если вы используете ключевые слова для транслирования своей стратегии Adwords, или если хотите быстро обнаружить высоко- и низкочастотные запросы и CPC («cost-per-click», цена-за-клик) для конкретных ключевых слов.

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

И выглядит такая таблица тоже неплохо.

3. Конвертируйте показатели объема при помощи SUBSTITUTE (ЗАМЕНИТЬ)

Существует множество разных способов сортировки данных в Excel. Тем не менее, ключ к продуктивности заключается в том, чтобы найти верный. Когда вы загружаете данные из утилиты для ключевых слов (типа Keyword Planner), CSV-файлы не всегда выглядят хорошо. Например, мои объемы ключевых слов выглядят так:

Справедливости ради, они выглядят так же при использовании Keyword Planner.

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

Excel не может отсортировать мой список, основываясь на понятиях типа «10К» или «1М». Он не дружит с К и М. Ему нужны реальные цифры. К счастью, Excel имеет нечто вроде быстрой формулы, которая поможет вам отсортировать объемы должным образом.

Во-первых, замените К и М и преобразуйте их в «000» или «000000». Создайте новую колонку с названием «От меньшего к большему» или типа того.

Выберите клетку в новой колонке и вставьте =SUBSTITUTE (=ЗАМЕНИТЬ):

Ваша формула должна выглядеть примерно так:

=SUBSTITUTE(C2,”K”,”000”)

Номер клетки изменится согласно ряду, который вы конвертируете.

Вот как это выглядит после вставки формулы:

И окончательные результаты:

Можно также заменить М на «000000» при помощи той же формулы. Выглядеть будет вот так:

=SUBSTITUTE(C2,”M”,”000000”)

Так вы разберетесь со всеми К и М в таблице.

А еще можно выполнить оба действия одновременно (если у вас диапазон от 100К до 1М, например), используя следующую формулу (изменяя номер клетки):

=SUBSTITUTE(SUBSTITUTE(C2,”K”,”000”), “M”, “000000”)

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

Формула для минимума:

Максимума:

И среднего показателя:

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

Вы сможете быстро переключаться между колонками «от большего к меньшему» и «от меньшего к большему» без необходимости долго сортировать или переставлять вручную.

Эти формулы призваны экономить ваше время и энергию, в конце концов.

4. Извлекайте определенные данные при помощи «REGEXTRACT» (“ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ ТАБЛИЦЫ”)

Конечно, ключевые слова – это не только данные в таблицах SEO.

Иногда вам придется сортировать URL, названия доменов, заголовки блогов или email адреса.

Возможно, вам потребуется найти конкретные посты в блоге или лэндинговые страницы с HTTP versus или HTTPS.

Всегда можно использовать панель поиска Excel и найти их по отдельности, но если у вас длинный список URL, это займет время. :\/\n]+)”)),“”)

Громоздко, но работает.

Вот что получаем в нашем примере:

И в конечном итоге:

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

Если вы все еще не уверены в процессе или в том, как использовать синтаксис вместе с REGEXTRACT, у Ahrefs есть таблица, которая показывает, как работает эта формула.

Это просто палочка-выручалочка для тех, кто не испытывает особой нежности к Excel формулам.

5. Форматируйте теги названий с помощью формулы «PROPER» (“ПРОПНАЧ”)

Еще одно утомительное занятие, с которым вы можете столкнуться при работе с таблицами для SEO, это переформатирование данных.

Возможно, теги заголовков или ключевые слова были импортированы в нижнем регистре, а вам надо, чтобы они были записаны заглавными буквами.

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

Или, по крайней мере, я надеюсь, что вы не собираетесь это делать, потому что можно легко сменить строчные буквы на заглавные одним скопом, используя формулу PROPER, которая выглядит вот так:

=PROPER(C2)

Просто, не так ли?

Создайте новую колонку и вставьте формулу туда. Вот так:

И конечный результат:

Если у вас много тегов, которые нужно записать заглавными буквами, это тоже сработает.

Можно искать отдельные теги, используя формулу HLOOKUP (ГПР) (вернемся к ней позже).

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

Если хотите изменить ключевое слово или тэн названия в нижнем регистре на верхний (например, акроним), воспользуйтесь формулой UPPER (ПРОПИСН). Вот так:

Вы можете мне не верить, но формула LOWER (СТРОЧН) совершит обратное действие 🙂

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

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

Так что вот вам быстрый и легкий способ решить этот вопрос.

6. Совершайте поиск в больших таблицах с помощью «VLOOKUP» (“ВПР”)

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

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

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

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

Это VLOOKUP (ВПР), которая выглядит так:

=VLOOKUP(A4;’Lookup Table’!A2:D1000;4;FALSE)

Если вы хотите найти специальное ключевое слово среди двух листов одной таблицы, то процесс таков:

Эта формула может показаться сложной на первый взгляд, но становится проще, когда вы ее освоите.

Первая секция – это название того, что вы ищете. Поставьте его в кавычки:

Затем добавьте наименование колонки.

После этого добавьте порядковый номер колонки. Первая колонка – под цифрой 1 и так далее.

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

Можно также использовать FALSE, если точное совпадение необязательно.

Обратите внимание на то, что VLOOKUP (ВПР) работает только если необходимые вам данные находятся в первой колонке (крайняя левая), а данные расположены в алфавитном порядке.

Так что вам возможно придётся форматировать колонки перед поиском VLOOKUP (ВПР). Но формула все равно очень пригодится.

Ее также можно использовать для поиска цены продукта или специальной категории, как, например, здесь:

Если нужно искать по рядам, используйте формулу HLOOKUP (ГПР).

V (В) для вертикального поиска и H (Г) для горизонтального.

Пример формулы HLOOKUP:

=HLOOKUP(D3;’Lookup Table’!A1:D10;2;FALSE)

Тут вы найдете пример этой функции:

Оба пути быстро приведут вас к тому, что вы ищете, особенно если найти нужно среди множества рядов, колонок или страниц в документе.

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

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

Немного тренировки, и вы освоите их на «отлично». Именно поэтому они во многом превосходят традиционное поисковое окно.

7. Быстро обнаруживайте дубликаты, используя «COUNTIF»(“СЧЁТЕСЛИ”)

Не все данные в таблице будут верными.

Вам понадобится быстрый и легкий способ отсортировать их, выявить реплики и заняться своими делами.

Формула Excel в данном случае – COUNTIF. И выглядит она так:

=COUNTIF(A:A,A2)

Допустим, у вас есть список элементов в первой колонке (колонка А), которые вы хотите проверить на дубликаты.

Во-первых, создайте новую колонку и вставьте формулу с номером клетки из колонки А, вот так:

Затем перетащите ее и скопируйте в другие клетки. После этого увидите вот что:

TRUE означает, что совпадения есть, а FALSE – что стопроцентных дубликатов нет. Эта формула находит только точные совпадения.

В примере выше вы видите, что даже если слова «seo strategy» показаны несколько раз (например, seo marketing strategy), то формула не сочтет это дубликатом.

Если вы просто хотите выделить эти дубликаты, можно сделать это при помощи условного форматирования.

Нажмите Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values

Можно выбрать цвет для дубликатов, если хотите просто выделить их, а не удалить.

Если хотите их удалить, нажмите Data > Remove Duplicates (Данные > Удалить Дубликаты), а затем под колонками выделите или снимите выделение с тех, в которых хотите удалить дубликаты.

Весь процесс занимает пару минут, но при этом действительно помогает почистить данные быстро.

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

И формула COUNTIF (СЧЁТЕСЛИ) – одна из них.

Заключение

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

Но если вы используете таблицы – особенно Excel таблицы – для SEO или маркетингового исследования, то вам просто необходимо овладеть некоторыми из них.

В особенности, присмотритесь к тем, которые помогают при поиске или очистке данных и делают это быстро, как VLOOKUP (ВПР) или COUNTIF (СЧЁТТЕСЛИ).

Умение конвертировать показатели объема с помощью SUBSTITUTE (ЗАМЕНИТЬ) просто жизненно необходимо для поиска ключевых слов.

А научившись создавать более сложные (но нужные) элементы типа сводных таблиц, вы в итоге сэкономите себе кучу времени и энергии. SEO и так непростая штука.

Сбросьте с себя часть бремени, изучив несколько приемов пользования Excel.

Если вы хотите научиться оптимизировать сайты и стать суперменом-сеошником, то можем предложить курс «SEO-оптимизация: продвижение сайтов в поисковых системах». После курсов проведете аудит сайта и создадите стратегию продвижения. Научитесь анализировать конкурентов, сформируете семантическое ядро. Прогнозируя результаты продвижения, сможете оптимизировать бюджет. Привлекательно? Записывайтесь!

ЗАПИСАТЬСЯ

Мнение редакции может не совпадать с мнением автора. Если у вас есть, что дополнить — будем рады вашим комментариям. Если вы хотите написать статью с вашей точкой зрения — прочитайте правила публикации на блоге WebPromoExperts.

Что такое формулы в Excel и как их использовать?

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

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

Вот посмотрите на основы формул Microsoft Excel.

Информация в этой статье относится к версиям Excel 2019, 2016 и 2013, а также к Excel для Office 365 и Excel для Mac.

Обзор формул

Формулы выполняют вычисления в Excel. Они всегда начинаются со знака равенства (=), где вы хотите получить ответ или результаты.

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

Значения

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

Константы

Константа — это значение, которое не изменяется и не рассчитывается. Хотя константы могут быть общеизвестными, такими как Pi (Π), отношение длины окружности к ее диаметру, они также могут иметь любое значение, например налоговую ставку или конкретную дату, которая изменяется нечасто.

Сотовые ссылки

Ссылки на ячейки, такие как A1 или h44, указывают расположение данных на листе. Ссылка на ячейку состоит из буквы столбца и номера строки, которые пересекаются в местоположении ячейки. При перечислении ссылки на ячейку буква столбца всегда отображается первой, например, A1, F26 или W345.

Вы вводите несколько последовательных ссылок на ячейки в формулу в виде диапазона , который указывает только начальную и конечную точки. Например, ссылки A1, A2, A3 могут быть записаны как диапазон A1: A3.

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

функции

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

операторы

Операторы — это символы или знаки, используемые в формуле для определения отношения между двумя или более ссылками на ячейки или значениями. Например, знак плюс (+) является арифметическим оператором, используемым в формулах, таких как = A2 + A3. Другие арифметические операторы включают знак минус (-1) для вычитания, косую черту (/) для деления и звездочку (*) для умножения.

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

В дополнение к арифметическим операторам операторы сравнения выполняют сравнение двух значений в формуле. Результат этого сравнения — ИСТИНА или ЛОЖЬ. Операторы сравнения включают знак равенства (=), меньше ( < ), меньше или равно ( <= ), больше ( > ), больше или равно ( > = ) и не равно ( <> ) ,

Функции AND и OR являются примерами формул, в которых используются операторы сравнения.

Наконец, амперсанд (&) является оператором конкатенации, объединяющим данные или несколько диапазонов данных в формуле. Вот пример:

{= ИНДЕКС (D6: F11, МАТЧ (D3 и E3, D6: D11 и E6: E11, 0), 3)}

Оператор конкатенации используется для объединения нескольких диапазонов данных в формуле поиска с использованием функций Excel INDEX и MATCH .

Как создать простую формулу

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

Выберите ячейку и введите знак равенства (=).
Выберите ячейку или введите ее адрес в выбранной ячейке.
Введите оператора. В этом примере мы используем знак минус (-).
Выберите следующую ячейку или введите ее адрес в выбранной ячейке.
Нажмите Enter или Return . Вы увидите результат расчета в ячейке с формулой.

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

Как использовать встроенную функцию с формулой

Выберите пустую ячейку.
Введите знак равенства (=), а затем введите функцию. В этом примере мы используем = SUM, чтобы увидеть общий объем продаж.
Введите открывающую скобку и затем выберите диапазон ячеек. Затем введите закрывающую скобку.
Нажмите Enter или Return, чтобы увидеть ваш результат.

Как фильтровать с помощью формулы в Excel

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

Синтаксис

=ЕСЛИОШИБКА(ИНДЕКС(диапазон списка,МАЛЕНЬКИЙ(ЕСЛИ(критерии=диапазон критериев,СТРОКА(диапазон критериев),»»),СТРОКА()-СТРОКА(возврат ячейки заголовка))),»»)

Шаги

Выберите диапазон ячеек, которые будут заполнены отфильтрованными значениями
Начните формулу с = ЕСЛИОШИБКА (функция для возврата пустой строки при возникновении ошибки)
Продолжить с ИНДЕКС(
Выберите или введите ссылку на диапазон, содержащий исходный список B:B,
Продолжайте с функцией SMALL( , которая предоставляет индексы строк ячеек
Затем используйте IF( , чтобы вернуть массив, содержащий числа и пустые строки
Используйте уравнение для фильтрации $E$3=C:C, ячейка критерия должна быть абсолютной ссылкой
Продолжить с ,СТРОКА(C:C),»»), что обеспечит ИСТИНА/ЛОЖЬ условия для функции ЕСЛИ
Введите ROW()-ROW(E5) , чтобы сгенерировать возрастающее число для массива из функции ЕСЛИ
Введите )),»») и нажмите Ctrl + Shift + Enter для завершения массива формула

Как

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

Наш пример возвращает значения из столбца B путем поиска значения ячейки E3 в столбце C. Это условие приводит к логическому тесту,

$E$3=C:C

Этот тест возвращает массив логических значений (ИСТИНА и ЛОЖЬ). Например, если значение ячейки E3 равно «I», логическая проверка возвращает массив, как показано ниже:

{ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА;…….; ЛОЖЬ}

. Эти логические значения становятся логические тестовые значения для функции IF . Функция IF предоставляет номера строк ячеек, соответствующих критериям, и возвращает пустые строки для остальных.

ЕСЛИ($E$3=C:C,СТРОКА(C:C),»»)

Здесь функция IF возвращает массив. На этот раз массив номеров строк и пустых строк.

{«»;»»;3;4;5;…….;»»}

Следующий шаг — сортировка номеров строк в нашем новом массиве. Функция SMALL может возвращать n наименьшее число из массива. Также обратите внимание, что Excel оценивает строковые значения как почти бесконечно большие числа, что делает любое другое число маленьким по сравнению с ним. Вот почему SMALL 9Функция 0017 используется вместо функции LARGE . Чтобы присвоить значение «n» функции SMALL , мы снова используем функцию ROW с одной ячейкой, которая должна быть на одну ячейку выше первой строки, чтобы возвращать числа от 1, и используем относительную ссылку, чтобы увеличить ее строку. количество. Как результат; Формула SMALL(IF($E$3=C:C,ROW(C:C),»»),ROW()-ROW(E5)) возвращает значение индекса строки, которое будет использоваться функцией INDEX для возврата значение из непустой ячейки.

{1}

Функция ИНДЕКС выбирает ссылку для возвращаемых значений, а функция ЕСЛИОШИБКА включает вложенную формулу, чтобы избежать ошибок.

=ЕСЛИОШИБКА(ИНДЕКС(B:B,МАЛЕНЬКИЙ(ЕСЛИ($E$3=C:C,СТРОКА(C:C),»»),СТРОКА()-СТРОКА(E5))),»»)

Наконец, нажмите комбинацию Ctrl + Shift + Enter вместо того, чтобы просто нажать клавишу Enter, чтобы ввести формулу в виде формулы массива, и все готово!

Что такое формулы Excel и зачем их использовать?

Боятся формул в Excel? Не будь! Это 5-минутное чтение поможет вам раскрыть весь потенциал Excel.

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

Что такое формулы Excel?

Формулы — это сердце и душа Excel. Формулы — это то, что делает Excel таким мощным и практичным. При этом они довольно просты: формула – это выражение, которое вычисляет значение ячейки.

Противоположное формуле значение является предопределенным. Например, цифра 1 или слово «баскетбол».

Формула, с другой стороны, является результатом функции или простой математической операции. Эти функции могут варьироваться от очень простых, таких как «1 + 1», до более сложных, таких как «ВПР (E2, A: C, False)».

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

Давайте введем вашу первую формулу

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

=2+2

Нажмите Enter и посмотрите, что произойдет.

Поздравляем! Вы только что сделали формулу.

При использовании формулы Excel важно помнить, что в отличие от того, когда вы пишете что-то на бумаге, где вы пишете знак равенства последним (2+2=), в Excel вы всегда начинаете свои формулы, записывая знак равенства первым.

В электронной таблице вы заметите, что дано вычисленное значение формулы. Но когда вы выберете его, вы увидите формулу вверху в строке формул.

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

Дополнительные базовые функции Excel для начинающих

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

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

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

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

Что такое ссылки на ячейки и как их использовать?

Возможно, вы заметили, что результаты автосуммы меняются в зависимости от ячеек над или рядом с ней. Это потому, что он использует ссылки на ячейки.

Ссылка на ячейку — это расположение поля на листе, поэтому, скажем, вы нажимаете B1, которая является ссылкой на ячейку. Столбец обозначается буквой, а строка — числом. Проще говоря, ссылка на ячейку представляет собой комбинацию столбца и числа.

Значение B — это столбец, 1 — это строка, что означает, что ссылка на ячейку — B1.

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