Файл: Лабораторная работа Г. Нижнекамск Как задать простое логическое условие в Excel.docx
ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 03.12.2023
Просмотров: 141
Скачиваний: 1
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
, созданных с помощью команды Таблица.
Как видите, все перечисленные выше правила подчеркивают, что массив – это одно целое. Если не выполнить, хотя бы одно из вышеперечисленных правил, 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
Если необходимо найти наименьшие значения, просто замените функцию НАИБОЛЬШИЙ на НАИМЕНЬШИЙ.
Подсчет количества отличий двух диапазонов в 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 - Наибольшее или наименьшее значение по условию
Как видите, все перечисленные выше правила подчеркивают, что массив – это одно целое. Если не выполнить, хотя бы одно из вышеперечисленных правил, 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
Если необходимо найти наименьшие значения, просто замените функцию НАИБОЛЬШИЙ на НАИМЕНЬШИЙ.
Подсчет количества отличий двух диапазонов в 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 - Наибольшее или наименьшее значение по условию