Excel значение между двумя числами
Обновлено: 03.07.2024
Если вы еще не Excel в Интернете, скоро вы увидите, что это не просто сетка для ввода чисел в столбцах или строках. Да, с помощью Excel в Интернете можно найти итоги для столбца или строки чисел, но вы также можете вычислять платежи по ипотеке, решать математические или инженерные задачи или находить лучшие сценарии в зависимости от переменных чисел, которые вы подключали.
Excel в Интернете делает это с помощью формул в ячейках. Формула выполняет вычисления или другие действия с данными на листе. Формула всегда начинается со знака равенства (=), за которым могут следовать числа, математические операторы (например, знак "плюс" или "минус") и функции, которые значительно расширяют возможности формулы.
Ниже приведен пример формулы, умножающей 2 на 3 и прибавляющей к результату 5, чтобы получить 11.
Следующая формула использует функцию ПЛТ для вычисления платежа по ипотеке (1 073,64 долларов США) с 5% ставкой (5% разделить на 12 месяцев равняется ежемесячному проценту) на период в 30 лет (360 месяцев) с займом на сумму 200 000 долларов:
Ниже приведены примеры формул, которые можно использовать на листах.
=A1+A2+A3 Вычисляет сумму значений в ячейках A1, A2 и A3.
=КОРЕНЬ(A1) Использует функцию КОРЕНЬ для возврата значения квадратного корня числа в ячейке A1.
=СЕГОДНЯ() Возвращает текущую дату.
=ПРОПИСН("привет") Преобразует текст "привет" в "ПРИВЕТ" с помощью функции ПРОПИСН.
=ЕСЛИ(A1>0) Анализирует ячейку A1 и проверяет, превышает ли значение в ней нуль.
Элементы формулы
Формула также может содержать один или несколько из таких элементов: функции, ссылки, операторы и константы.
1. Функции. Функция ПИ() возвращает значение числа Пи: 3,142.
2. Ссылки. A2 возвращает значение ячейки A2.
3. Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.
4. Операторы. Оператор ^ ("крышка") применяется для возведения числа в степень, а оператор * ("звездочка") — для умножения.
Использование констант в формулах
Использование операторов в формулах
Операторы определяют операции, которые необходимо выполнить над элементами формулы. Вычисления выполняются в стандартном порядке (соответствующем основным правилам арифметики), однако его можно изменить с помощью скобок.
Типы операторов
Приложение Microsoft Excel поддерживает четыре типа операторов: арифметические, текстовые, операторы сравнения и операторы ссылок.
Арифметические операторы
Арифметические операторы служат для выполнения базовых арифметических операций, таких как сложение, вычитание, умножение, деление или объединение чисел. Результатом операций являются числа. Арифметические операторы приведены ниже.
Арифметический оператор
Возведение в степень
Операторы сравнения
Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение: ИСТИНА либо ЛОЖЬ.
Оператор сравнения
>= (знак «больше или равно»)
Больше или равно
<= (знак «меньше или равно»)
Меньше или равно
Текстовый оператор конкатенации
Амперсанд (&) используется для объединения (соединения) одной или нескольких текстовых строк в одну.
Текстовый оператор
Соединение или объединение последовательностей знаков в одну последовательность
Выражение «Северный»&«ветер» дает результат «Северный ветер».
Операторы ссылок
Для определения ссылок на диапазоны ячеек можно использовать операторы, указанные ниже.
Оператор ссылки
Оператор диапазона, который образует одну ссылку на все ячейки, находящиеся между первой и последней ячейками диапазона, включая эти ячейки.
; (точка с запятой)
Оператор объединения. Объединяет несколько ссылок в одну ссылку.
Оператор пересечения множеств, используется для ссылки на общие ячейки двух диапазонов.
Порядок выполнения Excel в Интернете формулах
В некоторых случаях порядок вычисления может повлиять на возвращаемое формулой значение, поэтому для получения нужных результатов важно понимать стандартный порядок вычислений и знать, как можно его изменить.
Порядок вычислений
Формулы вычисляют значения в определенном порядке. Формула всегда начинается со знака равно(=).Excel в Интернете интерпретирует знаки после знака равно как формулу. После знака равно вычисляются элементы (операнды), такие как константы или ссылки на ячейки. Они разделены операторами вычислений. Excel в Интернете вычисляет формулу слева направо в соответствии с определенным порядком для каждого оператора в формуле.
Приоритет операторов
Если в одной формуле несколько операторов, Excel в Интернете выполняет операции в том порядке, который показан в таблице ниже. Если формула содержит операторы с одинаковым приоритетом, например операторы деления и умножения, Excel в Интернете эти операторы оцениваются слева направо.
Возведение в степень
Умножение и деление
Сложение и вычитание
Объединение двух текстовых строк в одну
Использование круглых скобок
Чтобы изменить порядок вычисления формулы, заключите ее часть, которая должна быть выполнена первой, в скобки. Например, следующая формула дает результат 11, так как Excel в Интернете умножение выполняется перед с добавлением. В этой формуле число 2 умножается на 3, а затем к результату прибавляется число 5.
Если же изменить синтаксис с помощью скобок, Excel в Интернете сбавляет 5 и 2, а затем умножает результат на 3, чтобы получить 21.
В следующем примере скобки, в которые заключена первая часть формулы, принудительно Excel в Интернете сначала вычислить ячейки B4+25, а затем разделить результат на сумму значений в ячейках D5, E5 и F5.
Использование функций и вложенных функций в формулах
Функции — это заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления.
Синтаксис функций
Приведенный ниже пример функции ОКРУГЛ, округляющей число в ячейке A10, демонстрирует синтаксис функции.
1. Структура. Структура функции начинается со знака равно (=), за которым следуют имя функции, открывая скобка, аргументы функции, разделенные запятой, и закрывая скобка.
2. Имя функции. Чтобы отобразить список доступных функций, щелкните любую ячейку и нажмите клавиши SHIFT+F3.
4. Всплывающая подсказка аргумента. При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, всплывающая подсказка появляется после ввода выражения =ОКРУГЛ(. Всплывающие подсказки отображаются только для встроенных функций.
Ввод функций
Диалоговое окно Вставить функцию упрощает ввод функций при создании формул, в которых они содержатся. При вводе функции в формулу в диалоговом окне Вставить функцию отображаются имя функции, все ее аргументы, описание функции и каждого из аргументов, текущий результат функции и всей формулы.
Чтобы упростить создание и редактирование формул и свести к минимуму количество опечаток и синтаксических ошибок, пользуйтесь автозавершением формул. После того как вы введите знак " ocpSection" role="region" aria-label="Вложенные функции">
Вложенные функции
В некоторых случаях может потребоваться использовать функцию в качестве одного из аргументов другой функции. Например, в приведенной ниже формуле для сравнения результата со значением 50 используется вложенная функция СРЗНАЧ.
1. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.
<c0>Предельное количество уровней вложенности функций</c0>. В формулах можно использовать до семи уровней вложенных функций. Если функция Б является аргументом функции А, функция Б находится на втором уровне вложенности. Например, в приведенном выше примере функции СРЗНАЧ и СУММ являются функциями второго уровня, поскольку обе они являются аргументами функции ЕСЛИ. Функция, вложенная в качестве аргумента в функцию СРЗНАЧ, будет функцией третьего уровня, и т. д.
Использование ссылок в формулах
Ссылка указывает на ячейку или диапазон ячеек на сайте и сообщает Excel в Интернете, где искать значения или данные, которые вы хотите использовать в формуле. С помощью ссылок в одной формуле можно использовать данные, которые находятся в разных частях листа, а также значение одной ячейки в нескольких формулах. Вы также можете задавать ссылки на ячейки разных листов одной книги либо на ячейки из других книг. Ссылки на ячейки других книг называются связями или внешними ссылками.
Стиль ссылок A1
Стиль ссылок по умолчанию По умолчанию в Excel в Интернете используется стиль ссылок A1, который ссылается на столбцы буквами (от A до XFD, всего 16 384 столбца) и ссылается на строки с числами (от 1 до 1 048 576). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, и затем — номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2.
Ячейка или диапазон
Использование
Ячейка на пересечении столбца A и строки 10
Диапазон ячеек: столбец А, строки 10-20.
Диапазон ячеек: строка 15, столбцы B-E
Все ячейки в строке 5
Все ячейки в строках с 5 по 10
Все ячейки в столбце H
Все ячейки в столбцах с H по J
Диапазон ячеек: столбцы А-E, строки 10-20
<c0>Ссылка на другой лист</c0>. В приведенном ниже примере функция СРЗНАЧ используется для расчета среднего значения диапазона B1:B10 на листе «Маркетинг» той же книги.
1. Ссылка на лист «Маркетинг».
2. Ссылка на диапазон ячеек с B1 по B10 включительно.
3. Ссылка на лист, отделенная от ссылки на диапазон значений.
Различия между абсолютными, относительными и смешанными ссылками
<c0>Относительные ссылки</c0>. Относительная ссылка в формуле, например A1, основана на относительной позиции ячейки, содержащей формулу, и ячейки, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании или заполнении формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании или заполнении относительной ссылки из ячейки B2 в ячейку B3 она автоматически изменяется с =A1 на =A2.
<c0>Абсолютные ссылки</c0>. Абсолютная ссылка на ячейку в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам и столбцам абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, а для использования абсолютных ссылок надо активировать соответствующий параметр. Например, при копировании или заполнении абсолютной ссылки из ячейки B2 в ячейку B3 она остается прежней в обеих ячейках: =$A$1.
Смешанные ссылки Смешанная ссылка имеет абсолютный столбец и относительную строку либо абсолютную строку и относительный столбец. Абсолютная ссылка на столбец принимает форму $A 1, $B 1 и так далее. Абсолютная ссылка на строку имеет форму A$1, B$1 и так далее. При изменении позиции ячейки, содержаной формулу, изменяется относительная ссылка, а абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам или вниз по столбцам относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при копировании или заполнении смешанной ссылки из ячейки A2 в B3 она будет меняться с =A$1 на =B$1.
Стиль трехмерных ссылок
Удобный способ для ссылки на несколько листов Трехмерные ссылки используются для анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка содержит ссылку на ячейку или диапазон, перед которой указываются имена листов. Excel в Интернете использует все таблицы, которые хранятся между начальным и конечним именами ссылки. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от листа 2 до листа 13 включительно.
При помощи трехмерных ссылок можно создавать ссылки на ячейки на других листах, определять имена и создавать формулы с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА, СТАНДОТКЛОНПА, ДИСПР, ДИСП.В, ДИСПА и ДИСППА.
Трехмерные ссылки нельзя использовать в формулах массива.
Трехмерные ссылки нельзя использовать вместе с оператор пересечения (один пробел), а также в формулах с неявное пересечение.
<c0>Что происходит при перемещении, копировании, вставке или удалении листов</c0>. Нижеследующие примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для суммирования значений в ячейках с A2 по A5 на листах со второго по шестой.
Вставка или копирование Если вставить листы между листами 2 и 6, Excel в Интернете будет включать в расчет все значения из ячеек с A2 по A5 на добавленных листах.
Удалить Если удалить листы между листами 2 и 6, Excel в Интернете вы вычислите их значения.
Переместить Если переместить листы между листами 2 и 6 в место за пределами диапазона, на который имеется ссылка, Excel в Интернете удалит их значения из вычислений.
Перемещение конечного листа Если переместить лист 2 или 6 в другое место книги, Excel в Интернете скорректирует сумму с учетом изменения диапазона листов.
Удаление конечного листа Если удалить лист 2 или 6, Excel в Интернете скорректирует сумму с учетом изменения диапазона листов между ними.
Стиль ссылок R1C1
Можно использовать такой стиль ссылок, при котором нумеруются и строки, и столбцы. Стиль ссылок R1C1 удобен для вычисления положения столбцов и строк в макросах. В стиле R1C1 Excel в Интернете указывает на расположение ячейки с помощью R, за которым следует номер строки, и C, за которым следует номер столбца.
относительная ссылка на ячейку, расположенную на две строки выше в том же столбце
Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее
Абсолютная ссылка на ячейку, расположенную во второй строке второго столбца
Относительная ссылка на строку, расположенную выше текущей ячейки
Абсолютная ссылка на текущую строку
При записи макроса Excel в Интернете некоторые команды с помощью стиля ссылок R1C1. Например, если записать команду (например, нажать кнопку "Автоумма"), чтобы вставить формулу, в которую добавляется диапазон ячеек, Excel в Интернете записи формулы со ссылками с помощью стиля R1C1, а не A1.
Использование имен в формулах
Можно создавать определенные имена для представления ячеек, диапазонов ячеек, формул, констант и Excel в Интернете таблиц. Имя — это значимое краткое обозначение, поясняющее предназначение ссылки на ячейку, константы, формулы или таблицы, так как понять их суть с первого взгляда бывает непросто. Ниже приведены примеры имен и показано, как их использование упрощает понимание формул.
В этом учебном материале по Excel мы рассмотрим примеры того, как подсчитать ячейки между двумя числами.
Основная формула
Описание
Для того, чтобы подсчитать количество ячеек, содержащих значения между двумя числами находящимися в диапазоне ячеек, вы можете использовать функцию СЧЁТЕСЛИМН. В основном виде формулы (выше) диапазон представляет собой диапазон ячеек, содержащих числа. Ячейка A1 представляет нижнюю границу, а B1 представляет верхнюю границу чисел, которые вы хотите подсчитать. В показанном примере формула в G5, ниже, имеет следующий вид:
Пояснение
Цель этого примера - подсчитать числа, попадающие в определенные диапазоны. Нижнее значение берется из столбца "Начало", а верхнее значение - из столбца "Конец". Для каждого диапазона мы хотим включить нижнее значение, но исключить верхнее значение. Для удобства подсчитываемые числа находятся в данных именованного диапазона.
Формула, используемая для решения этой проблемы, основана на функции СЧЁТЕСЛИМН, которая предназначена для подсчета ячеек, удовлетворяющих нескольким условиям.
СЧЁТЕСЛИМН принимает условия как пара диапазон/условие. Первая пара диапазон/условие проверяет значения в данных, которые больше или равны (>=) значению "Начало" в столбце E:
Поскольку мы предоставляем одинаковый диапазон для обоих условий, каждая ячейка в данных должна соответствовать обоим условиям, чтобы быть включенной в окончательный подсчет. Обратите внимание, что в обоих случаях нам нужно объединить ссылку на ячейку с логическим оператором. Это особенность статистических функций в Excel. Поскольку формула копируется в столбец G, она возвращает количество чисел, попадающих в диапазон, определенный столбцами E и F.
Альтернатива СЧЁТЕСЛИ
Если у вас более старая версия Excel, в которой нет функции СЧЁТЕСЛИМН, вы можете использовать функцию СЧЁТЕСЛИ следующим образом:
=СЧЁТЕСЛИ(данные;">="&E5)-СЧЁТЕСЛИ(данные;"> crayon-main" style="">
= СЧЁТЕСЛИ ( данные ; "> crayon-o">& E5 ) - СЧЁТЕСЛИ ( данные ; "> crayon-o">& F5 )Первый СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые больше или равны "Начало" (который по определению также включает значения, превышающие значение "Конец", поскольку "Конец" больше, чем "Начало"). Второй СЧЁТЕСЛИ подсчитывает количество ячеек со значениями, большими или равными значению "Конец". Это второе число вычитается из первого числа, что дает окончательный результат - количество ячеек, содержащих значения между "Начало" и "Конец".
Если вы хотите проверить, находится ли значение ячейки между двумя значениями в Excel, вам помогут следующие методы.
Проверьте, находится ли значение ячейки между двумя значениями с помощью формулы
Например, вам нужно проверить, находится ли значение в ячейке B2 между значениями в ячейках A2 и A3. Для этого примените следующую формулу.
1. Выберите пустую ячейку, в которой необходимо отобразить результат, введите формулу =IF(AND(B2>A2,B2<A3),"Yes","No") в Панель формул и затем нажмите Enter ключ. Смотрите скриншот:
Если значение в ячейке B2 находится между A2 и A3, вы получите результат «Да», в противном случае отобразится «Нет».
Внимание: Если значения ячеек A2 больше A3, вы должны поменять местами символы> и <в формуле как =IF(AND(B2<A2,B2>A3),"Yes","No") , см. снимок экрана:
Проверьте, находится ли значение ячейки между двумя значениями с помощью Kutools for Excel
Y Вы можете легко проверить, находится ли значение ячейки между двумя значениями с помощью Выбрать определенные ячейки полезности Kutools for Excel без использования формул. Пожалуйста, сделайте следующее.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Как показано в примере выше, в этом разделе нажмите Kutools > Выберите > Выбрать определенные ячейки для включения утилиты. Смотрите скриншот:
2. А в Выбрать определенные ячейки диалоговое окно, вам необходимо:
- 2.1 Щелкните значок в Выберите ячейки в этом диапазоне раздел для выбора ячейки B2 (ячейка, которую вы хотите проверить, находится ли она между двумя значениями);
- 2.2 Выберите Ячейка вариант в Тип выбора раздел;
- 2.3 В Конкретный тип раздел, выберите Больше из первого раскрывающегося списка введите число 30 в текстовое поле. Затем проверьте Разумеется, вариант, выберите Менее во втором раскрывающемся списке и введите 50 в текстовое поле;
- 2.4 Щелкните значок OK кнопка. Смотрите скриншот:
3. Если указанное значение ячейки находится между двумя значениями, вы получите диалоговое окно со следующим снимком экрана 1, показанным. В противном случае появится диалоговое окно, в котором сообщается, что нет ячеек, соответствующих критериям скриншота 2 ниже. Пожалуйста, нажмите кнопку ОК, чтобы закрыть его.
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Англоязычный вариант: =SUM(5; 5) или =SUM(A1; B1) или =SUM(A1:B5)
Функция СУММ позволяет вычислить сумму двух или более чисел. В этой формуле вы также можете использовать ссылки на ячейки.
С помощью формулы вы можете:
- посчитать сумму двух чисел c помощью формулы: =СУММ(5; 5)
- посчитать сумму содержимого ячеек, сссылаясь на их названия: =СУММ(A1; B1)
- посчитать сумму в указанном диапазоне ячеек, в примере во всех ячейках с A1 по B6: =СУММ(A1:B6)
Англоязычный вариант: =COUNT(A1:A10)
Данная формула подсчитывает количество ячеек с числами в одном ряду. Если вам необходимо узнать, сколько ячеек с числами находятся в диапазоне c A1 по A30, нужно использовать следующую формулу: =СЧЁТ(A1:A30).
СЧЁТЗ
Англоязычный вариант: =COUNTA(A1:A10)
С помощью данной формулы можно подсчитать количество заполненных ячеек в одном ряду, то есть тех, в которых есть не только числа, но и другие знаки. Преимущество формулы – её можно использовать для работы с любым типом данных.
ДЛСТР
Англоязычный вариант: =LEN(A1)
Функция ДЛСТР подсчитывает количество знаков в ячейке. Однако, будьте внимательны – пробел также учитывается как знак.
СЖПРОБЕЛЫ
Англоязычный вариант: =TRIM(A1)
Данная функция помогает избавиться от пробелов, не включая при этом пробелы между словами. Эта опция может быть чрезвычайно полезной, особенно в тех ситуациях, когда вы вносите в таблицу данные из другого источника и при вставке появляются лишние пробелы.
Мы добавили лишний пробел после фразы “Я люблю Excel”. Формула СЖПРОБЕЛЫ убрала его, в этом вы можете убедиться, взглянув на количество знаков с использованием формулы и без.
ЛЕВСИМВ, ПСТР и ПРАВСИМВ
=ЛЕВСИМВ(адрес_ячейки; количество знаков)
=ПРАВСИМВ(адрес_ячейки; количество знаков)
=ПСТР(адрес_ячейки; начальное число; число знаков)
Англоязычный вариант: =RIGHT(адрес_ячейки; число знаков), =LEFT(адрес_ячейки; число знаков), =MID(адрес_ячейки; начальное число; число знаков).
Эти формулы возвращают заданное количество знаков текстовой строки. ЛЕВСИМВ возвращает заданное количество знаков из указанной строки слева, ПРАВСИМВ возвращает заданное количество знаков из указанной строки справа, а ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции.
Мы использовали ЛЕВСИМВ, чтобы получить первое слово. Для этого мы ввели A1 и число 1 – таким образом, мы получили «Я».
Мы использовали ПСТР, чтобы получить слово посередине. Для этого мы ввели А1, поставили 3 как начальное число и затем ввели число 6 – таким образом, мы получили «люблю» из фразы «Я люблю Excel».
Мы использовали ПРАВСИМВ, чтобы получить последнее слово. Для этого мы ввели А1 и число 6 – таким образом, мы получили слово «Excel» из фразы «Я люблю Excel».
Формула: =ВПР(искомое_значение; таблица; номер_столбца; тип_совпадения)
Англоязычный вариант: =VLOOKUP (искомое_значение; таблица; номер_столбца; тип_совпадения)
Функция ВПР работает как телефонная книга, где по фрагменту известных данных – имени, вы находите неизвестные сведения – номер телефона. В формуле необходимо задать искомое значение, которое формула должна найти в столбце таблицы.
Например, у вас есть два списка: первый с паспортными данными сотрудников и их доходами от продаж за последний квартал, а второй – с их паспортными данными и именами. Вы хотите сопоставить имена с доходами от продаж, но, делая это вручную, можно легко ошибиться.
- В первом списке данные записаны с А1 по В13, во втором – с D1 по Е13.
- В ячейке B17 поставим формулу: =ВПР(B16; A1:B13; 2; ЛОЖЬ)
- B16 = искомое значение, то есть паспортные данные. Они имеются в обоих списках.
- A1:B13 = таблица, в которой находится искомое значение.
- 2 – номер столбца, где находится искомое значение.
- ЛОЖЬ – логическое значение, которое означает то, что вам требуется точное совпадение возвращаемого значения. Если вам достаточно приблизительного совпадения, указываете ИСТИНА, оно также является значением по умолчанию.
Эта формула не такая простая, как предыдущие, тем не менее она очень полезна в работе.
Формула: =ЕСЛИ(логическое_выражение; "текст, если логическое выражение истинно; "текст, если логическое выражение ложно")
Англоязычный вариант: =IF(логическое_выражение; "текст, если логическое выражение истинно; "текст, если логическое выражение ложно")
Когда вы проводите анализ большого объёма данных в Excel, есть множество сценариев для взаимодействия с ними. В зависимости от каждого из них появляется необходимость по‑разному воздействовать на данные. Функция «ЕСЛИ» позволяет выполнять логические сравнения значений: если что‑то истинно, то необходимо сделать это, в противном случае сделать что‑то ещё.
Снова обратимся к примеру из сферы продаж: допустим, что у каждого продавца есть установленная норма по продажам. Вы использовали формулу ВПР, чтобы поместить доход рядом с именем. Теперь вы можете использовать оператор «ЕСЛИ», который будет выражать следующее: «ЕСЛИ продавец выполнил норму, вывести выражение «Норма выполнена», если нет, то «Норма не выполнена».
В примере с ВПР у нас был доход в столбце B и имя человека в столбце E. Мы можем поместить квоту в столбце C, а следующую формулу – в ячейку D1:
=ЕСЛИ(B1>C1; "Норма выполнена"; "Норма не выполнена")
Функция «ЕСЛИ» покажет нам, выполнил ли первый продавец свою норму или нет. После можно скопировать и вставить эту формулу для всех продавцов в списке, значение автоматически изменится для каждого работника.
СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ
Формула: =СУММЕСЛИ(диапазон; условие; диапазон_суммирования) =СЧЁТЕСЛИ(диапазон; условие)
=СРЗНАЧЕСЛИ(диапазон; условие; диапазон_усреднения)
Англоязычный вариант: =SUMIF(диапазон; условие; диапазон_суммирования), =COUNTIF(диапазон; условие), =AVERAGEIF(диапазон; условие; диапазон_усреднения)
Эти формулы выполняют соответствующие функции – СУММ, СЧЁТ, СРЗНАЧ, если выполнено заданное условие.
Формулы с несколькими условиями – СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИМН – выполняют соответствующие функции, если все указанные критерии соответствуют истине.
Используя функции на предыдущем примере, мы можем узнать:
СУММЕСЛИ – общий доход только для продавцов, выполнивших норму.
СРЗНАЧЕСЛИ – средний доход продавца, если он выполнил норму.
СЧЁТЕСЛИ – количество продавцов, выполнивших норму.
Конкатенация
Формула: =(ячейка1&" "&ячейка2)
За этим причудливым словом скрывается объединение данных из двух и более ячеек в одной. Сделать объединение можно с помощью формулы конкатенации или просто вставив символ & между адресами двух ячеек. Если в ячейке A1 находится имя «Иван», в ячейке B1 – фамилия «Петров», их можно объединить с помощью формулы =A1&" "&B1. Результат – «Иван Петров» в ячейке, где была введена формула. Обязательно оставьте пробел между " ", чтобы между объединёнными данными появился пробел.
Формула конкатенации даёт аналогичный эффект и выглядит так: =ОБЪЕДИНИТЬ(A1;" "; B1) или в англоязычном варианте =concatenate(A1;" "; B1).
Кстати, все перечисленные формулы можно применять и в Google‑таблицах.
Эта статья является лишь верхушкой айсберга в изучении Excel. Для профессионального использования программы рекомендуем учится у профессионалов на курсах по Microsoft Excel.
В программе Эксель очень удобно делать различного рода расчеты, как простые (суммирование, умножение, деление), так и с использованием формул. Я уже рассказывала, как рассчитать сумму в Эксель, и статью можете прочесть на сайте. Разбирали мы функции СУММЕСЛИ и СУММЕСЛИМН.
Сейчас давайте рассмотрим еще один простой вид расчета – вычитание. Покажу, как его применить к обычным числам, или к тем значениям, формат которых изменен на денежный, даты или время.
Обычные числа
Начнем с самого простого – использование программы в качестве калькулятора. Для этого выделите любую ячейку, поставьте в нее знак «=» (он вводится с клавиатуры), наберите первое число, ставьте «-» (он тоже на клавиатуре), набирайте второе и так все значения, которые необходимо отнять.
В конце нажмите «Enter» и расчет будет выполнен.
Если же у Вас на рабочем листе есть заполненные данные, которые требуется вычесть, тогда переходим на ячейку, в которой будет результат (А4). Поставьте в нее знак «=», а потом выделите сначала число от которого вычитаете (А3), ставьте «-», затем выделяем второе значение (В3).
Нажимайте «Enter» и появится результат. В строке формул можно посмотреть, из каких ячеек берутся данные для расчета.
Если с вычитанием чисел понятно, тогда перейдем к датам. Они должны быть вписаны в блоки и для каждой нужно указать числовой формат «Дата».
Давайте отнимем от нее определенное количество дней. Выберите любую ячейку (Е3), впишите в нее «=». Потом кликните по дате (Е1), поставьте минус и впишите нужное число дней.
В результате формат ячейки изменится, и в ней отобразится та дата, которая меньше изначальной на то значение, которое Вы указали.
Читайте еще: Как обрезать видео на компьютере без программ 
Для того чтобы отнять между собой две даты и узнать между ними разницу в днях, ставим в пустом блоке равно (Е4), выделяем позднее значение (Е1), потом раннее (D1).
Нажимаем «Enter» и получаем результат. То есть, между датами 5 октября и 12 сентября 23 дня.
Также для расчета разницы дат можно использовать формулу РАЗНДАТ(). У нее в скобках указываются следующие аргументы: сначала раннее потом позднее значение и в чем хотим получить результат (день, месяц, год).
Подробнее про последний аргумент. Если хотите получить результат в днях, укажите «d», месяцах – «m», годах – «y». Если нужны только дни, и не хотите учитывать года, тогда «yd» (например, разница 5 лет 23 дня, останется только 23), дни без учета месяцев – «md», только месяцы – «ym».
Отнимем для примера 12.09.2016 и 05.10.2017. Поставьте в ячейку «=» и наберите «РАЗНДАТ» со скобками. Внутри них ставим курсив и выделяем сначала раннюю (D1), затем позднюю (Е1) дату. В конце укажите нужную букву. В примере считаем разницу в полных месяцах. Между аргументами ставьте «;» без пробелов. Получилась формула:
Нажмем «Enter» и получим результат. Между указанными значениями у нас ровно 12 месяцев.
Воспользоваться формулой можно и иначе: сразу в строке формул укажите нужные значения, взяв их в двойные кавычки.
Время
Для расчета разницы во времени, нужно чтобы для них был выбран числовой формат «Время». У меня это ячейки G1 и H1. Та, в которой будет расчет (Н3), может быть и общего формата. Ставим в нее равно и вычитаем от большего значения времени меньшее.
После нажатия «Enter» отобразится разница и у ячейки изменится формат.
Если нужна разница в минутах, тогда ее можно рассчитать. Получившееся количество часов (10) умножьте на 60 и прибавьте минуты (23).
Получилось 623 минуты.
Денежные единицы
Чтобы отнять между собой значения в ячейках, где выбран денежный формат, используется самый простой способ. Выберите пустой блок, поставьте в нем знак равенства и отнимете значения, выделяя их по очереди.
После нажатия уже знакомой кнопки, получим значение, после которого также будет стоять символ выбранной денежной единицы.
Вот такими способами получится посчитать разницу между числами, датами, временем, или денежными единицами в Эксель.
Читайте также: