Именованный диапазон в EXCEL

Обычно ссылки на диапазоны ячеек вводятся непосредственно в формулы, например =СУММ(А1:А10) . Другим подходом является использование в качестве ссылки имени диапазона. В статье рассмотрим какие преимущества дает использование имени.

Назовем Именованным диапазоном в MS EXCEL, диапазон ячеек, которому присвоено Имя (советуем перед прочтением этой статьи ознакомиться с правилами создания Имен ).

Преимуществом именованного диапазона является его информативность. Сравним две записи одной формулы для суммирования, например, объемов продаж: =СУММ($B$2:$B$10) и =СУММ(Продажи) . Хотя формулы вернут один и тот же результат (если, конечно, диапазону B2:B10 присвоено имя Продажи ), но иногда проще работать не напрямую с диапазонами, а с их именами.

Совет : Узнать на какой диапазон ячеек ссылается Имя можно через Диспетчер имен расположенный в меню Формулы/ Определенные имена/ Диспетчер имен .

Ниже рассмотрим как присваивать имя диапазонам. Оказывается, что диапазону ячеек можно присвоить имя по разному: используя абсолютную или смешанную адресацию .

Условное форматирование отличий в упорядоченных значениях
При желании можно применить к отличающимся ячейкам условное форматирование, делая заливку ячейки, меняя цвет текста и т.д. В этом случае нужно выбирать пункт «Условное форматирование», в выпадающем списке которого выбираем «Управление правилами».

10 наиболее полезных функций при анализе данных в Excel — ExcelGuide: Про Excel и не только

• выделите, диапазон B2:B10 на листе 1сезон ;
• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
• в поле Имя введите: Продажи ;
• в поле Область выберите лист 1сезон (имя будет работать только на этом листе) или оставьте значение Книга , чтобы имя было доступно на любом листе книги;
• убедитесь, что в поле Диапазон введена формула =’1сезон’!$B$2:$B$10
• нажмите ОК.

Теперь при наведении курсора на любую ячейку диапазона, к которой мы применили проверку данных, справа от неё появляется треугольник. При нажатии на этот треугольник открывается список вводимых данных, который подтягивается из перечня на другом листе.

Определить диапазон в Excel

Существуют три способа создания именованных диапазонов:

• путем ввода имени диапазона в поле Имя;
• путем выбора на вкладке ФОРМУЛЫ в группе Определенные имена инструмента Создать из выделенного;
• путем выбора на вкладке ФОРМУЛЫ в группе Определенные имена инструментов Присвоить имя или Диспетчер имен.

Для создания имени диапазона с помощью поля Имя (рис. 1.1) выделите ячейку или диапазон ячеек, которым требуется присвоить имя, установите курсор в поле Имя, введите имя диапазона, и нажмите клавишу . На рис. 1.1 ячейке В3 присвоено имя Старт.

Рис. 1.1. Создание имени диапазона путем выбора диапазона ячеек и ввода имени в поле Имя

Скачать заметку в формате Word или pdf, примеры в формате Excel

Рис. 1.3. При выборе имени диапазона отмечаются все ячейки, соответствующие этому диапазону

При нажатии клавиши открывается диалоговое окно Вставка имени, в котором отображаются имена всех диапазонов.

Присвоение имени означает, что вместо любой ссылки Старт в формуле будет автоматически подставлено значение из ячейки В3.

Предположим, что необходимо присвоить имя Данные прямоугольному диапазону ячеек A1:B5. Выделите диапазон ячеек A1:B5, введите с клавиатуры Данные в поле Имя и нажмите клавишу . Теперь с помощью формулы =СРЗНАЧ(Данные) можно вычислить среднее значение содержимого ячеек A1:B4 (рис. 1.4).

Рис. 1.4. Присвоение диапазону A1:B5 имени Данные и нахождение среднего значения именованного диапазона

Рис. 1.5. Присвоение имени несмежному диапазону ячеек

Рис. 1.6. Создание имен с помощью инструмента Создать из выделенного

Теперь имена в первом столбце выделенного диапазона связаны с ячейками во втором столбце выделенного диапазона. Таким образом, ячейке B6 присвоено имя диапазона СА, ячейка B7 имеет имя СО и т.д. Создавать имена таких диапазонов с помощью поля Имя было бы невероятно утомительно! Нажмите на стрелку раскрывающегося списка в поле Имя и убедитесь, что все имена диапазонов созданы.

Создание имен диапазонов с помощью инструмента Присвоить имя. Если на вкладке ФОРМУЛЫ в группе Определенные имена выбрать инструмент Диспетчер имен (и затем нажать кнопку Создать) или инструмент Присвоить имя, откроется диалоговое окно Создание имени (рис. 1.7).

Предположим, требуется присвоить имя область1 диапазону ячеек A2:B7. Введите область1 в поле Имя, переместите курсор в поле Диапазон, и выделите диапазон на листе или введите с клавиатуры =A2:B7. Нажмите кнопку OK для завершения присваивания.

При нажатии на стрелку раскрывающегося списка в поле Область можно выбрать строку Книга или любой лист в книге, указав тем самым область действия имени (рис. 1.8). К любым именам диапазонов можно добавить примечания. Очень полезная опция, если не очевидно, что подразумевает выбранное имя диапазона.

В Microsoft Excel 2013 существует простой способ изменения или удаления имен диапазонов. Перейдите на вкладку ФОРМУЛЫ, выберите группу Определенные имена и откройте Диспетчер имен. Появится список имен всех диапазонов (рис. 1.9).

Редактирование формул в диалоговых окнах

Что делать? Нажмите F2. [2] Клавиша F2 позволяет переключаться между режимом указания (ввод) и режимом редактирования (правка). В режиме редактирования стрелки действуют именно так, как при редактировании формулы. На рисунке ниже попытка двинуть курсор влево увенчалась успехом:

Несколько конкретных примеров использования имен диапазонов

1. Необходимо вычислить общий объем продаж в штатах Аризона, Калифорния, Монтана, Нью-Йорк и Нью-Джерси.

Если вы помните наизусть сокращенные наименования штатов, то можно использовать формулу =AZ+CA+MT+NY+NJ (рис. 1.10)

Рис. 1.10. Использование имен вычисления объема продаж в отдельных штатах

2. Необходимо определить среднюю доходность акций, казначейских векселей и облигаций.

Рис. 1.12. Подсказка при вводе в формуле имени диапазона

5. Как добиться отображения недавно созданных имен диапазонов в ранее созданных формулах?

Рассмотрим небольшую таблицу, содержащую формулы (рис. 1.14).

6. Можно ли вывести на лист Excel список всех имен диапазонов (и представляемых ими ячеек)?

Откройте окно Вставка имени с помощью клавиши и нажмите кнопку Все имена (рис. 1.15). На листе, начиная с текущей ячейки, появится список имен диапазонов и соответствующих им ячеек.

Рис. 1.15. Вывод на лист Excel список всех имен диапазонов (и представляемых ими ячеек)

Пример 1. Предполагаемый годовой доход вычисляется как кратный прошлогоднему доходу (рис. 1.16). Воспользуемся формулу =(1+прирост)*предыдущий_год (имя диапазона не может содержать пробел). Требуется вычислить доходы за 2012–2018 гг. с приростом 10% в год, начиная с базового уровня 300 млн. долларов в 2011 г.

Рис. 1.16. Для любой ячейки это имя указывает на ячейку, находящуюся над активной ячейкой

Пример 2. Для каждого дня недели дана почасовая оплата и количество отработанных часов (рис. 1.17). Вычислим зарплату за каждый день по формуле почасовая*часы.

Выберите строку 12 (щелкните слева на 12) и в поле Имя (рядом со строкой формул) введите имя почасовая. Выберите строку 13 и введите в поле Имя – часы. Если теперь в ячейку F14 ввести формулу =почасовая*часы и скопировать эту формулу в диапазон G14:L14, то в каждом столбце автоматически появится результат перемножения значений почасовой оплаты и отработанных часов.

Если вам интересно, предлагаю несколько более сложных примеров использования имен диапазонов: Создание пользовательских функций при помощи имен, Автоматическое обновление сводной таблицы.

• В Excel невозможно использовать в качестве имен диапазонов буквы r и c.
• Единственными символами, которые можно использовать в именах диапазонов, являются точка (.) и подчеркивание (_).
• При использовании инструмента Создать из выделенного пробелы в созданном имени автоматически будут заменены символами подчеркивания (_). Например, имя Product 1 будет создано как Product_1.
• Имена диапазонов не могут начинаться с цифр или выглядеть как ссылка на ячейку. Например, в качестве имен диапазонов невозможно использовать имена 3Q и A4. Кроме того, в Microsoft Excel 2013 имеется более 16 000 столбцов, и такие имена, как cat1, являются недопустимыми, поскольку существует ячейка с именем CAT1. Если попытаться присвоить ячейке имя CAT1, появится сообщение о том, что введено недопустимое имя. В случае необходимости используйте подчеркивание (_) и назовите ячейку cat1_.

Исходные данные находятся в файле Имена диапазонов. Задания.xlsx

1. На листе Задание 1 содержатся данные о ежемесячной доходности акций General Motors и Microsoft. Присвойте имена диапазонам, содержащим ежемесячную доходность для каждой акции, и вычислите среднемесячную доходность каждой акции.
2. На листе Задание 2 присвойте имя Красный диапазону, содержащему ячейки A1:B3 и A6:B8.
3. На листе Задание 3 в ячейки G5 и G6 введите широту и долготу любого города, а в ячейки G7 и G8 широту и долготу другого города. В ячейке G10 вычисляется расстояние между двумя городами. Определите имена диапазонов для широты и долготы каждого города и убедитесь, что эти имена отображаются в формуле для расчета расстояния.
4. На листе Задание 4 содержится количество акций для каждого вида акций и цена одной акции. Вычислите стоимость акций для каждого вида по формуле =количество*цена.
5. На листе Задание 5 создайте имя диапазона для расчета среднего значения продаж за последние пять лет. Измените формулы в ячейках Е14:Е20.

[1] При написании заметки использованы материалы книги Уэйн Л. Винстон. Microsoft Excel 2013. Анализ данных и бизнес-моделирование, глава 1. [2] При написании этого раздела использованы идеи книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 156. [3] У меня не получилось воспользоваться указанным методом, поэтому пришлось перенабрать формулы после присвоения имен. Но выплыла другая проблема. В списках есть одинаковые данные, отличающиеся только значком *. После выполнения формулы были отмечены, как совпадающие, и данные с * и без *. Что нужно поменять в формуле, чтобы она возвращала только точные совпадения?
Спасибо.

Как сравнить два столбца в Excel — методы сравнения данных Excel | Exceltip

Теперь в любой ячейке листа 1сезон можно написать формулу в простом и наглядном виде: =СУММ(Продажи) . Будет выведена сумма значений из диапазона B2:B10 .

Использование макроса VBA

Использование макросов для сравнения двух столбцов позволяет унифицировать процесс и сократить время на подготовку данных. Решение о том, какой результат сравнения необходимо отобразить, полностью зависит от вашей фантазии и навыков владения макросами. Ниже представлена методика, опубликованная на официальном сайте Микрософт.

Создайте новый модуль кода VBA и введите следующий код. О том, как писать макросы вы можете прочитать более подробно в статье написания VBA кода.

В данном коде переменной CompareRange присваивается диапазон со сравниваемым массивом. Затем запускается цикл, который просматривает каждый элемент в выделенном диапазоне и сравнивает его с каждым элементом сравниваемого диапазона. Если были найдены элементы с одинаковыми значениями, макрос заносит значение элемента в столбец С.

Чтобы использовать макрос, вернитесь на рабочий лист, выделите основной диапазон (в нашем случае, это ячейки A1:A11), нажмите сочетание клавиш Alt+F8. В появившемся диалоговом окне выберите макрос Find_Matches и щелкните кнопку выполнить.

После выполнения макроса, результат должен быть следующим:

Мы использовали смешанную адресацию B1500:B$10 (без знака $ перед названием столбца). Такая адресация позволяет суммировать значения находящиеся в строках 2, 3,…10, в том столбце, в котором размещена формула суммирования. Формулу суммирования можно разместить в любой строке ниже десятой (иначе возникнет циклическая ссылка).

Как изменить диапазон ячеек в excel

Обратите внимание, что EXCEL при создании имени использовал абсолютную адресацию $B200:$B$10 . Абсолютная ссылка жестко фиксирует диапазон суммирования: в какой ячейке на листе Вы бы не написали формулу =СУММ(Продажи) – суммирование будет производиться по одному и тому же диапазону B1:B10.

ЕСЛИОШИБКА

При работе с формулами в Excel, можно время от времени сталкиваться с различными ошибками. Так в примере ниже функция ВПР вернула ошибку #Н/Д из-за того, что в базе данных по ФИО нет искомой нами фамилии (более подробно об ошибке #Н/Д вы можете прочитать в этой статье: «Как исправить ошибку #Н/Д в Excel»)

Для обработки таких ситуаций отлично подойдет функция ЕСЛИОШИБКА. Ее синтаксис следующий:

— Значение, результат которого проверяется на ошибку.
— Значение, если ошибка — В случае, если в результате работы функции получаем ошибку, то выводится не ошибка, а данное значение.

В случае с нашим примером выше, мы можем предположить, что фамилия может быть некорректной, соответственно ЕСЛИОШИБКА вернет нам предупреждение, что бы мы проверили написание фамилии.

В примере выше, мы проверяем результат работы функции ВПР(E2;A1:C6;2;0) и в случае, если вернется ошибка, то выдаем сообщение «Проверьте фамилию!».

Порядок действий следующий, выделяем первый набор данных, именуемый у нас «Столбец 1», и в меню «Условное форматирование» выбираем пункт «Создать правило…». В появившемся окошке выбираем «Использовать формулу для определения форматируемых ячеек», вписываем необходимую формулу «=СЧЁТЕСЛИ($C$3:$D$12;A3)=0» и выбираем способ форматирования.

Как сравнить две таблицы в Excel? – Офисные программы

В ячейке С2 введите следующую формулу =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A2;$B500:$B$11;0));»»;A2) и протяните ее до ячейки С11. Данная формула последовательно просматривает наличие каждого элемента из столбца A в столбце B и возвращает значение элемента, если оно было найдено в столбце B.

Как сравнить две таблицы в Excel?

Сравнить таблицы в Excel нажатием одной кнопки к сожалению, не получится, и мало того, возможно для сравнения придется еще и некоторым образом подготовить данные, а также написать формулу.

Простое сравнение упорядоченных значений
В зависимости от необходимого результата подбирается и способ сравнения данных из таблиц. Самый простой способ, это сравнение двух на первый взгляд одинаковых столбцов для выявления строк, в которых это отличие все же есть. Сравнивать таким образом можно как числовые значения, так и текст.

Сравним два столбца цифровых значений, в которых отличие имеется только в нескольких ячейках. Записав простую формулу в соседнем столбце, условие равенства двух ячеек «=B3=C3», мы получим результат «ИСТИНА», если содержимое ячеек одинаковое, и «ЛОЖ», если содержимое ячеек отличается. Растянув формулу по всей высоте столбца сравниваемых значений очень легко будет найти отличающуюся ячейку.

Условное форматирование отличий в упорядоченных значениях
При желании можно применить к отличающимся ячейкам условное форматирование, делая заливку ячейки, меняя цвет текста и т.д. В этом случае нужно выбирать пункт «Условное форматирование», в выпадающем списке которого выбираем «Управление правилами».

В диспетчере правил выбираем пункт «Создать правило», а в создании правил выбираем «Использовать формулу для определения форматируемых ячеек». Теперь мы можем задать формулу «=$B3$C3» для определения форматируемой ячейки, и задать для нее формат, нажав на кнопку «Формат».

Теперь у нас имеется правило отбора ячеек, задано форматирование, и определен диапазон сравниваемый ячеек. После нажатия на кнопку «OK», заданное нами правило будет применено.

Сравнение и форматирование отличий в неупорядоченных значениях
Сравнение таблиц Эксель не ограничивается сравнением упорядоченных значений. Иногда приходится сравнивать диапазоны перемешанных значений, в которых необходимо определить вхождение одного значения в диапазон других значений.

Например, у нас имеется набор значений, оформленный в виде двух столбцов, и еще один такой же набор значений. В первом наборе у нас имеются все значения от 1 до 20, а во втором некоторые значения отсутствуют и продублированы другими значениями. Наша задача выделить условным форматированием в первом наборе значения, которых нет во втором наборе.

Порядок действий следующий, выделяем первый набор данных, именуемый у нас «Столбец 1», и в меню «Условное форматирование» выбираем пункт «Создать правило…». В появившемся окошке выбираем «Использовать формулу для определения форматируемых ячеек», вписываем необходимую формулу «=СЧЁТЕСЛИ($C$3:$D$12;A3)=0» и выбираем способ форматирования.

В нашей формуле используется функция «СЧЁТЕСЛИ», которая подсчитывает количество повторений значения из определенной ячейки «A3» в заданном диапазоне «$C$3:$D$12», которым выступает наш второй столбец. В качестве сравниваемой ячейки необходимо указывать первую ячейку из диапазона значений, к которым будет применяться форматирование.

После применения созданного правила все ячейки с неповторяющимися значениями в другом наборе значений будут выделены указанным цветом.

Вариантов сравнения таблиц в Экселе может быть бесчисленное множество, причем некоторые из них можно провести только при помощи надстройки VBA.

— Диапазон суммирования — область листа Эксель, из которой мы суммируем данные
— Диапазон условия 1 — Диапазон ячеек, которые мы проверяем на соответствие условию
— Условие 1 — Условие, которое проверяется на соответствие в Диапазоне 1.
Обратите внимание, что диапазонов условий и соответственно условий может быть столько, сколько вам нужно.

Именованный диапазон в EXCEL. Примеры и описание

Как уже говорилось выше, именованные массивы могут использоваться во время выполнения различных операций в Экселе: формулы, функции, специальные инструменты. Давайте на конкретном примере рассмотрим, как это происходит.

Использование именованных диапазонов в сложных формулах

Предположим, что имеется сложная (длинная) формула, в которой несколько раз используется ссылка на один и тот же диапазон:

Если нам потребуется изменить ссылку на диапазон данных, то это придется сделать 3 раза. Например, ссылку E2:E8 поменять на J14:J20.

Но, если перед составлением сложной формулы мы присвоим диапазону E2:E8 какое-нибудь имя (например, Цены), то ссылку на диапазон придется менять только 1 раз и даже не в формуле, а в Диспетчере имен!

Более того, при создании формул EXCEL будет сам подсказывать имя диапазона! Для этого достаточно ввести первую букву его имени.

Excel добавит к именам формул, начинающихся на эту букву, еще и имя диапазона!

В Microsoft Excel 2013 существует простой способ изменения или удаления имен диапазонов. Перейдите на вкладку ФОРМУЛЫ, выберите группу Определенные имена и откройте Диспетчер имен. Появится список имен всех диапазонов (рис. 1.9).

Excel. Имена диапазонов

• выделите, диапазон B2:B10на листе1сезон;
• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя;
• в поле Имя введите: Продажи;
• в поле Область выберите лист 1сезон(имя будет работать только на этом листе) или оставьте значение Книга, чтобы имя было доступно на любом листе книги;
• убедитесь, что в поле Диапазон введена формула =’1сезон’!$B$2:$B$10
• нажмите ОК.

— Диапазон суммирования — область листа Эксель, из которой мы суммируем данные
— Диапазон условия 1 — Диапазон ячеек, которые мы проверяем на соответствие условию
— Условие 1 — Условие, которое проверяется на соответствие в Диапазоне 1.
Обратите внимание, что диапазонов условий и соответственно условий может быть столько, сколько вам нужно.