Лабораторная работа Г. Нижнекамск Как задать простое логическое условие в Excel
 Скачать 3.3 Mb.
|
|
Подсчет количества отличий двух диапазонов в Excel Формула массива, представленная на рисунке ниже, позволяет подсчитать количество различий в двух диапазонах:  Рис. 247 - Подсчет количества отличий двух диапазонов в Excel Данная формула сравнивает соответствующие значения двух диапазонов. Если они равны, функция ЕСЛИ возвращает ноль, а если не равны – единицу. В итоге получается массив, который состоит из нулей и единиц. Затем функция СУММ суммирует значения данного массива и возвращает результат. Необходимо, чтобы оба сравниваемых диапазона имели одинаковый размер и ориентацию. Транспонирование массива в Excel Вспомним предыдущий пример и попробуем усложнить задачу. К примеру, требуется сравнить диапазоны в Excel, которые имеют одинаковый размер, но разную ориентацию – один горизонтальный, а другой вертикальный. В этом случае на помощь придет функция ТРАНСП, которая позволяет транспонировать массив. Теперь формула из прошлого примера, немножко усложнится:  Рис. 248 - Транспонирование массива в Excel Транспонировать массив в Excel – значит изменить его ориентацию, а точнее заменить строки столбцами, а столбцы строками. Суммирование округленных значений в Excel На рисунке ниже представлены товары, цена которых указана в евро, а также их количество и итоговая стоимость в рублях. В ячейке D9 отображается общая сумма всего заказа.  Рис. 249 - Суммирование округленных значений в Excel Если изменить форматирование в диапазоне D4:D8, то становится видно, что значения в этих ячейках не округлены, а всего лишь визуально отформатированы. Соответственно, мы не можем быть уверенны в том, что сумма в ячейке D9 является точной. В Excel существует, как минимум, два способа исправить эту погрешность. 1. Ввести в ячейки D4:D8 уже округленные значения. Формула массива будет выглядеть следующим образом:  Рис. 250 - Суммирование округленных значений в Excel 2. Использовать в ячейке D9 формулу массива, которая сначала округляет значения, а затем суммирует их.  Рис. 251 - Суммирование округленных значений в Excel Теперь мы можем быть уверенными в том, что сумма в ячейке D9 соответствует действительности. Как видите, сумма до и после округления немного отличается. В нашем случае это всего лишь одна копейка. Наибольшее или наименьшее значение по условию На рисунке ниже представлен фрагмент таблицы продаж, в которой 1231 позиция. Наша задача посчитать максимальную продажу, которую осуществил заданный продавец.  Рис. 252 - Наибольшее или наименьшее значение по условию Пусть в ячейке G3 мы будем задавать фамилию продавца, тогда формула массива будет выглядеть следующим образом:  Рис. 253 - Наибольшее или наименьшее значение по условию В данном случае функция ЕСЛИ сравнивает значения диапазона B3:B1234 c заданной фамилией. Если фамилии совпадают, то возвращается сумма продажи, а если нет – ЛОЖЬ. В итоге функция ЕСЛИ формирует одномерный вертикальный массив, который состоит из сумм продаж указанного продавца и значений ЛОЖЬ, всего 1231 позиция. Затем функция МАКС обрабатывает получившийся массив и возвращает из него максимальную продажу. В нашем случае это:  Рис. 254 - Наибольшее или наименьшее значение по условию Если массив содержит логические значения, то функции МАКС и МИН их игнорируют. Чтобы вывести минимальную продажу, воспользуемся этой формулой:  Рис. 255 - Наибольшее или наименьшее значение по условию Данная формула позволяет вывести 5 наибольших продаж указанного продавца:  Рис. 256 - Наибольшее или наименьшее значение по условию Итак, в данном уроке мы рассмотрели несколько интересных примеров применения формул массива в Excel. Подходы к редактированию формул массива в Excel Использование формул массива в Excel имеет целый ряд преимуществ. Даже сложность работы с ними, зачастую можно принять как преимущество, поскольку сложность сразу отсеивает дилетантов. В этот уроке мы разберем два подхода к редактированию формул массива в Excel. Они не идеальны, но, возможно, Вам пригодятся. Подход №1 На рисунке ниже представлена формула массива, размер которой необходимо расширить с C3:C8 до C1:C10. Чтобы проделать это, выполните следующие действия:  Рис. 257 - Подходы к редактированию формул массива в Excel (Подход №1) 1. Выделите диапазон ячеек, которые будет содержать новый массив. Здесь главное, чтобы новый массив перекрывал собой старый, иначе Excel не даст закончить редактирование и выдаст предупреждение. В нашем случае это диапазон C1:C10. 2. Далее нужно удостовериться, что активной является ячейка, принадлежащая старому массиву. Это позволит сохранить структуру формулы, пусть и с «ложными» ссылками на ячейки. Поскольку в нашем случае активной является пустая ячейка, выделим подходящую. Допустим C3: Сделать ячейку активной в выделенном диапазоне можно с помощью клавиши Enter. 3. Перейдем в режим редактирования формулы (клавиша F2). На данном этапе можно перезадать области, участвующие в формировании массива. В нашем случае мы перезададим обе области:  Рис. 258 - Подходы к редактированию формул массива в Excel (Подход №1) 4. Сохраним изменения в формуле, нажав комбинацию клавиш Ctrl+Shift+Enter. Формула массива будет обновлена. Подход №2 Второй подход можно применить как для уменьшения, так и для увеличения размера массива в Excel. Здесь главное, чтобы у старого и нового массивов была как минимум одна общая ячейка. На рисунке ниже представлен массив, диапазон которого необходимо уменьшить с С1:С10 до C1:C5. Чтобы проделать это, выполните следующие действия: 1. Выделите диапазон, содержащий формулу массива. 2. Нажмите клавишу F2, чтобы перейти в режим редактирования.  Рис. 259 - Рис. 258 - Подходы к редактированию формул массива в Excel (Подход №2) 3. Нажмите комбинацию клавиш Ctrl+Enter. Это действие вводит отдельную формулу в каждую из ячеек диапазона. Причем эти формулы не являются формулами массивов. Например, в ячейке С3 содержится следующая формула:  Рис. 260 - Подходы к редактированию формул массива в Excel (Подход №2) 4. Перезадайте диапазон, в котором будет находиться новый массив, и сделайте активной ячейку из старого массива. Это позволит сохранить формулу, пусть и с «ложными» ссылками. В нашем случае мы просто уменьшим размер диапазона до С1:C5:  Рис. 261 - Подходы к редактированию формул массива в Excel (Подход №2) 5. Снова перейдите в режим редактирования. На данном этапе можно перезадать области, участвующие в формировании массива. В нашем случае мы перезададим обе области:  Рис. 262 - Подходы к редактированию формул массива в Excel (Подход №2) 6. Нажмите комбинацию клавиш Ctrl+Shift+Enter, чтобы сохранить изменения. Размер массива будет уменьшен.  Рис. 263 - Подходы к редактированию формул массива в Excel (Подход №2) 7. Удалите лишние формулы из диапазона C6:C10. Как видите, оба подхода достаточно сложные и запутанные, поэтому их реальная применимость в Excel под большим вопросом. Все манипуляции, проведенные выше, сводятся к одному — сохранить формулу. А точнее ее структуру, поскольку ссылки в большинстве случаев приходится перезадавать заново. На много проще скопировать формулу из старого массива, затем удалить его и создать новый. |