Auditportal.ru

Аудит Портал
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как искать или находить сразу несколько значений в Excel?

Суммирование ячеек в Excel по условию

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

  1. 1 Простое сложение в Excel
  2. 2 Суммирование ячеек по условию
  3. 3 Функция «SUMMESLI»
  4. 4 Функция «SUMMESLIMN»
  5. 5 Sum

Простое сложение в Excel

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

Функция СУММ в Excel

Суммирование ячеек по условию

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

  • Range — обязательный аргумент, представляющий собой массив, в котором проверяется заданное условие;
  • Criterion — еще один обязательный аргумент, представляющий собой условие для выбора значений в ячейках. Если значение равно заданному числу, оно должно быть введено без кавычек, в противном случае кавычки обязательны: например, если значение больше числа 5, оно должно быть введено как «>5». Текстовые значения также работают: если вы хотите суммировать доходы продавца Иванова в таблице, то введите условие «Иванов»;
  • Диапазон сумм — это массив значений, которые нужно суммировать.

Чтобы облегчить работу с вычислительными операциями в электронных таблицах Excel, существуют две функции, которые складывают ячейки при выполнении условий: «СУММЕСЛИ» и «СУММЕСЛИН». Давайте рассмотрим каждый из них более подробно.

Читайте так же:
Как использовать макрос для вставки строки под таблицей в Excel?

Функция «СУММЕСЛИ»

Когда вы хотите суммировать значения ячеек с одинаковым условием, подходит S UMMESLY. Чтобы применить его в действии, следуйте инструкциям:

    Выбор ячейки для вывода результата СУММЕСЛИ

    Выбор функции СУММЕСЛИ

    Аргументы функции СУММЕСЛИ

    Критерий функции СУММЕСЛИ

    Диапазон суммирования СУММЕСЛИ

    Результат функции СУММЕСЛИ

    В выделенной ячейке будет показан результат сложения, а в верхней строке — функция. Можно вычислить значение без вызова окна «Аргументы функции», если все сделать вручную. Это должно выглядеть так: =СУММЕСЛИ(X; «Условие»; Y) , где X представляет диапазон, а Y — диапазон суммирования. Нет необходимости вводить значения, вы можете просто выбрать их мышью в таблице. Запятая разделяет все элементы в функции.

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

    Функция «СУММЕСЛИМН»

    «SUMMESLIMN» позволяет вычислить результат суммы, используя различные условия. Эта функция предоставляет больше возможностей для определения параметров математического расчета. Для расчета можно использовать несколько критериев суммы, а также определить до 127 условий. Используя эту таблицу в качестве примера, попробуйте найти, сколько килограммов яблок купил Евдокимов, потому что он также купил бананы.

    Чтобы суммировать клетки с более чем одним условием, следуйте следующим инструкциям

    1. Выделите пустую ячейку, в которой будет отображаться конечный результат, затем нажмите на кнопку fx, которая находится рядом со строкой функций.
    2. В разделе «Математические» в окне «Вставка функций» нажмите «СУММЕСЛИМН», затем подтвердите выбор, нажав на кнопку «ОК».

    Вставка функции СУММЕСЛИМН в ячейку

    Диапазон суммирования СУММЕСЛИМН

    Диапазон первого условия СУММЕСЛИМН

    Первое условие СУММЕСЛИМН

    Диапазон и второе условие СУММЕСЛИМН

    Можно написать функцию «SUMMESLYMN» вручную в строке формул, но это сложно, так как используется слишком много условий. В этой таблице результат равен 8, а функция отображается полностью вверху.

    СУММЕСЛИМН в строке формул

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

    Поиск в экселе — как искать и применение функции с примерами

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

    Поиск

    Я начну с менее популярной функции — поиска. Этот инструмент позволяет найти позицию искомой информации в тексте, выраженную в виде числа. Кроме того, можно искать не только отдельные символы, но и целые комбинации букв. Чтобы включить поиск, необходимо записать в строке формул одноименную функцию, за которой следует знак равенства. Синтаксис выглядит следующим образом:

    Поиск в экселе 1

    1. Первый блок используется для записи информации для поиска.
    2. Вторая часть функции позволяет определить диапазон поиска текста.
    3. Третий аргумент является необязательным. Его использование оправдано, если известна начальная точка поиска внутри ячейки.

    Рассмотрим пример: вам нужно найти из списка фрукты, которые начинаются на букву А.

    1. Составьте список на листе

    Поиск в экселе 2

    1. В следующем столбце введите =POISE(«a»;$B$4:$B$11). Не забывайте использовать инвертированные запятые при использовании текста в качестве аргумента.

    Поиск в экселе 3

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

    Поиск в экселе 4

    Затем полученные результаты можно использовать для приведения в более удобную для использования форму.

    Позиции с единицами указывают, в каком из рядов находится фрукт, начинающийся на букву a. Как вы можете видеть, оставшиеся числа также указывают на расположение искомой буквы в оставшихся позициях диапазона. Однако одна из ячеек содержит ошибку! Эта проблема возникает в двух случаях при использовании функции ПОИСК:

    1. Ячейка ноль
    2. Блок не содержит искомой информации.

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

    Найти

    Используйте комбинацию клавиш Ctrl+F, чтобы быстро открыть окно «Найти». Инструмент также можно активировать с помощью отдельной кнопки на панели инструментов.

    Поиск в экселе 5

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

    Поиск в экселе 6

    Она также может быть выполнена в виде одноименной формулы, которая позволяет осуществлять поиск по словам. Возьмем, к примеру, список фруктов. Предположим, вы набираете =Find(«a»;$B$4:$B$11), используете автозаполнение и получаете следующий результат:

    Поиск в экселе 7

    Различия

    После анализа результатов, полученных при использовании инструментов SEARCH и SEARCH, можно выделить несколько ключевых отличий:

    1. Функция ПОИСК чувствительна к регистру, т.е. алгоритм распознает большие и маленькие символы. Это хорошо видно на примере оранжевого цвета. SEARCH дал 1, а HATE дал ошибку, потому что строчная буква a отсутствует в ячейке. Аналогичная ситуация наблюдается с ананасом и айвой.

    Поиск в экселе 8

    1. Дополнительным отличием является то, что SEARCH может работать с подстановочными знаками, такими как знаки вопроса (?) и звездочки (*), которые используются для неточной формы поискового запроса.

    Примечание! Важно использовать тильду (), чтобы найти именно эти символы в тексте

    1. Инструмент ПОИСК реализован как в виде отдельной кнопки на главной панели, так и в качестве встроенной функции редактора.

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

    Трюк №70. Поиск n-го вхождения значения при помощи функции ВПР

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

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

    В этом примере мы предполагаем, что у вас есть таблица с двумя столбцами данных, где столбец А содержит имена, а столбец В — возраст людей (рис. 6.12).

    Рис. 6.12. Настройка данных для функции ВПР (VLOOKUP)

    Рис. 6.12. Установка данных для функции VLOOKUP

    Вы можете использовать функцию VLOOKUP, чтобы выделить возраст человека из его имени. К сожалению, некоторые имена встречаются несколько раз. Вы хотите, чтобы VLOOKUP мог находить не только первое вхождение имени Дэйв, но и последующие вхождения. Вот как это сделать (помните, что в этом примере данные находятся в столбцах A и B).

    Сначала полностью выделите столбец А, щелкнув букву А в заголовке столбца, а затем выберите команду Вставка → Столбцы (Insert → Columns), чтобы добавить пустой столбец (который станет столбцом А). Щелкните ячейку А2 (пропустив А1, так как В1 — это заголовок) и введите следующую формулу: =B2&COUNTIF($B$2:B2;B2), в русской версии Excel =В2&СЧЁТЕСЛИ($В$2:В2;В2). Скопируйте ее вниз на столько строк, сколько данных в столбце В (еще раз щелкните ячейку А2 и дважды щелкните маркер заполнения). Вы получите имена Дэйв1, Дэйв2, Дэйв3 и т. д. (рис. 6.13). Обратите внимание на абсолютную ссылку $В$2 в функции СЧЁТЕСЛИ (COUNTIF) и на то, что остальные ссылки — относительные. Это необходимо, чтобы функция работала правильно.

    Рис. 6.13. Данные с формулой ВПР (VLOOKUP), добавленной к столбцу А

    Рисунок 6.13: Данные с формулой VLOOKUP, добавленные в столбец A

    Если вы еще не догадались: теперь вы можете найти n-ое вхождение любого имени в столбце A.

    Щелкните ячейку D2 и введите следующую формулу: =VLOOKUP(«Дэйв3»;$A$l:$C$100;3;FALSE), в русской версии Excel =ВПР(«Дэйв3»;$А$1:$С$100;3;ЛОЖЬ). Формула вернет возраст для третьего вхождения имени Дэйв (рис. 6.14).

    Рис. 6.14. Данные со второй формулой ВПР (VLOOKUP), добавленной к столбцу D

    Рис. 6.14. Вторая формула VLOOKUP добавляется в столбец D данных

    Конечно, можно скрыть столбец А, так как видеть его не нужно. Кроме того, имена в столбце А можно использовать как исходный диапазон для списка в другой ячейке, выбрав команду Данные → Проверка → Список (Data → Validation List) и введя ссылку на ячейку, содержащую этот список, в функции ВПР (VLOOKUP).

    голоса
    Рейтинг статьи
    Ссылка на основную публикацию
    Adblock
    detector