Как поставить скобки в excel
Если вы еще не 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. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.
Использование ссылок в формулах
Ссылка указывает на ячейку или диапазон ячеек на сайте и сообщает 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
1. Ссылка на лист «Маркетинг».
2. Ссылка на диапазон ячеек с B1 по B10 включительно.
3. Ссылка на лист, отделенная от ссылки на диапазон значений.
Различия между абсолютными, относительными и смешанными ссылками
Смешанные ссылки Смешанная ссылка имеет абсолютный столбец и относительную строку либо абсолютную строку и относительный столбец. Абсолютная ссылка на столбец принимает форму $A 1, $B 1 и так далее. Абсолютная ссылка на строку имеет форму A$1, B$1 и так далее. При изменении позиции ячейки, содержаной формулу, изменяется относительная ссылка, а абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам или вниз по столбцам относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при копировании или заполнении смешанной ссылки из ячейки A2 в B3 она будет меняться с =A$1 на =B$1.
Стиль трехмерных ссылок
Удобный способ для ссылки на несколько листов Трехмерные ссылки используются для анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка содержит ссылку на ячейку или диапазон, перед которой указываются имена листов. Excel в Интернете использует все таблицы, которые хранятся между начальным и конечним именами ссылки. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от листа 2 до листа 13 включительно.
При помощи трехмерных ссылок можно создавать ссылки на ячейки на других листах, определять имена и создавать формулы с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА, СТАНДОТКЛОНПА, ДИСПР, ДИСП.В, ДИСПА и ДИСППА.
Трехмерные ссылки нельзя использовать в формулах массива.
Трехмерные ссылки нельзя использовать вместе с оператор пересечения (один пробел), а также в формулах с неявное пересечение.
Вставка или копирование Если вставить листы между листами 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, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Следующая простая формула может помочь вам добавить скобки вокруг текста в ячейке, пожалуйста, сделайте следующее:
1. Пожалуйста, введите эту формулу = "(" & A2 & ")" в пустую ячейку помимо ваших данных, например, C2, см. снимок экрана:
2, Затем нажмите Enter чтобы получить результат, а затем выберите ячейку C2, перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите применить к этой формуле, все значения ячеек были вставлены в скобки, см. снимок экрана:
Вставьте скобки вокруг текста в ячейке с кодом VBA
Если вы знакомы с кодом VBA, я могу создать код VBA для вас, чтобы решить эту задачу.
1, Удерживать ALT + F11 ключи в Excel, и он открывает Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.
Код VBA: вставьте скобки вокруг текста в ячейке
3, Затем нажмите F5 нажмите клавишу для выполнения этого кода, появится окно подсказки, напоминающее вам о выборе диапазона данных, который вы хотите добавить в скобки, см. снимок экрана:
4, Затем нажмите OK, все выбранные ячейки были вставлены скобками сразу.
Вставьте скобки вокруг текста в ячейке с помощью Kutools for Excel
Если вас интересуют другие удобные надстройки для Excel, Kutools for Excel могу оказать вам услугу своим Добавить текст функция, вы можете быстро добавить любые символы в содержимое ячейки в любом месте.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Выберите диапазон данных, в который вы хотите вставить скобки.
2. Нажмите Кутулс > Текст > Добавить текст, см. снимок экрана:
3. В Добавить текст диалоговом окне введите полуоткрытую скобку « ( ”В Текст и выберите Перед первым персонажем под Позиция, затем нажмите Применить кнопку, см. снимок экрана:
4. Перед каждым выбранным текстовым значением были вставлены полуоткрытые скобки, и диалоговое окно все еще открыто, продолжайте вводить половину закрывающей скобки “ ) ”В Текст поле и выберите После последнего символа , затем нажмите Ok кнопку, см. снимок экрана:
5. И скобки были вставлены в значения ячеек.
Примечание:
С этой Добавить текст вы также можете вставить скобки в любое место содержимого ячейки. Например, я хочу вставить скобки вокруг текста после четвертого символа ячейки, пожалуйста, сделайте следующее:
1. В Добавить текст диалоговом окне введите полуоткрытую скобку « ( ”В Текст коробка и выберите Указывать вариант, затем введите конкретный номер позиции, в которую вы хотите вставить скобку, а затем нажмите Применить кнопку, чтобы завершить этот шаг, см. снимок экрана:
2. Затем вставьте полузакрывающуюся скобку “ ) ”В Текст коробка и выберите После последнего символа вариант, и теперь вы можете просмотреть результаты в правом списке, см. снимок экрана:
3, Наконец, нажмите Ok кнопку, чтобы закрыть это диалоговое окно, и скобки были вставлены вокруг текста в определенном месте по мере необходимости.
Советы: Если вы проверите Пропускать нетекстовые ячейки в диалоговом окне, добавляемый текст не будет вставлен в нетекстовые ячейки.
Демо: вставьте скобки вокруг текста в ячейку с помощью Kutools for Excel
Kutools for Excel: с более чем 200 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 60 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Как в Excel вывести отрицательные числа в скобках?
Вы когда-нибудь замечали, что Excel может отображать отрицательные числа в скобках? Excel ' Формат ячеек функция может отлично завершить эту работу. Чтобы узнать больше об этой операции, прочтите следующую статью.
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Отображать отрицательные числа в скобках в Excel
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Предположим, у вас есть список чисел, который содержит как положительные, так и отрицательные числа, как показано на скриншоте ниже, и вы можете заключить в скобки отрицательные числа, выполнив следующие действия:
1. Выделите числовые ячейки, щелкните правой кнопкой мыши и выберите Формат ячеек из контекстного меню см. снимок экрана:
2. В Формат ячеек диалоговое окно под Число вкладку нажмите Число вариант, а затем выберите нужные десятичные разряды (обычно вам нужно выбрать самые большие десятичные разряды существующих данных, чтобы ваши числа не изменились). Затем щелкните (1234.210) в Отрицательные числа список, см. снимок экрана:
3. Затем нажмите OK чтобы закрыть это диалоговое окно, и выбранные отрицательные числа были отображены в скобках, см. снимки экрана:
Ноты:
1. После применения форматирования все числа отображаются как те же десятичные разряды, которые вы установили.
2. Если на вашем листе есть целые числа, вы можете установить Десятичные числа как 0 в Формат ячеек Диалог.
Как заключить все ячейки в кавычки или вставить кавычки вокруг текста в Excel?
Вставьте кавычки вокруг текстового значения для нескольких ячеек с формулами
Чтобы заключить все указанные ячейки в кавычки, вам могут помочь следующие простые формулы. Пожалуйста, сделайте следующее:
1. Введите эту формулу: = СИМВОЛ (34) & A1 & СИМВОЛ (34) в пустую ячейку, в этом примере, в ячейку C1, см. снимок экрана:
2. Затем перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, все значения ячеек заключены в двойные кавычки, см. Снимок экрана:
Ноты:
1. Вот еще одна формула, которая поможет вам вставить двойные кавычки вокруг текста ячейки: = "" "" & A1 & "" "" .
2. Чтобы вставить значения ячеек в одинарные кавычки, примените следующую формулу: = "'" & A1 & "'" .
Kutools for Excel's Добавить текст Функция может помочь вам добавить определенный символ или букву до или после всех ячеек, в то же время она также может вставить символ в определенную позицию текстовых строк.
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Вставьте кавычки вокруг текстового значения для нескольких ячеек с помощью Kutools for Excel
Работы С Нами Kutools for Excelмногофункциональный инструмент -Добавить текст, вы можете добавлять любые символы или буквы до, после или в определенной позиции значений ячеек.
После установки Kutools for Excel, выполните следующие действия:
1. Выберите значения ячеек, в которые вы хотите вставить кавычки.
2. Нажмите Кутулс > Текст > Добавить текст, см. снимок экрана:
3. В Добавить текст диалоговом окне введите первую половину двойной кавычки в Текст поле и выберите Перед первым персонажем под Позиция раздел, см. снимок экрана:
4. Затем нажмите Применить кнопку, введите последнюю половину двойной кавычки в Текст поле и выберите После последнего символа под Позиция раздел, см. снимок экрана:
5, Наконец, нажмите Ok и заключены двойные кавычки вокруг текстовой строки в выбранных ячейках.
Ноты:
1. Чтобы вставить одинарные кавычки в ячейки, вам просто нужно заменить двойные кавычки на одинарные.
2. С помощью этой утилиты вы также можете вставлять кавычки в определенную позицию текстового значения.
Демо: вставьте кавычки вокруг текстового значения для нескольких ячеек с помощью Kutools for Excel
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Без формул массива (array formulas ) можно обойтись, т.к. это просто сокращенная запись группы однотипных формул. Однако, у формул массива есть серьезное преимущество: одна такая формула может заменить один или несколько столбцов с обычными формулами.
Например, можно найти сумму квадратов значений из диапазона А2:A12 , просто записав в ячейке B14 формулу =СУММ(A2:A12^2) . Для сравнения: чтобы найти сумму квадратов, используя обычные формулы, нам потребуется дополнительный столбец для вычисления квадратов значений и одна ячейка для их суммирования (см. файл примера или диапазон B 2 :B13 на рисунке ниже).
В отличие от ввода обычных формул, после ввода формулы массива нужно нажать вместо ENTER комбинацию клавиш CTRL+SHIFT+ENTER (поэтому, иногда, формулы массива также называются формулами CSE — это первые буквы от названия клавиш, используемых для ввода C trl, S hift, E nter). После этого формула будет обрамлена в фигурные скобки < >(их не вводят с клавиатуры, они автоматически появляются после нажатия CTRL+SHIFT+ENTER ). Это обрамление показано на рисунке выше (см. Строку формул ).
Чтобы глубже понять формулы массива проведем эксперимент:
- выделим ячейку B13 , содержащую обычную формулу =СУММ($B$2:$B$12) ;
- в C троке формул выделим аргумент функции СУММ() , т.е. $B$2:$B$12 ;
- нажмем клавишу F9 , т.е. вычислим, выделенную часть формулы;
- получим – массив квадратов значений из столбца В . Массив – это просто набор неких элементов (значений).
Т.е. обычная функция СУММ() в качестве аргумента получила некий массив (или точнее ссылку на него).Теперь проведем тот же эксперимент с формулой массива:
- выделим ячейку, содержащую формулу массива =СУММ($A$2:$A$12^2) ;
- в строке формул выделим аргумент функции СУММ() , т.е. $A$2:$A$12^2 ;
- нажмем клавишу F9 , т.е. вычислим, выделенную часть формулы;
- получим – тот же массив, что и в первом случае.
Т.е. нажатие CTRL+SHIFT+ENTER заставило EXCEL перед суммированием произвести промежуточные вычисления с диапазоном ячеек (с массивом содержащихся в нем значений). Для самой функции СУММ() ничего не изменилось – она получила тот же массив, только предварительно вычисленный, а не прямо из диапазона ячеек, как в случае с обычной формулой. Понятно, что вместо функции СУММ() в формуле массива может быть использована любая другая функция MS EXCEL: СРЗНАЧ() , МАКС() , НАИБОЛЬШИЙ() и т.п.
Вышеприведенный пример иллюстрирует использование функции массива возвращающей единственное значение, т.е. результат может быть выведен в одной ячейке. Это достигается использованием функций способных «свернуть» вычисленный массив до одного значения ( СУММ() , СРЗНАЧ() , МАКС() ). Примеры таких функций массива приведены в статье Формулы массива, возвращающие одно значение .
Формулы массива также могут возвращать сразу несколько значений. Понятно, что для того чтобы отобразить такой результат необходимо задействовать целый диапазон ячеек. Примеры таких функций приведены в статье Формулы массива, возвращающие несколько значений .
Преимущества и недостатки формул массива рассмотрены в одноименной статье Формулы массива. Преимущества и недостатки .
В файле примера также приведено решение данной задачи функцией СУММПРОИЗВ() , которая зачастую не требует введения ее как формулы массива: =СУММПРОИЗВ($A$2:$A$12^2)
Здесь, при вводе формулы СУММПРОИЗВ() нажимать CTRL+SHIFT+ENTER необязательно.
ПРИМЕЧАНИЕ : При создании Именованных формул и правил Условного форматирования формулы массива нельзя ввести нажимая CTRL+SHIFT+ENTER . Эти формулы вводятся только в ячейки листа. Однако, если формуле массива присвоить Имя , то EXCEL «сообразит», что нужно с ней нужно делать. Например, если формуле =СУММ($A$2:$A$12^2) присвоить имя Сумма_квадратов, а затем в ячейке указать =Сумма_квадратов , то получим правильный результат.
Excel фигурные скобки в формуле
Эта статья поможет разобраться, как работают формулы массива в Excel. Помещённая в одну ячейку формула массива выполняет сразу несколько вычислений.
Без формулы массива
Не прибегая к помощи формулы массива, нам потребуется выполнить следующую последовательность действий, чтобы определить максимальное развитие в столбце Progress.
- Первым делом мы рассчитаем развитие для каждого студента.
С формулой массива
Нам не нужно использовать вспомогательный диапазон в столбце D. Excel может хранить данные в своей памяти. Диапазон данных, сохранённый в памяти Excel, называется массивом констант.
-
Мы уже знаем, что определить прогресс первого студента нам поможет формула, показанная на рисунке ниже.
Замечание: Формула массива в строке формул заключена в фигурные скобки <>. Не пытайтесь ввести их вручную, скобки исчезнут при попытке отредактировать формулу.
- Диапазон данных (массив констант) хранится в памяти Excel, не в диапазоне ячеек. Массив констант можно представить в таком виде: .
- Функция МАКС (MAX) использует этот массив как аргумент и возвращает результат 63.
Клавиша F9
При работе с формулами массива можно использовать режим просмотра содержимого массива констант.
-
В формуле выделите C2:C6-B2:B6, как показано на рисунке ниже.
Отлично! Элементы вертикального массива констант разделяются точкой с запятой (в англоязычной версии Excel) или двоеточием (в русифицированной версии Excel). Элементы горизонтального массива констант разделяются запятой (в англоязычной версии Excel) или точкой с запятой (в русифицированной версии Excel).
Высший пилотаж: массивы в Excel
Друзья, в предыдущем посте о об округлении чисел я обещал рассказать о формулах массивов. Это мощный и сложный инструмент, о котором знают и пользуются не многие. Такие формулы позволяют выполнять расчеты, которые нельзя выполнить другими средствами Эксель, снизить громоздкость таблиц, исключив промежуточные расчеты. Давайте же не буду томить Вас ожиданием, переходим к изучению материала.
Что такое формулы массивов в Эксель
Как следует из самого названия, формулы массивов – это вычисления, связанные с обработкой массивов (матриц) данных. Некоторые операции с массивами можно выполнить с помощью функций, а некоторые, можно посчитать только с применений формул массивов.
Например, чтобы посчитать среднее значение чисел в диапазоне А1:А10 , можно использовать функцию СРЗНАЧ(Диапазон)
Среднее значение в Excel
Как известно, функция СРЗНАЧ игнорирует пустые ячейки, но учитывает нулевые. А что если их не нужно учитывать? Вот тогда поможет формула массива. И мы с Вами посчитаем правильное среднее значение прямо сейчас! Вам необходимо понимать один факт: в формулах массивов вместо одного значения для расчетов можно указать целый диапазон.
- Обычно первым аргументом функции «ЕСЛИ» записывается условие. Например, «A1=0», и если значение в ячейке А1 равно нулю, выполняется второй аргумент, в противном случае – третий. Мы же передали функции ссылку не на одну ячейку, а на массив из десяти значений ( А1:А10 ). Функция «ЕСЛИ» проверит каждое из них и все нулевые заменит пустой строкой. Результатом будет массив из десяти чисел или пустых значений. Такой список функция СРЗНАЧ уже отработает правильно;
- Полученный массив передается в функцию СРЗНАЧ, и теперь она считает среднее значение чисел без учета нулей, т.к. их уже нет.
Как видите, результат вычисления получился другой. Несложно проверить, что именно это число мы ожидали получить.
Рассмотрим еще один пример, похожий на предыдущий.
У нас есть ежедневные продажи за 20 дней. Нужно посчитать сумму продаж в те дни, когда касса превысила 5000. В обычных условиях нам понадобится промежуточный расчет. Так, в колонке С мы проверяем, если продажи выше пяти тысяч – возвращаем эту сумму, если ниже – записываем ноль. Далее, в ячейке С23 суммируем все числа столбца С и получаем искомый результат.
Теперь посмотрите, как мы сделали все эти действия одновременно в одной лишь ячейке Е4 и получили такой же результат! Обратите внимание на простую формулу в строке формул:
Получается, промежуточный расчет делать и не нужно было, он лишь засоряет таблицу, усложняя структуру страницы. Очевидное преимущество за формулой массива, правда?
Сумма без учета ошибок
Отличный способ применения формул массивов – сложить числа в массиве, содержащем ошибки. Пусть в массиве В2:F5 у нас результаты вычислений, среди которых встречаются ошибки. Как сложить эти числа, игнорируя ошибки? Очень просто, вот вам формула: =СУММ(ЕСЛИОШИБКА(B2:F5;0)) . Здесь функция ЕСЛИОШИБКА проверит содержание каждой ячейки и заменит ( в памяти компьютера, не на листе ) ошибки на нули. Далее, функция СУММ просуммирует все обработанные значения и выдаст результат без учета ошибок.
Общий смысл применения формул таков, что Вы заменяете одно значение на массив, а Excel выполняет вычисления для каждого из элементов массива. Это нужно понимать и осознавать, тогда формулы массивов Вам будут по плечу. Очень практичными получаются формулы массивов с применением поименованных диапазонов вместо обычных ссылок.
Эксперты MS Excel выстраивают из подобных формул огромные комбинации, выполняя в одной ячейке столько операций, сколько обычные пользователи умещают на несколько листов. Так что, экспериментируйте, пробуйте разные варианты применения, и не забывайте: для завершения ввода нажимаем Ctr+Shift+Enter , вместо клавиши «Ввод».
Если что-то из темы статьи вам непонятно или не получается, задавайте вопросы в комментариях, будем разбираться вместе!
А в следующей публикации мы с вами будем рисовать диаграммы, а это слишком важная тема, чтобы ее пропустить, подписывайтесь на электронную рассылку и читайте свежие статьи пока горячи!
Читайте также: