Файл: Лабораторная работа Г. Нижнекамск Как задать простое логическое условие в Excel.docx
ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 03.12.2023
Просмотров: 144
Скачиваний: 1
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
Многоячеечные формулы массива в Excel
В данном уроке мы познакомимся с многоячеечной формулой массива, разберем наглядный пример ее применения в Excel, а также отметим некоторые особенности использования. Если Вы мало знакомы с формулами массивов, то советуем сначала обратиться к уроку, где описаны базовые принципы работы с ними.
Применение многоячеечной формулы массива
На рисунке ниже представлена таблица с наименованием товара, его ценой и количеством. В ячейках D2:D6 вычисляется общая стоимость каждого вида товара (с учетом количества).
Рис. 220 - Применение многоячеечной формулы массива
В данном примере диапазон D2:D6 содержит пять формул. Многоячеечная формула массива позволяет вычислить тот же самый результат, используя одну единственную формулу. Чтобы воспользоваться формулой массива выполните следующие действия:
1. Выделите диапазон ячеек, где должны отображаться результаты. В нашем случае это диапазон D2:D6.
Рис. 221 - Применение многоячеечной формулы массива
2. Как и при вводе любой формулы в Excel, первым делом необходимо ввести знак равенства.
3. Выделите первый массив значений. В нашем случае это диапазон с ценами товаров B2:B6.
Рис. 223 - Применение многоячеечной формулы массива
4. Введите знак умножения и выделите второй массив значений. В нашем случае это диапазон с количеством товаров C2:C6.
Рис. 224 - Применение многоячеечной формулы массива
5. Если бы мы вводили обычную формулу в Excel, то закончили бы ввод нажатием клавиши Enter. Но поскольку это формула массива, то нужно нажать комбинацию клавиш Ctrl+Shift+Enter. Этим мы укажем Excel, что это не обычная формула, а формула массива, и он автоматически заключит ее в фигурные скобки.
Рис. 225 - Применение многоячеечной формулы массива
Excel автоматически заключает формулу массива в фигурные скобки. Если Вы вставите скобки вручную, Excel воспримет это выражение как обычный текст.
6. Обратите внимание, что все ячейки диапазона D2:D6 содержат абсолютно одинаковое выражение. Фигурные скобки, в которые оно заключено, говорят о том, что это формула массива.
7. Если бы мы при вводе формулы массива выделили меньший диапазон, например, D2:D4, то она вернула бы нам только первые 3 результата:
Рис. 226 - Применение многоячеечной формулы массива
8. А если больший диапазон, то в «лишних» ячейках оказалось бы значение #Н/Д (нет данных)
Когда мы умножаем первый массив на второй, перемножаются их соответствующие элементы (B2 с C2, B3 с C3, B4 с C4 и т.д.). В итоге образуется новый массив, который содержит результаты вычислений. Поэтому, чтобы получить корректный результат, размерности всех трех массивов должны быть соответствующими.
Преимущества многоячеечных формул массива
Использовать в Excel единую многоячеечную формулу массива в большинстве случаев предпочтительнее, чем несколько отдельных формул. Рассмотрим основные преимущества, которые она предлагает:
1. Используя многоячеечную формулу массива, Вы на 100% уверены, что все формулы в рассчитываемом диапазоне введены правильно.
2. Формула массива в большей степени защищена от случайного изменения, поскольку редактировать можно только весь массив в целом. Если Вы попробуете изменить часть массива, у Вас ничего не получится. Например, при попытке удалить формулу из ячейки D4, Excel выдаст следующее предупреждение
3. Вы не сможете вставить новые строки или столбцы в диапазон, где введена формула массива. Чтобы вставить новую строку или столбец, придется заново перезадавать весь массив. Данный пункт можно рассматривать как преимущество, так и недостаток.
Итак, в данном уроке Вы познакомились с многоячеечными формулами массивов и разобрали небольшой пример.
Одноячеечные формулы массива в Excel
В данном уроке мы познакомимся с одноячеечной формулой массива и разберем наглядный пример ее применения в Excel. Если Вы еще совсем мало знакомы с формулами массивов, то советуем сначала обратиться к уроку, где описаны базовые принципы работы с массивами в Excel.
Применение одноячеечной формулы массива
Если Вы читали урок о многоячеечных формулах массивов, то на рисунке ниже представлена уже знакомая Вам таблица. В этот раз наша задача подсчитать общую стоимость всех товаров.
Рис. 227 - Применение одноячеечной формулы массива
Мы, конечно же, можем поступить классическим способом и просто просуммировать значения из диапазона ячеек D2:D6. В итоге получится нужный результат:
Рис. 228 - Применение одноячеечной формулы массива
Но бывают ситуации, когда делать промежуточные вычисления (в нашем случае это диапазон D2:D6) не имеет никакого смысла, неудобно или вовсе нельзя. В таком случае на помощь приходит одноячеечная формула массива, которая позволит вычислить результат всего одной формулой. Чтобы ввести такую формулу массива в Excel, выполните следующие действия:
1. Выделите ячейку, где должен появиться результат:
2. Введите следующую формулу:
Рис. 229 - Применение одноячеечной формулы массива
3. Поскольку это формула массива, то ввод необходимо завершить нажатием комбинации Ctrl+Shift+Enter. В итоге мы получим результат, идентичный подсчитанному ранее.
Как же работает данная формула массива?
1. Данная формула сначала перемножает соответствующие значения двух диапазонов:
Рис. 230 - Как же работает данная формула массива
2. И на основе полученных данных создает новый вертикальный массив, который существует только в оперативной памяти компьютера:
Рис. 231 - Как же работает данная формула массива
3. Затем функция СУММ выполняет суммирование значений этого массива и возвращает результат.
Формулы массивов – это одно из самых сложных, и в тоже время полезных, средств в Microsoft Excel. Одноячеечные формулы массивов позволяют выполнять вычисления, которые невозможно выполнить никакими другими способами. В следующих уроках мы рассмотрим несколько таких примеров.
Итак, в данном уроке Вы познакомились с одноячеечными формулами массивов и разобрали пример решения простой задачи.
Массивы констант в Excel
В Microsoft Excel можно создавать массивы, которые не хранятся в диапазонах ячеек. Их принято называть массивами констант. В этом уроке Вы узнаете, что же такое массивы констант и как с ними работать в Excel.
Коротко о массивах констант
Чтобы создать массив констант, введите его элементы и заключите их в фигурные скобки. Например, на рисунке ниже представлен массив, состоящий из 6 констант:
={1;2;3;4;5;6}
Такой массив можно использовать в формулах Excel. Например, следующая формула суммирует значения этого массива:
=СУММ({1;2;3;4;5;6})
В формулах можно обрабатывать сразу несколько массивов. Например, следующая формула вернет максимальное значение, которое получится в результате сложения двух массивов констант:
=МАКС({1;2;3;4;5;6}+{7,8,9,10,11,12})
Массивы констант могут содержать числа, текст, логические значения и значения ошибки #Н/Д:
={12;"Текст";ИСТИНА;ЛОЖЬ;#Н/Д}
У Вас может возникнуть резонный вопрос: Зачем нужен такой массив? Отвечу на него в виде примера.
Пример применения массива констант в Excel
На рисунке ниже приведен список студентов, которые получили определенные оценки:
Рис. 232 - Пример применения массива констант в Excel
Наша задача перевести оценку из числового вида в ее словесное описание и вывести соответствующие значения в диапазоне C2:C7. В данном случае создавать отдельную табличку для хранения текстового описания оценок не имеет смысла, поэтому выгоднее создать следующий массив констант:
={"";"Неудовл.";"Удовл.";"Хорошо";"Отлино"}
В данном случае первый элемент массива содержит пустую строку, поскольку предполагается, что оценки 1 быть не может.
Тогда формула, возвращающая нужный нам результат, будет выглядеть следующим образом:
Рис. 233 - Пример применения массива констант в Excel
В этом примере функция ИНДЕКС возвращает значение элемента из массива констант, положение которого задано порядковым номером (оценкой).
Данная формула не является формулой массива, хоть она и содержит массив. Поэтому при ее вводе достаточно нажать клавишу Enter.
Конечно же, мы в силах скопировать данную формулу в остальные ячейки и получить нужный нам результат:
Рис. 234 - Пример применения массива констант в Excel
Но грамотнее будет использовать многоячеечную формулу массива. Выглядеть она будет следующим образом:
Мы можем пойти еще дальше и присвоить массиву констант имя. Имя назначается точно так же, как и обычной константе, через диалоговое окно Создание имени:
Рис. 235 - Пример применения массива констант в Excel
Не забывайте указывать знак равенства в поле Диапазон, иначе Excel воспримет массив как текстовую строку.
Теперь формула выглядит менее пугающей:
Рис. 236 - Пример применения массива констант в Excel
Как видите, в некоторых случаях массивы констант бывают даже очень полезны.
Итак, в данном уроке Вы познакомились с массивами констант и их применением в Excel.
Редактирование формул массива в Excel
В прошлых уроках мы разобрали основные понятия и сведения касаемо массивов в Excel. В этом уроке мы продолжим изучение формул массива, но с большим уклоном на их практическое применение. Итак, как же изменить уже существующую формулу массива в Excel?
Правила редактирования формул массива
Когда формула массива помещена в одну ячейку, то ее редактирование в Excel обычно не представляет особой сложности. Здесь главное не забыть закончить редактирование комбинацией клавиш Ctrl+Shift+Enter.
Если же формула многоячеечная, т.е. возвращает массив, то сразу возникают определенные трудности, особенно у начинающих пользователей. Давайте рассмотрим несколько правил, которые необходимо усвоить, прежде чем начать редактирование массива.
1. Нельзя изменять содержимое одной ячейки, содержащей формулу массива. Но к каждой ячейке можно применить свое форматирование.
2. Нельзя удалять ячейки, которые входят в формулу массива. Можно удалить только весь массив.
3. Нельзя перемещать ячейки, которые входят в формулу массива. Зато можно переместить весь массив.
4. Нельзя вставлять новые ячейки, в том числе строки и столбцы, в диапазон массива.
5. Нельзя использовать многоячеечные формулы массива в таблицах