Эксель и функция: Функция И — Служба поддержки Майкрософт

Функция ЕСЛИ в Excel для чайников

Узнайте, как, когда и зачем применяется функция ЕСЛИ в Excel.

Table Of Contents

1. Введение
2. Функция ЕСЛИ — Описание
3. Синтаксис
4. Пример
5. Заключение

Введение

Функция ЕСЛИ является наиболее популярной среди логических функций Excel. Поняв, как работать с ней, вам станет легко оперировать остальными логическими функциями.

Функция ЕСЛИ — Описание

Итак, функция ЕСЛИ возвращает одно значение, если указанное условие дает в результате значение ИСТИНА, и другое значение, если условие дает в результате значение ЛОЖЬ.

Синтаксис

=ЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь])

Обязательный аргумент «лог_выражение» является любым значением или выражением, которое в результате возвращает значение ИСТИНА или ЛОЖЬ.

Например, если значение в ячейке B4 равно «Google», тогда это выражение принимает значение ИСТИНА, в противном случае — значение ЛОЖЬ.

Причем в этом аргументе (лог_выражение) может использоваться любой оператор сравнения. 

Допустим: A2>100.

Необязательный аргумент «значение_если_истина» — это значение, которое возвращается, если аргумент «лог_выражение» соответствует значению ИСТИНА.

И еще один необязательный аргумент «значение_если_ложь» — это значение, которое возвращается, если аргумент «лог_выражение» соответствует значению ЛОЖЬ.

Перейдем к практике.

Пример

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

Условие следующее: если в колонке G температура ниже 0, то «значение_если_истина» в функции ЕСЛИ будет «Да», а если выше, то «значение_если ложь» составит «Нет».

В ячейке I2 ставим знак «равно» и вводим сперва наше логическое выражение, другими словами утверждение: G2<0.

Там, где данное условие соблюдается, то есть это ИСТИНА, пусть Excel вернет нам «Да», а где нет, то есть ЛОЖЬ – «Нет».

Ответы получены.

Поэкспериментируем, изменив значение на «-1».

Поскольку условие G2<0 соблюдается, то мы получаем «Да».

Заключение

Итак, функция ЕСЛИ оказалась не такой уж и сложной, не правда ли?

💡Кстати, получите бесплатно мое PDF руководство по 6 самым распространенным ошибкам в формуле Excel, с которыми отчаянно борются многие! 

Дарю 7-ми дневное обучение в моей академии!

Если вам понравилась данная статья, то обязательно поделитесь ею и подпишитесь!

💥💻📊 Мой девиз: Учись сегодня, создай что-то практичное и успешное завтра. Пусть он станет вашим!

04.04.2021

345

Функции Excel И и ИЛИ

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

Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010; и Excel для Mac.

TRUE или FALSE Значения

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

• Для функции ИЛИ (см. Строку 2 выше) проверяются несколько условий. Если какое-либо из проверенных условий является истинным, функция ИЛИ возвращает ответ ИСТИНА. Если все условия не выполняются, OR дает значение FALSE.
• Для функции AND (см. Строку 3 выше) проверяются несколько условий. Если все условия выполняются, функция возвращает ИСТИННЫЙ ответ. Если нет, функция возвращает FALSE в качестве значения.

Объединить с другими функциями

Значения TRUE или FALSE отображаются «как есть» в ячейках, где расположены функции. Функции также можно комбинировать с другими функциями Excel, такими как функция IF (показана в строках 4 и 5 выше), для получения различных результатов или выполнения ряда вычислений.

Как работает каждая функция

На изображении выше ячейки B2 и B3 содержат функции OR и AND. Оба используют несколько  операторов сравнения  для проверки различных условий для данных в ячейках A2, A3 и A4  рабочего листа .

Две функции заключаются в следующем:

Условия, которые они тестируют:

• Если данные в ячейке A2 меньше 50 (символ <означает меньше).
• Если данные в ячейке A3 не равны 75 (<> является символом не равным).
• Если данные в ячейке A4 больше или равны 100 (> = является символом больше или равно).

Для функции AND в ячейке B3 данные в ячейках с A2 по A4 должны соответствовать всем трем условиям, чтобы функция возвращала ИСТИННЫЙ ответ. Первые два условия выполнены. Поскольку значение в ячейке A4 не больше или равно 100, выход для функции AND равен FALSE.

Для функции ИЛИ в ячейке В2 данные в ячейках А2, А3 или А4 должны соответствовать только одному условию, чтобы функция возвращала ИСТИННЫЙ ответ. В этом примере данные в ячейках A2 и A3 удовлетворяют требуемому условию, поэтому для функции OR выводится TRUE.

Синтаксис функции и аргументы

Синтаксис функции   относится к макету функции и включает в себя имя функции, скобки и  аргументы . Синтаксис для функции OR:

Синтаксис для функции AND:

Logical1 (обязательно): относится к тестируемому состоянию. Форма условия — это ссылка на  ячейку  проверяемых данных, за которой следует само условие, например A2 <50.

Logical2, … Logical255 (необязательно): дополнительные условия, которые можно протестировать максимум до 255.

Введите функцию ИЛИ

Шаги ниже описывают, как войти в функцию ИЛИ, расположенную в ячейке B2 на изображении выше. Те же шаги можно использовать для входа в функцию AND, расположенную в ячейке B3.

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

1. Откройте пустой лист Excel и введите следующие данные.

2. Выберите ячейку B2, чтобы сделать ее  активной . Это где функция ИЛИ будет расположена.

3. Выберите  Формулы .

4. Выберите  Logical,  чтобы открыть раскрывающийся список функций.

5. Выберите  ИЛИ,  чтобы открыть диалоговое окно «Аргументы функций». Если вы используете Mac, откроется Formula Builder .

6. Поместите курсор в текстовое поле Logical1.

7. Выберите ячейку A2 на листе, чтобы ввести эту ссылку на ячейку.

8. В диалоговом окне введите <50 после ссылки на ячейку.

9. Поместите курсор в текстовое поле Logical2.

10. Выберите ячейку A3 на листе, чтобы ввести ссылку на вторую ячейку.

11. Введите <> 75  после ссылки на ячейку.

12. Поместите курсор в текстовое поле Logical3.

13. Выберите ячейку A4 на листе, чтобы ввести третью ссылку на ячейку.

14. Введите  > = 100  после ссылки на ячейку.

15. Выберите ОК . За исключением Excel для Mac, где вы выбираете Готово .

Значение TRUE  появляется в ячейке B2, потому что данные в ячейке A3 соответствуют условию не быть равным 75. Когда вы выбираете ячейку B2, полная функция появляется на  панели формул  над рабочим листом.

Попробуйте функцию AND

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

Значение FALSE  появляется в ячейке B3, потому что только одно из тестируемых условий должно быть ложным, чтобы функция AND возвращала значение FALSE. В этом примере два условия ложны:

• Данные в ячейке А2 не менее 50.
• Данные в ячейке A4 не больше или не равны 100.

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

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

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

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

Excel работает с использованием функций, а их около 500! Они варьируются от базовой арифметики до сложной статистики.

Если вы новый пользователь Excel, это количество может быть довольно пугающим.

Итак, мы здесь, чтобы помочь вам! 🤝

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

Начнем!

Содержание

Что такое функции Excel?

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

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

Что такое операторы?

Это символы, которые определяют тип вычисления, которое вы хотите выполнить для элементов формулы.

Например, чтобы сложить два числа, введите в ячейку «=1+1». Как только вы нажмете , введите , Excel выполнит формулу и вернет результат, равный 2.

Вот несколько примеров распространенных операторов:

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

Вы можете обойти это, добавив начальный апостроф (‘) . Вот как вы можете отображать формулы в виде текста, как в таблице выше.

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

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

Что такое функции?

Это предварительно определенные процессы в Excel. Каждая функция в Excel имеет уникальное имя и конкретные входные данные . Функция принимает эти входные данные и выполняет соответствующие вычисления.

Входы или аргументы функции Excel всегда заключаются в круглые скобки.

Например, это синтаксис для функции MAX :

=MAX(число1, [число2], …)

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

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

По мере изучения Excel вы обнаружите, что формулы Excel редко состоят из отдельных чисел, как в формуле «=1+1».

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

Хорошо! Вы только что узнали, как работает функция в Excel.

Давайте погрузимся прямо в список! 🤿

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

Основные математические функции (начальный уровень ★☆☆)

1. СУММ

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

Синтаксис: =СУММ(число1, [число2], …)

Попробуйте это в рабочей тетради.

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

1. Введите эту формулу в ячейку F2 :

=СУММ(B2:E2)

Вы также можете ввести «=СУММ(B2,C2,D2,E2)», но «=СУММ(B2: E2)» намного проще.

2. Нажмите Введите . Затем Excel оценивает формулу, и ячейка возвращает общее число, равное 360.

3. Скопируйте это для остальных учащихся или перетащите вниз маркер заполнения .

Обратите внимание, что функция СУММ игнорирует ячейки, содержащие текст . («X» означает, что учащийся не смог пройти тест)

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

2. СЧЕТ

Далее следует функция СЧЁТ . Он возвращает количество ячеек, содержащих числовые значения в пределах входного диапазона.

Синтаксис : =СЧЕТ(значение1, [значение2], …)

1. Чтобы получить количество тестов, пройденных каждым учащимся, используйте эту формулу в ячейке G2 :

=СЧЕТ(B2:E2 )

2. Нажмите Введите и заполните строки ниже.

Если вы хотите включить в подсчет нечисловые значения, вы можете использовать СЧЕТЧИК функция. Чтобы подсчитать количество пустых ячеек, вы можете использовать функцию СЧИТАТЬПУСТОТЫ .

Узнайте больше о функции COUNT и ее вариантах здесь.

3. СРЕДНЕЕ

Среднее значение списка чисел равно сумме, деленной на количество чисел в этом списке.

Это достаточно просто, чтобы подсчитать баллы викторины. У вас уже есть СУММА и СЧЕТЧИК тестов для каждого учащегося.

Но с 9 становится еще проще0003 Функция СРЗНАЧ в Excel.

Синтаксис : =СРЗНАЧ (значение1, [значение2], …)

1. Введите это в ячейку h3 :

=СРЗНАЧ(B2:E2)

и введите 2 в поле Enter ряды ниже.

Функция СРЗНАЧ также имеет варианты для более сложных вычислений. Кликните сюда, чтобы узнать больше.

Логические функции (средний уровень — ★★☆)

Немного повысим уровень сложности.

А 9Логическая функция 0003 в Excel позволяет проводить сравнения и использовать результаты для изменения способа вычисления формулы.

4. ЕСЛИ

Функция ЕСЛИ — очень популярная функция в Excel, и ее довольно легко освоить.

Синтаксис : =IF(логическая_проверка, значение_если_истина, [значение_если_ложь])

Эта функция проверяет, является ли логическая проверка ИСТИННОЙ или ЛОЖНОЙ. Затем он возвращает указанное значение на основе результата.

Используя средний балл каждого учащегося, попробуйте поставить оценки «ПРОШЕЛ» или «НЕ ПРОШЕЛ». Предположим, что проходной балл для этого класса равен 60.

1. Начните формулу в ячейке C2 с «=IF(«

оценка 60.

2. Итак, формула принимает вид:

=ЕСЛИ(B2>=60,

Если сравнение возвращает ИСТИНА , то формула должна вернуть текст «ПРОШЕЛ». Таким образом, value_if_true аргумент должен быть «PASS».0005

И если он возвращает FALSE , то аргумент value_if_false должен быть «FAIL».

3. Таким образом, формула принимает вид:

=ЕСЛИ(B2>=60,»ПРОШЕЛ»,»НЕ ПРОШЕЛ»)

4. Нажмите Enter и заполните строки ниже.

Что, если бы вам нужно было выставлять оценки по шкале, а не просто «ПРОШЕЛ» и «НЕ ПРОШЕЛ»?

Для этого необходимо использовать несколько критериев или логических тестов. Хотя это возможно с использованием вложенных функций ЕСЛИ , он может очень быстро запутаться. Вместо этого вы можете использовать функцию IFS .

5. Функция IFS

Функция IFS была введена в Excel 2016 для замены вложенных функций ЕСЛИ .

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

📖 Другими словами, функция IFS выводит значение, соответствующее первому заданному критерию, который является истинным .

Синтаксис : =IFS(логическая_проверка1, значение_если_истина1, [логическая_проверка2], [значение_если_истина2],..)

1. Сначала формула должна проверить, является ли средний балл (столбец B) больше или равен 90 , Если да, он должен вернуть «А» .

=IFS(B2>=90,»A»,

2. Если нет, то следует проверить, больше ли средний балл или равен 80. Если да, он должен вернуть «Б» . Если вы сделаете это до уровня D, формула станет такой:

=IFS(B2>=90,A”,B2>=80,B”,B2>=70,C”,B2>=60 «D»,

3. Для последней оценки «F» поставьте «ИСТИНА» для логического теста

Функция IFS будет оценивать только последних указанных критериев , если все предыдущие логические значения были FALSE. Таким образом, вы можете установить последних критерия, чтобы они всегда были TRUE , что делает его оператором « catch all ».0005

Окончательная формула будет следующей:

=IFS(B2>=90,A,B2>=80,B,B2>=70,C,B2>=60,D, TRUE, «F»)

PRO-TIP:

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

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

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

=ЕСЛИ(B2>=$H$2,$F$2,B2>=$H$3,$F$3,B2>
=$H$4,$F$4,B2>=$H$5,$ F$5,TRUE,$F$6)

Текстовые функции (средний уровень — ★★☆)

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

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

Для этого сначала необходимо извлечь имя и фамилию из столбца A.

6. НАЙТИ

Имена разделены пробелом » « . Таким образом, вы должны определить положение пробела в каждой текстовой строке в столбце A.

Функция НАЙТИ в Excel возвращает номер или позицию указанного символа или подстроки в другой текстовой строке.

Синтаксис : =НАЙТИ(найти_текст, внутри_текста, [начальный_номер])

Чтобы получить позицию пробела ” “ , введите следующую формулу:

=НАЙТИ(» «,A2)

,

взгляните на функцию LEN .

7. LEN

Эта функция возвращает количество символов в текстовой строке.

Синтаксис : =LEN(текст)

Чтобы получить количество символов в имени каждого учащегося:

=LEN(A2)

Теперь можно перейти к извлечению имени и фамилии с помощью функции MID в Excel.

8. MID

Эта функция извлекает заданное количество символов из середины текстовой строки.

Синтаксис : = MID(text, start_num, num_chars)

Это одна из трех текстовых функций, которые используются для извлечения текста. Два других — LEFT и RIGHT — извлекают текст из начала и конца текстовой строки соответственно.

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

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

=MID(A2,1,B2-1)

Или вы можете выразить ее напрямую, используя формулу НАЙТИ ранее.

=СРЕД(A2,1,НАЙТИ(» «,A2)-1)

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

=СРЕД(A2,B2+1,C2-B2)

Или, используя более ранние формулы НАЙТИ и ДЛСТР : ,ДЛСТР(A2)-НАЙТИ(» «,A2))

Теперь вы можете комбинировать фамилию и имя в нужном порядке, используя Функция CONCAT .

9. СЦЕПИТЬ

Подобно IFS , СЦЕПИТЬ — еще одна новая функция в Excel 2016. Она заменила старую функцию СЦЕПИТЬ .

Синтаксис : =CONCAT(текст1, [текст2],…)

Объедините фамилию и имя с запятой и символом пробела «,» между ними.

=CONCAT(E2″, «,D2)

ПРОФЕССИОНАЛЬНЫЙ СОВЕТ:

В приведенном выше примере вы использовали вспомогательные столбцы для FIND , LEN и MID , чтобы помочь построить окончательную формулу и визуализировать ее работу.

В реальных приложениях вы можете использовать одну длинную формулу для получения таких результатов: «,A2)),», «,MID(A2,1,FIND(» «,A2)-1))

Функции поиска и ссылок (расширенный уровень – ★★★)

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

Взгляните на рабочий лист «Расписание» .

10. СТОЛБЦ

Функция СТОЛБЦ в Excel возвращает номер столбца данной ячейки.

Синтаксис : =СТОЛБЦ([ссылка])

Давайте попробуем назначить конкретные даты для каждого теста. Например, вы можете захотеть, чтобы викторины проводились каждый понедельник. Это означает, что первая дата викторины должна смещаться на 1 неделю или 7 дней для каждой последующей даты викторины.

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

=$B$2+(COLUMN()-2)*7

Два (2) вычитается из номера столбца, так что последовательность начинается с 1.

Вы также можете получить этот результат, используя гораздо более простой «=B2+7» , так как вы добавляете фиксированное количество дней к каждой дате. 🤔

Но, используя функцию СТОЛБЦ , можно создавать сложные узоры.

Возьмем, к примеру, такой шаблон:

Викторины по-прежнему проводятся каждый понедельник. Но каждую третью неделю они проводятся в среду.

Вот формула для этого шаблона:

=$B$2+(COLUMN()-2)*7+IF(MOD(COLUMN()-1,3)=0,2,0)

Функция MOD в Excel возвращает остаток после деления числа на заданный делитель. Это часть Math & Trig группы функций.

Эта группа включает в себя другие забавные функции, такие как ABS , которая возвращает абсолютное значение числа, и ROUND , которая округляет число до указанного количества цифр.

Узнайте больше о функциональных группах в конце этой статьи!

Каспер Лангманн , специалист по Microsoft Office

11. ROW

Далее рассмотрим функцию ROW . Он работает точно так же, как COLUMN , но вместо этого возвращает номер строки.

Синтаксис : =СТРОКА([ссылка])

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

Предположим, что R1C1 — это место, ближайшее к столу учителя.

1. Вы можете рассадить студентов одно за другим и в две колонки:

=CONCAT(«R»,MOD(ROW()-6,3)*2+1″,C»,INT( (СТРОКА()-6)/3)*2+1)

2. Или они могут располагаться в строках по 3 и в столбцах по 2

=CONCAT(«R»,MOD(ROW()-6, 2)*2+1″,C»,INT((ROW()-6)/2)*2+1)

3. Вы также можете посадить их в самые дальние ряды:

=CONCAT(» R», MOD(СТРОКА()-6,2)*4+1″,C»,INT((СТРОКА()-6)/2)*2+1)

4. Или в самых дальних столбцах:

=CONCAT(«R»,MOD(ROW()-6,3)*2+1″,C»,INT((ROW()-6)/ 3)*4+1)

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

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

12. ПОИСКПОЗ

Теперь откройте рабочий лист «Поиск» .

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

Для начала вы будете использовать функцию ПОИСКПОЗ . Он ищет указанный элемент в заданном диапазоне ячеек. Затем он возвращает относительную позицию первого совпадения.

Синтаксис : =ПОИСКПОЗ(искомое_значение, искомое_массив, [тип_совпадения])

1. искомое_значение — это элемент, который вы хотите найти. Итак, установите это на ячейку B2 .
2. lookup_array — это диапазон или массив таблиц, в которых вы хотите выполнить поиск. Используйте F2:F7 из рабочего листа «Список классов» .
3. Для match_type установите это значение равным нулю, чтобы функция искала точное совпадение . (Узнайте больше о MATCH и различных типах в этой статье)

Тогда формула будет выглядеть так:

=MATCH(B2,’Class List’!F2:F7,0)

он работает правильно только в том случае, если имя введено точно так, как оно написано в столбце F списка классов .

Чтобы исправить это, вы можете использовать подстановочный знак звездочки «*» , чтобы работал поиск по имени или фамилии.

Формулу также можно заключить в функцию IFNA . Таким образом, если формула не может найти данное имя в таблице, она вернет фразу типа «Результат не найден».

13. ИНДЕКС

Функция ИНДЕКС извлекает значение из заданного массива таблиц на основе предоставленных номеров строк и столбцов.

Синтаксис : =ИНДЕКС (массив, номер_строки, [номер_столбца])

Подобно примеру ПОИСКПОЗ , вам необходимо указать, где находится поиск диапазона или массива.

Для row_num можно использовать более ранний результат MATCH в Cell B5 . Затем для col_num используйте 1 для First Name :

=INDEX(‘Class List’!D2:E7,B5,1)

И установите col_num на 2 для 4 Last Name 4 .

=ИНДЕКС(‘Список классов’!D2:E7,B5,2)

Вот так у вас есть рабочая формула поиска 🔍!

Это всего лишь небольшой пример бесчисленных возможностей использования ИНДЕКС и СОВПАДЕНИЕ комбинация . Нажмите здесь, чтобы увидеть больше примеров!

14. ВПР

Функция ВПР в Excel работает аналогично комбинации ИНДЕКС и ПОИСКПОЗ . Он быстрее настраивается, но менее универсален. VLOOKUP также работает, только если ваш массив поиска находится в самом левом углу справочной таблицы.

Синтаксис : = ВПР (искомое_значение, табличный_массив, индексный_номер столбца, [диапазон_просмотра])

На этот раз вы будете использовать результат «Имя» (ячейка B6) в качестве lookup_value . Используйте это и VLOOKUP , чтобы получить баллы данного учащегося из рабочего листа «Оценки викторины» .

=ВПР($B$6,’Оценки викторины’!$A$2:$E$7,СТОЛБЦ(),ЛОЖЬ)

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

=ВПР($B$7&»*»,Расписание!$A$6:$E$11,СТОЛБЦ(),ЛОЖЬ)

15.

ДВССЫЛ

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

ДВССЫЛ позволяет указывать ссылки на ячейки с помощью текстовых строк.

СИНТАКСИС : =ДВССЫЛ(ref_text, [a1])

Например, вместо ввода «=A1» вы можете ввести «=ДВССЫЛ(«A»&1) . Это означает, что вы можете динамически изменять ссылки.

Возьмем ИНДЕКС И СООТВЕТСТВУЙТЕ 9Формула 0004, которую вы использовали для получения фамилии. Вы можете получить тот же результат, используя эту формулу:

=ДВССЫЛ(«‘Список классов’!»&»E»&(B5+1))

Функция ДВССЫЛ открывает так много возможностей с динамическими ссылками в Экселе. Я высоко ценю эту статью как подробный учебник по INDIRECT.

Вот и все – Что теперь?

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

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

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

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

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

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

Мы поможем вам максимально эффективно использовать Excel! 📈

Другие соответствующие ресурсы

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

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

Спасибо за внимание! 😄

Kasper Langmann2022-10-21T18:46:11+00:00 Ссылка для загрузки страницы

30 функций Excel за 30 дней 30XL30D

Как использовать текст, информацию и функции поиска Excel, такие как ВПР, ИНДЕКС, ДВССЫЛ и ЯЧЕЙКА

Знакомство с заданием 30XL30D

Следите за блогом Contextures

Дополнительные учебные пособия

Введение в программу 30XL30D Challenge

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

В январе 2011 г. для испытания 30XL30D мы сосредоточились на 30 Excel
функции в следующих категориях:

• Текст
• Информация
• Поиск и ссылка

Следите за блогом Contextures

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

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

• ВПР
• ИНДЕКС
• СПИЧКА
• КОСВЕННЫЙ  

Чтобы увидеть, как работают функции Excel, вы можете загрузить 30XL30D
примеры файлов из ежедневных статей блога Contextures. Файлы
будет в формате Excel 2010/2007 и заархивирован.

Excel Functions Challenge Ежедневные сообщения в блоге

День
01 — EXACT — проверка на точное совпадение между текстовыми строками,
включая верхний и нижний регистр.

День
02 — ОБЛАСТИ — возвращает количество областей в ссылке

День
03 — TRIM — удаляет лишние пробелы из текстовой строки

День
04 — INFO — получить информацию об операционной среде

https://contexturesblog.com/archives/2011/01/06/30-excel-functions-in-30-days-05-choose/ — получить значение из списка на основе индекса
номер

День
06 — ИСПРАВЛЕНО — округлить число и вернуть результат в виде текста.

День
07 — CODE — возвращает числовой код для первого символа
в текстовой строке

День
08 — CHAR — возвращает указанный символ для числа
введено

День
09 — ВПР — ищет значение в первом столбце таблицы,
возвращает другое значение из той же строки

День
10 — HLOOKUP — ищет значение в первой строке таблицы, возвращает
другое значение из того же столбца

День
11 — CELL — предоставляет информацию о форматировании ячейки, содержании и
местоположение

День
12 — COLUMNS — возвращает количество столбцов в массиве.
или ссылка

День
13 — TRANSPOSE — изменение вертикальных диапазонов на горизонтальные,
или наоборот

День
14 — T — возвращает текст из значения или пустую строку
без текста

День
15 — REPT — повторяет текстовую строку заданное количество раз

День
16 — ПРОСМОТР — возвращает значение из одной строки или одного столбца
диапазона или из массива

День
17 — ERROR.TYPE — возвращает число, основанное на типе ошибки

День
18 — SEARCH — ищет текстовую строку в другом тексте
строка и возвращает ее позицию

День
19 — ПОИСКПОЗ — ищет значение в массиве и возвращает его
позиция, если найдена

День
20 — ADDRESS — создает адрес в виде текста из строки и столбца
цифры

День
21 — TYPE — определяет тип значения в ячейке, возвращая
номер

день
22 — N — Возвращает число на основе значения ячейки

День
23 — НАЙТИ — находит текст в другой строке и учитывает регистр
чувствительный

День
24 — ИНДЕКС — возвращает значение или ссылку на основе строки и
номер столбца

День
25 — ЗАМЕНИТЕ — заменяет указанное количество символов
в текстовой строке с новым текстом.

https://contexturesblog.com/archives/2011/01/27/30-excel-functions-in-30-days-26-offset/ — возвращает ссылку, указанного размера, смещения
от начального номера

День
27 — ЗАМЕНА — заменяет старый текст новым текстом в тексте
строка с учетом регистра

День
28 — ГИПЕРССЫЛКА — создает гиперссылку в ячейке на основе ссылки
расположение и понятное имя

День
29 — CLEAN — удаляет непечатаемые символы из текста

День
30 — INDIRECT — возвращает ссылку, указанную текстом
строка

Смотреть видеоролики о функциях

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

На сайте Contextures есть и другие видео. Например,
Вы можете посмотреть одно из следующих видео на ПОИСКПОЗ или ВПР:

Видеоруководство по функции ПОИСКПОЗ в Excel  

Видеоруководство по функции ВПР в Excel 

Скрыть ошибки функции ВПР в Excel
С IF и ISNA

Или посмотрите это видео с ГИПЕРССЫЛКОЙ.