ПРИМЕР ПРИМЕНЕНИЯ МАССИВА КОНСТАНТ В EXCEL
На рисунке ниже приведен список студентов, которые получили определенные оценки:
Наша задача перевести оценку из числового вида в ее словесное описание и вывести соответствующие значения в диапазоне C2:C7. В данном случае создавать отдельную табличку для хранения текстового описания оценок не имеет смысла, поэтому выгоднее создать следующий массив констант:
={"";"Неудовл.";"Удовл.";"Хорошо";"Отлино"}
В данном случае первый элемент массива содержит пустую строку, поскольку предполагается, что оценки 1 быть не может.
Тогда формула, возвращающая нужный нам результат, будет выглядеть следующим образом:
В этом примере функция ИНДЕКС возвращает значение элемента из массива констант, положение которого задано порядковым номером (оценкой).
Данная формула не является формулой массива, хоть она и содержит массив. Поэтому при ее вводе достаточно нажать клавишу Enter.
Конечно же, мы в силах скопировать данную формулу в остальные ячейки и получить нужный нам результат:
Но грамотнее будет использовать многоячеечную формулу массива. Выглядеть она будет следующим образом:
Мы можем пойти еще дальше и присвоить массиву констант имя. Имя назначается точно так же, как и обычной константе, через диалоговое окно Создание имени:
Не забывайте указывать знак равенства в поле Диапазон, иначе Excel воспримет массив как текстовую строку.
|
|
|
Теперь формула выглядит менее пугающей:
Как видите, в некоторых случаях массивы констант бывают даже очень полезны.
РЕДАКТИРОВАНИЕ ФОРМУЛ МАССИВА В EXCEL
В прошлых уроках мы разобрали основные понятия и сведения касаемо массивов в Excel. В этом уроке мы продолжим изучение формул массива, но с большим уклоном на их практическое применение. Итак, как же изменить уже существующую формулу массива в Excel?
ПРАВИЛА РЕДАКТИРОВАНИЯ ФОРМУЛ МАССИВА
Когда формула массива помещена в одну ячейку, то ее редактирование в Excel обычно не представляет особой сложности. Здесь главное не забыть закончить редактирование комбинацией клавиш Ctrl+Shift+Enter.
Если же формула многоячеечная, т.е. возвращает массив, то сразу возникают определенные трудности, особенно у начинающих пользователей. Давайте рассмотрим несколько правил, которые необходимо усвоить, прежде чем начать редактирование массива.
1. Нельзя изменять содержимое одной ячейки, содержащей формулу массива. Но к каждой ячейке можно применить свое форматирование.
2. Нельзя удалять ячейки, которые входят в формулу массива. Можно удалить только весь массив.
3. Нельзя перемещать ячейки, которые входят в формулу массива. Зато можно переместить весь массив.
|
|
|
4. Нельзя вставлять новые ячейки, в том числе строки и столбцы, в диапазон массива.
5. Нельзя использовать многоячеечные формулы массива в таблицах, созданных с помощью команды Таблица.
Как видите, все перечисленные выше правила подчеркивают, что массив – это одно целое. Если не выполнить, хотя бы одно из вышеперечисленных правил, Excel не даст отредактировать массив и выдаст следующее предупреждение:
ВЫДЕЛЕНИЕ МАССИВА В EXCEL
Если необходимо изменить формулу массива, то первое, что нужно сделать – это выделить диапазон, в котором содержится массив. В Excel существует, как минимум, 3 способа сделать это:
1. Выделить диапазон массива вручную, т.е. с помощью мыши. Это самый простой, но в ряде случаев абсолютно непригодный способ.
2. С помощью диалогового окна Выделить группу ячеек. Для этого выделите любую ячейку, которая принадлежит массиву:
А затем на вкладке Главная из раскрывающегося списка Найти и выделить выберите пункт Выделить группу ячеек.
Откроется диалоговое окно Выделить группу ячеек. Установите переключатель на пункт текущий массив и нажмите ОК.
Текущий массив будет выделен:
3. При помощи комбинации клавиш Ctrl+/. Для этого выберите любую ячейку массива и нажмите комбинацию.
|
|
|
КАК УДАЛИТЬ ФОРМУЛУ МАССИВА
Самое простое, что Вы можете сделать с массивом в Excel – это удалить его. Для этого достаточно выделить нужный массив и нажать клавишу Delete.
КАК ОТРЕДАКТИРОВАТЬ ФОРМУЛУ МАССИВА
На рисунке ниже представлена формула массива, которая складывает значения двух диапазонов. Из рисунка видно, что при вводе формулы мы допустили небольшую ошибку, наша задача ее исправить.
Чтобы отредактировать формулу массива, выполните следующие действия:
1. Выделите диапазон массива любым из известных Вам способов. В нашем случае это диапазон C1:C12.
2. Перейдите в режим редактирования формулы, для этого щелкните по строке формул или нажмите клавишу F2. Excel удалит фигурные скобки вокруг формулы массива.
3. Внесите необходимые корректировки в формулу:
4. А затем нажмите комбинацию клавиш Ctrl+Shift+Enter, чтобы сохранить изменения. Формула будет отредактирована.
ИЗМЕНЕНИЕ РАЗМЕРОВ ФОРМУЛЫ МАССИВА
Очень часто возникает необходимость уменьшить или увеличить количество ячеек в формуле массива. Скажу сразу, что дело это не простое и в большинстве случаев будет проще удалить старый массив и создать новый.
Прежде чем удалять старый массив, скопируйте его формулу как текст, а затем используйте ее в новом массиве. При громоздких формулах такой подход позволит сэкономить уйму времени.
|
|
|
Если же необходимо изменить расположение массива на рабочем листе, не меняя его размерность, просто переместите его как обычный диапазон.
ПРИМЕНЕНИЕ ФОРМУЛ МАССИВА В EXCEL
В этом уроке приведены практические примеры использования формул массива в Excel. Это самый минимум из того, что с их помощью можно сделать. Надеюсь, что большая часть этих примеров, обязательно пригодится для решения Ваших повседневных задач.
ПОДСЧЕТ КОЛИЧЕСТВА ЗНАКОВ В ДИАПАЗОНЕ ЯЧЕЕК
На рисунке ниже представлена формула, которая подсчитывает количество знаков, включая пробелы, в диапазоне A1:A10.
В данном примере функция ДЛСТР подсчитывает длину каждой текстовой строки из заданного диапазона, а функцияСУММ – суммирует эти значения.
НАИБОЛЬШИЕ И НАИМЕНЬШИЕ ЗНАЧЕНИЯ ДИАПАЗОНА В EXCEL
Следующая формула возвращает 3 наибольших значения диапазона A1:D6
Чтобы возвратить другое количество наибольших значений, достаточно изменить массив констант. Например, следующая формула возвращает уже 6 наибольших значений того же диапазона:
Если необходимо найти наименьшие значения, просто замените функцию НАИБОЛЬШИЙ на НАИМЕНЬШИЙ.
ПОДСЧЕТ КОЛИЧЕСТВА ОТЛИЧИЙ ДВУХ ДИАПАЗОНОВ В EXCEL
Формула массива, представленная на рисунке ниже, позволяет подсчитать количество различий в двух диапазонах:
Данная формула сравнивает соответствующие значения двух диапазонов. Если они равны, функция ЕСЛИ возвращает ноль, а если не равны – единицу. В итоге получается массив, который состоит из нулей и единиц. Затем функцияСУММ суммирует значения данного массива и возвращает результат.
Необходимо, чтобы оба сравниваемых диапазона имели одинаковый размер и ориентацию.
ТРАНСПОНИРОВАНИЕ МАССИВА В EXCEL
Вспомним предыдущий пример и попробуем усложнить задачу. К примеру, требуется сравнить диапазоны в Excel, которые имеют одинаковый размер, но разную ориентацию – один горизонтальный, а другой вертикальный. В этом случае на помощь придет функция ТРАНСП, которая позволяет транспонировать массив. Теперь формула из прошлого примера, немножко усложнится:
Транспонировать массив в Excel – значит изменить его ориентацию, а точнее заменить строки столбцами, а столбцы строками.
СУММИРОВАНИЕ ОКРУГЛЕННЫХ ЗНАЧЕНИЙ В EXCEL
На рисунке ниже представлены товары, цена которых указана в евро, а также их количество и итоговая стоимость в рублях. В ячейке D9 отображается общая сумма всего заказа.
Если изменить форматирование в диапазоне D4:D8, то становится видно, что значения в этих ячейках не округлены, а всего лишь визуально отформатированы. Соответственно, мы не можем быть уверенны в том, что сумма в ячейке D9 является точной.
В Excel существует, как минимум, два способа исправить эту погрешность.
1. Ввести в ячейки D4:D8 уже округленные значения. Формула массива будет выглядеть следующим образом:
2. Использовать в ячейке D9 формулу массива, которая сначала округляет значения, а затем суммирует их.
Теперь мы можем быть уверенными в том, что сумма в ячейке D9 соответствует действительности.
Как видите, сумма до и после округления немного отличается. В нашем случае это всего лишь одна копейка.
НАИБОЛЬШЕЕ ИЛИ НАИМЕНЬШЕЕ ЗНАЧЕНИЕ ПО УСЛОВИЮ
На рисунке ниже представлен фрагмент таблицы продаж, в которой 1231 позиция. Наша задача посчитать максимальную продажу, которую осуществил заданный продавец.
Пусть в ячейке G3 мы будем задавать фамилию продавца, тогда формула массива будет выглядеть следующим образом:
В данном случае функция ЕСЛИ сравнивает значения диапазона B3:B1234 c заданной фамилией. Если фамилии совпадают, то возвращается сумма продажи, а если нет – ЛОЖЬ. В итоге функция ЕСЛИ формирует одномерный вертикальный массив, который состоит из сумм продаж указанного продавца и значений ЛОЖЬ, всего 1231 позиция. Затем функция МАКС обрабатывает получившийся массив и возвращает из него максимальную продажу. В нашем случае это:
Если массив содержит логические значения, то функции МАКС и МИН их игнорируют.
Чтобы вывести минимальную продажу, воспользуемся этой формулой:
Данная формула позволяет вывести 5 наибольших продаж указанного продавца:
ПОДХОДЫ К РЕДАКТИРОВАНИЮ ФОРМУЛ МАССИВА В EXCEL
Использование формул массива в Excel имеет целый ряд преимуществ. Даже сложность работы с ними, зачастую можно принять как преимущество, поскольку сложность сразу отсеивает дилетантов. В этот уроке мы разберем два подхода к редактированию формул массива в Excel. Они не идеальны, но, возможно, Вам пригодятся.
ПОДХОД №1
На рисунке ниже представлена формула массива, размер которой необходимо расширить с C3:C8 до C1:C10.
Чтобы проделать это, выполните следующие действия:
1. Выделите диапазон ячеек, которые будет содержать новый массив. Здесь главное, чтобы новый массив перекрывал собой старый, иначе Excel не даст закончить редактирование и выдаст предупреждение. В нашем случае это диапазон C1:C10.
2. Далее нужно удостовериться, что активной является ячейка, принадлежащая старому массиву. Это позволит сохранить структуру формулы, пусть и с "ложными" ссылками на ячейки. Поскольку в нашем случае активной является пустая ячейка, выделим подходящую. Допустим C3:
Сделать ячейку активной в выделенном диапазоне можно с помощью клавиши Enter.
3. Перейдем в режим редактирования формулы (клавиша F2). На данном этапе можно перезадать области, участвующие в формировании массива. В нашем случае мы перезададим обе области:
4. Сохраним изменения в формуле, нажав комбинацию клавиш Ctrl+Shift+Enter. Формула массива будет обновлена.
ПОДХОД №2
Второй подход можно применить как для уменьшения, так и для увеличения размера массива в Excel. Здесь главное, чтобы у старого и нового массивов была как минимум одна общая ячейка.
На рисунке ниже представлен массив, диапазон которого необходимо уменьшить с С1:С10 до C1:C5.
Чтобы проделать это, выполните следующие действия:
1. Выделите диапазон, содержащий формулу массива.
2. Нажмите клавишу F2, чтобы перейти в режим редактирования.
3. Нажмите комбинацию клавиш Ctrl+Enter. Это действие вводит отдельную формулу в каждую из ячеек диапазона. Причем эти формулы не являются формулами массивов. Например, в ячейке С3 содержится следующая формула:
4. Перезадайте диапазон, в котором будет находиться новый массив, и сделайте активной ячейку из старого массива. Это позволит сохранить формулу, пусть и с "ложными" ссылками. В нашем случае мы просто уменьшим размер диапазона до С1:C5:
5. Снова перейдите в режим редактирования. На данном этапе можно перезадать области, участвующие в формировании массива. В нашем случае мы перезададим обе области:
6. Нажмите комбинацию клавиш Ctrl+Shift+Enter, чтобы сохранить изменения. Размер массива будет уменьшен.
7. Удалите лишние формулы из диапазона C6:C10.
Как видите, оба подхода достаточно сложные и запутанные, поэтому их реальная применимость в Excel под большим вопросом. Все манипуляции, проведенные выше, сводятся к одному - сохранить формулу. А точнее ее структуру, поскольку ссылки в большинстве случаев приходится перезадавать заново. На много проще скопировать формулу из старого массива, затем удалить его и создать новый.
КАК НАСТРОИТЬ ЛЕНТУ В EXCEL 2013
Не всем пользователям Microsoft Excel удобно работать с предустановленными на Ленте вкладками. Иногда гораздо практичнее создать свою собственную вкладку с требуемым набором команд. В данном уроке мы покажем Вам как это можно сделать в Excel.
Любой пользователь Excel можете настроить Ленту под свои нужды, создав необходимые вкладки с любым перечнем команд. Команды размещаются в группах, и у Вас есть возможность создать любое количество групп, чтобы настроить Ленту. При желании, команды можно добавлять прямо на предустановленные вкладки, предварительно создав пользовательскую группу.
1. Щелкните по Ленте правой кнопкой мыши и из выпадающего меню выберите пункт Настройка ленты.
2. В появившемся диалоговом окне Параметры Excel найдите и выберите Создать вкладку.
3. Убедитесь, что выделена Новая группа. Выберите команду и нажмите Добавить. Вы также можете перетаскивать команды напрямую в группы.
4. Добавив все необходимые команды, нажмите OK. Вкладка будет создана, а команды добавлены на Ленту.
Если Вы не нашли необходимую команду среди часто используемых, откройте раскрывающийся список Выбрать команды и выберите пункт Все команды.
РЕЖИМ СЕНСОРНОГО УПРАВЛЕНИЯ ЛЕНТОЙ В EXCEL 2013
В последнее время все больше людей работают в Excel с планшетных компьютеров и прочих устройств с сенсорным экраном. Это доставляет определенные неудобства, поскольку стандартный интерфейс Excel рассчитан в большей степени на работу с персональным компьютером. К счастью в Excel 2013 встроен инструмент, который с легкостью позволяет решить эту проблему.
Если Вы работаете в Excel на устройстве с сенсорным экраном, Вы можете запустить Режим сенсорного управления, чтобы создать больше свободного пространства на Ленте, упрощая запуск команд пальцами.
1. Щелкните стрелку справа от Панели быстрого доступа и из раскрывающегося меню выберите пункт Режим сенсорного управления или мыши.
2. Команда Режим сенсорного управления или мыши появится на Панели быстрого доступа.
3. В раскрывающемся меню команды выберите пункт Сенсорное управление.
4. Лента переключится в режим сенсорного управления, а размер иконок и расстояние между ними увеличится.
Чтобы отключить Режим сенсорного управления, щелкните команду Режим сенсорного управления или мыши и из раскрывающегося меню выберите пункт Мышь.
Дата добавления: 2018-02-15; просмотров: 1695; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!