Лабораторная работа Г. Нижнекамск Как задать простое логическое условие в Excel
 Скачать 3.3 Mb.
|
|
Многоячеечные формулы массива в 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. Нельзя использовать многоячеечные формулы массива в таблицах, созданных с помощью команды Таблица. Как видите, все перечисленные выше правила подчеркивают, что массив – это одно целое. Если не выполнить, хотя бы одно из вышеперечисленных правил, Excel не даст отредактировать массив и выдаст следующее предупреждение:  Рис. 237 - Правила редактирования формул массива Выделение массива в Excel Если необходимо изменить формулу массива, то первое, что нужно сделать – это выделить диапазон, в котором содержится массив. В Excel существует, как минимум, 3 способа сделать это: 1. Выделить диапазон массива вручную, т.е. с помощью мыши. Это самый простой, но в ряде случаев абсолютно непригодный способ. 2. С помощью диалогового окна Выделить группу ячеек. Для этого выделите любую ячейку, которая принадлежит массиву Затем на вкладке Главная из раскрывающегося списка Найти и выделить выберите пункт Выделить группу ячеек.  Рис. 238 - Выделение массива в Excel Откроется диалоговое окно Выделить группу ячеек. Установите переключатель на пункт текущий массив и нажмите ОК.  Рис. 239 - Выделение массива в Excel Текущий массив будет выделен 3. При помощи комбинации клавиш Ctrl+/. Для этого выберите любую ячейку массива и нажмите комбинацию. Как удалить формулу массива Самое простое, что Вы можете сделать с массивом в Excel – это удалить его. Для этого достаточно выделить нужный массив и нажать клавишу Delete. Как отредактировать формулу массива На рисунке ниже представлена формула массива, которая складывает значения двух диапазонов. Из рисунка видно, что при вводе формулы мы допустили небольшую ошибку, наша задача ее исправить.  Рис. 240 - Как отредактировать формулу массива Чтобы отредактировать формулу массива, выполните следующие действия: 1. Выделите диапазон массива любым из известных Вам способов. В нашем случае это диапазон C1:C12 2. Перейдите в режим редактирования формулы, для этого щелкните по строке формул или нажмите клавишу F2. Excel удалит фигурные скобки вокруг формулы массива.  Рис. 241 - Как отредактировать формулу массива 3. Внесите необходимые корректировки в формулу:  Рис. 242 - Как отредактировать формулу массива 4. А затем нажмите комбинацию клавиш Ctrl+Shift+Enter, чтобы сохранить изменения. Формула будет отредактирована.  Рис. 243 - Как отредактировать формулу массива Изменение размеров формулы массива Очень часто возникает необходимость уменьшить или увеличить количество ячеек в формуле массива. Скажу сразу, что дело это не простое и в большинстве случаев будет проще удалить старый массив и создать новый. Прежде чем удалять старый массив, скопируйте его формулу как текст, а затем используйте ее в новом массиве. При громоздких формулах такой подход позволит сэкономить уйму времени. Если же необходимо изменить расположение массива на рабочем листе, не меняя его размерность, просто переместите его как обычный диапазон. Применение формул массива в Excel В этом уроке приведены практические примеры использования формул массива в Excel. Это самый минимум из того, что с их помощью можно сделать. Надеюсь, что большая часть этих примеров, обязательно пригодится для решения Ваших повседневных задач. Подсчет количества знаков в диапазоне ячеек На рисунке ниже представлена формула, которая подсчитывает количество знаков, включая пробелы, в диапазоне A1:A10.  Рис. 244 - Подсчет количества знаков в диапазоне ячеек В данном примере функция ДЛСТР подсчитывает длину каждой текстовой строки из заданного диапазона, а функция СУММ – суммирует эти значения. Наибольшие и наименьшие значения диапазона в Excel Следующая формула возвращает 3 наибольших значения диапазона A1:D6  Рис. 245 - Наибольшие и наименьшие значения диапазона в Excel Чтобы возвратить другое количество наибольших значений, достаточно изменить массив констант. Например, следующая формула возвращает уже 6 наибольших значений того же диапазона:  Рис. 246 - Наибольшие и наименьшие значения диапазона в Excel Если необходимо найти наименьшие значения, просто замените функцию НАИБОЛЬШИЙ на НАИМЕНЬШИЙ. |