Excel в этой формуле не указаны ссылка на диапазон или определенное имя
Обновлено: 20.05.2024
По умолчанию Excel автоматически присваивает именам диапазоны ячеек с абсолютным адресом ссылки. Такие имена наиболее часто используются в процессе работы. Но иногда нужно присвоить относительный адрес ссылки для имени.
Примеры имен с относительными ссылками
Имея прибыль в отдельных месяцах, посчитаем суммарную прибыль за квартал. Для этой задачи можно сложить значения выбранных ячеек, но значительно удобнее использовать имя с относительной адресацией.
- Составьте отчет, так как показано ниже на рисунке и перейдите в ячейку C4.
- Выберите инструмент: «Формулы»-«Определенные имена»-«Присвоить имя».
- В диалоговом окне: «Создание имени» введите в поле «Имя:» его название «квартал», а в поле «Диапазон:» вводим относительные адрес диапазона охватывающего прибыли трех месяцев первого квартала. Опцию в выпадающем списке «Область:» установите на свое усмотрение. С данным параметром вы знакомы из предыдущего урока: Область видимости имени на листе Excel.
- Напротив последнего месяца первого квартала (ячейка C4) введите формулу: =СУММ(квартал). Скопируйте ее во все ячейки напротив последних месяцев каждого квартала: C7, C10 и C13.
Примечание. Чтобы быстро и удобно изменить тип адреса в ссылках (с абсолютного на относительный), используйте клавишу F4.
Присвоить имя функции в Excel
Имена в Excel можно присваивать не только диапазонам ячеек и значениям, но так же и формулам. Имя, присвоенное формуле с относительным адресом ссылки, можно использовать как функцию.
- Создайте книгу Excel с отчетами так как показано на рисунке:
- Перейдите в ячейку B6 и выберите инструмент: : «Формулы»-«Присвоить имя».
- Заполните диалоговое окно так как показано на рисунке. Обратите внимание на то, что в поле «Диапазон» указана функция с относительными ссылками на диапазон. А в параметре «Область» обязательно указан текущий лист «2008».
- В ячейки B6, C6 и D6 введите значение: =итого.
- Скопируйте лист «2008» назвав новые копии листов: «2009» и «2010». И откройте «Формулы»-«Диспетчер имен».
Как видно в диспетчере имен при копировании листов для них автоматически создались имена «итого».
В excel есть возможность присвоения имен ячейкам, таблицам и диапазонам. После присвоения, эти имена можно использовать в формулах, при этом формулы становятся намного понятнее пользователю.
Присвоение имени константе .
Имена можно присваивать и константам, которых даже нет в вашем файле. Например, если в своем прогнозе вы используете рост рынка в размере 5% в следующем году, то этому росту можно присвоить имя. Для этого перейдите на ленте во вкладку Формулы -> Определенные имена -> Задать имя:
Откроется диалоговое окно "Создание имени", в котором необходимо ввести Имя, Область действия этого имени (лист, книга), Примечание (чтобы не забыть, что это за константа), в поле Диапазон необходимо ввести число и нажать ОК:
Теперь эту константу можно использовать во всей книге при расчете формулы, например так:
Обратите внимание, теперь значительно проще понять как был сформирован объем продаж в 2021 году.
Важно : константой может быть не только число, но и текст. Таким образом вы можете упростить ввод часто повторяющихся слов.
Присвоение имени ячейке и формуле.
Порядок присвоения имени ячейке и формуле не сильно отличается от предыдущего примера. Перейдите на ленте в блок Задать имя и в диалоговом окне "Создание имени" на последнем шаге вместо ввода числа введите либо ссылку на ячейку, либо формулу:
Если вы хотите, чтобы ваша именованная формула при расчете использовала значение из ячейки, находящейся левее (например, A4) от той, в которой формула будет находиться (B4), обязательно перед созданием имени активируйте ячейку B4 и не используйте абсолютные ссылки.
Подробнее об абсолютных и относительных ссылках можете прочитать в статье Абсолютные и относительные ссылки в Excel .
Присвоение имени диапазону.
В присвоении имени диапазону нет ничего сложного, просто в окне Создание имени в поле Диапазон пропишите ссылку на него. Интереснее дела обстоят с использованием пересечения диапазонов (это те ячейки, которые являются общими для двух диапазонов). Предположим, есть таблица с объемом продаж трех менеджеров за 4 квартала:
Возможно, вам приходилось работать с листами, в которых использовалась, формула типа: =СУММ(А5000:А5078). Вы гадали, что же находится в ячейках А5000:А5078!? Если в ячейках А5000:А5078 содержатся объемы продаж по регионам, не кажется ли вам формула =СУММ(ПродажиРегионы) более понятной? В данной главе описываются способы присвоения имен отдельным ячейкам и диапазонам ячеек, а также способы вставки имен диапазонов в формулы. [1]
Как создать именованный диапазон?
Существуют три способа создания именованных диапазонов:
- путем ввода имени диапазона в поле Имя;
- путем выбора на вкладке ФОРМУЛЫ в группе Определенные имена инструмента Создать из выделенного;
- путем выбора на вкладке ФОРМУЛЫ в группе Определенные имена инструментов Присвоить имя или Диспетчер имен.
Для создания имени диапазона с помощью поля Имя (рис. 1.1) выделите ячейку или диапазон ячеек, которым требуется присвоить имя, установите курсор в поле Имя, введите имя диапазона, и нажмите клавишу <Enter>. На рис. 1.1 ячейке В3 присвоено имя Старт.
Рис. 1.1. Создание имени диапазона путем выбора диапазона ячеек и ввода имени в поле Имя
При нажатии в поле Имя на стрелку раскрывающегося списка появятся имена диапазонов, определенные в текущей книге (рис. 1.2). При выборе в поле Имя имени диапазона все ячейки, соответствующие этому диапазону, отмечаются автоматически. Это позволяет убедиться в правильности выбора ячейки или диапазона ячеек для указанного имени. В именах диапазонов регистр не учитывается. Например, если выбрать имя Финиш, будет отмечена ячейка Е8 (рис. 1.3).
Рис. 1.2. Список имен диапазонов
Рис. 1.3. При выборе имени диапазона отмечаются все ячейки, соответствующие этому диапазону
При нажатии клавиши <F3> открывается диалоговое окно Вставка имени, в котором отображаются имена всех диапазонов.
Присвоение имени означает, что вместо любой ссылки Старт в формуле будет автоматически подставлено значение из ячейки В3.
Предположим, что необходимо присвоить имя Данные прямоугольному диапазону ячеек A1:B5. Выделите диапазон ячеек A1:B5, введите с клавиатуры Данные в поле Имя и нажмите клавишу <Enter>. Теперь с помощью формулы =СРЗНАЧ(Данные) можно вычислить среднее значение содержимого ячеек A1:B4 (рис. 1.4).
Рис. 1.4. Присвоение диапазону A1:B5 имени Данные и нахождение среднего значения именованного диапазона
Иногда требуется присвоить имя диапазону ячеек, состоящему из нескольких несмежных прямоугольных диапазонов. Например, B3:C4, E6:G7 и B10:C10 (рис. 1.5). Для присвоения имени выделите любой из трех прямоугольников. Удерживая клавишу <Ctrl>, выделите остальные два диапазона. Отпустите клавишу <Ctrl>, введите имя Несмежный в поле Имя и нажмите клавишу <Enter>. Теперь имя Несмежный в любой формуле указывает на содержимое ячеек B3:C4, E6:G7 и B10:C10.
Рис. 1.5. Присвоение имени несмежному диапазону ячеек
Создание имен с помощью инструмента Создать из выделенного. На листе «Рис. 1.6» Excel-файла с примерами содержатся продажи за март для каждого из 50 штатов США (рис. 1.6). Требуется присвоить каждой ячейке в диапазоне B2:B51 сокращенное название штата. Выделите диапазон A2:B51 и на вкладке ФОРМУЛЫ в группе Определенные имена выберите инструмент Создать из выделенного, и затем в открывшемся диалоговом окне установите флажок в столбце слева.
Рис. 1.6. Создание имен с помощью инструмента Создать из выделенного
Теперь имена в первом столбце выделенного диапазона связаны с ячейками во втором столбце выделенного диапазона. Таким образом, ячейке B6 присвоено имя диапазона СА, ячейка B7 имеет имя СО и т.д. Создавать имена таких диапазонов с помощью поля Имя было бы невероятно утомительно! Нажмите на стрелку раскрывающегося списка в поле Имя и убедитесь, что все имена диапазонов созданы.
Создание имен диапазонов с помощью инструмента Присвоить имя. Если на вкладке ФОРМУЛЫ в группе Определенные имена выбрать инструмент Диспетчер имен (и затем нажать кнопку Создать) или инструмент Присвоить имя, откроется диалоговое окно Создание имени (рис. 1.7).
Рис. 1.7. Диалоговое окно Создание имени
При нажатии на стрелку раскрывающегося списка в поле Область можно выбрать строку Книга или любой лист в книге, указав тем самым область действия имени (рис. 1.8). К любым именам диапазонов можно добавить примечания. Очень полезная опция, если не очевидно, что подразумевает выбранное имя диапазона.
Рис. 1.8. Выбор области действия имени
Диспетчер имен
В Microsoft Excel 2013 существует простой способ изменения или удаления имен диапазонов. Перейдите на вкладку ФОРМУЛЫ, выберите группу Определенные имена и откройте Диспетчер имен. Появится список имен всех диапазонов (рис. 1.9).
Рис. 1.9. Диспетчер имен
Для изменения имени диапазона дважды щелкните кнопкой мыши на имени этого диапазона или выделите его и нажмите кнопку Изменить; после этого можно изменить имя диапазона, ячейки в диапазоне и примечания. Область действия не подлежит изменению. Для удаления какого-либо подмножества имен диапазонов сначала выделите имена диапазонов, которые требуется удалить. Если имена диапазонов перечислены последовательно, выделите первое имя в группе имен, которую требуется удалить, затем, удерживая клавишу <Shift>, выделите последнее имя в группе. Если требуемые имена не перечислены друг за другом, можно выделить любое из имен, которое необходимо удалить, а далее, удерживая клавишу <Ctrl>, выделить остальные требуемые имена диапазонов. Затем для удаления выбранных имен диапазонов нажмите кнопку Удалить.
Редактирование формул в диалоговых окнах
Когда Excel отображает диалоговое окно (например, как на рис. 1.7 или 1.9), в котором можно записать ссылку на диапазон, поле, содержащее такую ссылку, всегда находится в режиме указания. Если активизировать поле Диапазон и воспользоваться стрелками для редактирования ссылки на диапазон, то вы обнаружите, что при этом вы именно указываете на диапазон, а не редактируете текст ссылки. Если на рисунке ниже вы поместите курсор в поле Диапазон, то попытка двинуть курсор влево с помощью стрелки даст неожиданный результат. Вместо движения курсора произошло изменение ссылки (обратите внимание: актуальный режим указан в левой части статусной панели):
Что делать? Нажмите F2. [2] Клавиша F2 позволяет переключаться между режимом указания (ввод) и режимом редактирования (правка). В режиме редактирования стрелки действуют именно так, как при редактировании формулы. На рисунке ниже попытка двинуть курсор влево увенчалась успехом:
Несколько конкретных примеров использования имен диапазонов
1. Необходимо вычислить общий объем продаж в штатах Аризона, Калифорния, Монтана, Нью-Йорк и Нью-Джерси.
Если вы помните наизусть сокращенные наименования штатов, то можно использовать формулу =AZ+CA+MT+NY+NJ (рис. 1.10)
Рис. 1.10. Использование имен вычисления объема продаж в отдельных штатах
2. Необходимо определить среднюю доходность акций, казначейских векселей и облигаций.
Выделите диапазон ячеек B2:D84 (рис. 1.11, часть строк на рис. скрыта), перейдите на вкладку ФОРМУЛЫ в группе Определенные имена выберите инструмент Создать из выделенного. В этом примере имена диапазона указаны в строке выше. Диапазон B3:B84 получает имя Акции, диапазон C3:C84 — имя Векселя и диапазон D3:D84 — имя Облигации. Таким образом, необходимость помнить, где находятся данные, отпадает. Например, если после начала ввода в ячейку B86 формулы нажать клавишу <F3>, откроется диалоговое окно Вставка имени. Кроме того, можно вызвать на экран список доступных имен диапазонов, если после начала ввода на вкладке ФОРМУЛЫ в группе Определенные имена выбрать инструмент Использовать в формуле. И, наконец, если вы помните первые буквы имени диапазона, и начнете их вводить в формуле, Excel выдаст подсказку (рис. 1.12). Эта опция Excel называется автозавершение формул. Для завершения ввода имени диапазона дважды щелкните на имени Векселя. Удобство использования имен диапазонов заключается в том, что, не зная точно, где находятся данные, можно работать с данными в любом месте книги!
Рис. 1.11. Исторические данные по инвестициям
Рис. 1.12. Подсказка при вводе в формуле имени диапазона
3. Использование имен столбца и строки
4. Имена с областью действия книга и лист
При создании имен с помощью поля Имя областью действия имен по умолчанию становится Книга. Однако, можно присвоить одно и тоже имя на разных листах, выбрав область действия Лист. Например, создайте новую книгу Excel, содержащую три листа, и введите числа 4, 5, 6 в ячейки E4:E6 на листе Лист1 и 3, 4, 5 в ячейки E4:E6 на листе Лист2. Затем откройте окно Диспетчер имен, присвойте имя jam ячейкам E4:E6 на листе Лист1 и определите область действия для этого имени как Лист1. Далее перейдите на Лист2, откройте окно Диспетчер имен, присвойте имя jam ячейкам E4:E6 и определите область действия для этого имени как Лист2. Диалоговое окно Диспетчер имен показано на рис. 1.13.
Рис. 1.13. Имена на уровне Листа
5. Как добиться отображения недавно созданных имен диапазонов в ранее созданных формулах?
Рассмотрим небольшую таблицу, содержащую формулы (рис. 1.14).
Рис. 1.14. Новые имена диапазонов в старых формулах
Ячейка F3 содержит цену продукта, а ячейка F4 — потребность в продукте =10000–300*F3. В ячейки F5 и F6 введена себестоимость единицы продукции и постоянные затраты, соответственно. Прибыль вычисляется в ячейке F7 по формуле =F4*(F3–F5)–F6. Выделите диапазон E3:F7, затем для присвоения ячейке F3 имени цена, ячейке F4 имени потребность, ячейке F5 имени себестоимость, ячейке F6 имени затраты и ячейке F7 имени прибыль используйте вкладку ФОРМУЛЫ, инструмент Создать из выделенного и флажок в столбце слева. Теперь имена созданных диапазонов необходимо отобразить в формулах ячеек F4 и F7. Для применения имен сначала выделите диапазон, для которого они создаются (в данном случае F3:F7). Затем на вкладке ФОРМУЛЫ в группе Определенные имена нажмите стрелку раскрывающегося списка Присвоить имя и выберите инструмент Применить имена. Выделите в окне имена, которые требуется применить, и нажмите кнопку OK. Обратите внимание, что в ячейке F4 теперь находится формула =10000-300*цена, а в ячейке F7 формула =потребность*(цена–себестоимость)–затраты, что и требовалось. [3]
6. Можно ли вывести на лист Excel список всех имен диапазонов (и представляемых ими ячеек)?
Откройте окно Вставка имени с помощью клавиши <F3> и нажмите кнопку Все имена (рис. 1.15). На листе, начиная с текущей ячейки, появится список имен диапазонов и соответствующих им ячеек.
Рис. 1.15. Вывод на лист Excel список всех имен диапазонов (и представляемых ими ячеек)
7. Использование формул для определения диапазона
Пример 1. Предполагаемый годовой доход вычисляется как кратный прошлогоднему доходу (рис. 1.16). Воспользуемся формулу =(1+прирост)*предыдущий_год (имя диапазона не может содержать пробел). Требуется вычислить доходы за 2012–2018 гг. с приростом 10% в год, начиная с базового уровня 300 млн. долларов в 2011 г.
Сначала в поле Имя присвойте ячейке B3 имя прирост. Теперь самое интересное! Переместите курсор в ячейку B7 и на вкладке ФОРМУЛЫ в группе Определенные имена выберите инструмент Присвоить имя для открытия диалогового окна Создание имени. Введите данные, как показано на рис. 1.16. Поскольку активной является ячейка B7, Excel всегда будет интерпретировать имя диапазона как указывающее на ячейку, находящуюся над текущей ячейкой. Это не будет работать, если в ссылке на ячейку B6 останется знак доллара, поскольку он не позволит изменить ссылку на строку и указать строку непосредственно над активной ячейкой (подробнее см. Относительные, абсолютные и смешанные ссылки на ячейки в Excel. Если в ячейку B7 ввести формулу =предыдущий*(1+прирост) и скопировать ее в диапазон B8:B13, каждая ячейка будет содержать требуемую формулу, по которой содержимое ячейки непосредственно над активной ячейкой будет умножаться на 1,1.
Рис. 1.16. Для любой ячейки это имя указывает на ячейку, находящуюся над активной ячейкой
Пример 2. Для каждого дня недели дана почасовая оплата и количество отработанных часов (рис. 1.17). Вычислим зарплату за каждый день по формуле почасовая*часы.
Выберите строку 12 (щелкните слева на 12) и в поле Имя (рядом со строкой формул) введите имя почасовая. Выберите строку 13 и введите в поле Имя – часы. Если теперь в ячейку F14 ввести формулу =почасовая*часы и скопировать эту формулу в диапазон G14:L14, то в каждом столбце автоматически появится результат перемножения значений почасовой оплаты и отработанных часов.
Рис. 1.17. Расчет зарплаты по дням недели
Если вам интересно, предлагаю несколько более сложных примеров использования имен диапазонов: Создание пользовательских функций при помощи имен, Автоматическое обновление сводной таблицы.
Некоторые замечания:
Задания для самостоятельной работы
Исходные данные находятся в файле Имена диапазонов. Задания.xlsx
- На листе Задание 1 содержатся данные о ежемесячной доходности акций General Motors и Microsoft. Присвойте имена диапазонам, содержащим ежемесячную доходность для каждой акции, и вычислите среднемесячную доходность каждой акции.
- На листе Задание 2 присвойте имя Красный диапазону, содержащему ячейки A1:B3 и A6:B8.
- На листе Задание 3 в ячейки G5 и G6 введите широту и долготу любого города, а в ячейки G7 и G8 широту и долготу другого города. В ячейке G10 вычисляется расстояние между двумя городами. Определите имена диапазонов для широты и долготы каждого города и убедитесь, что эти имена отображаются в формуле для расчета расстояния.
- На листе Задание 4 содержится количество акций для каждого вида акций и цена одной акции. Вычислите стоимость акций для каждого вида по формуле =количество*цена.
- На листе Задание 5 создайте имя диапазона для расчета среднего значения продаж за последние пять лет. Измените формулы в ячейках Е14:Е20.
[2] При написании этого раздела использованы идеи книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 156.
[3] У меня не получилось воспользоваться указанным методом, поэтому пришлось перенабрать формулы после присвоения имен.
2 комментария для “Excel. Имена диапазонов”
Александр, если я правильно понял вопрос, обратиться к конкретной ячейке именованного диапазона можно, например, с помощью функции ИНДЕКС. Ячейка В84 является 82-й в диапазоне Акции, поэтому обратиться к ней можно с помощью формулы =ИНДЕКС(Акции;82).
Нужна помощь по синтаксису формул:
В приложении вставил пример: на листе2 есть пример простенькой суммы элементов с первой страницы, а так же ПРИМЕРНО то, что я хочу получить(без знака равно) (диапазон задан через функции), но непонятно, как правильно тогда задать ссылку на лист1. Просто так границы диапазона можно легко задать функциями, но с новым листом у меня появились проблемы.
Нужна помощь по синтаксису формул:
В приложении вставил пример: на листе2 есть пример простенькой суммы элементов с первой страницы, а так же ПРИМЕРНО то, что я хочу получить(без знака равно) (диапазон задан через функции), но непонятно, как правильно тогда задать ссылку на лист1. Просто так границы диапазона можно легко задать функциями, но с новым листом у меня появились проблемы.
В приложении вставил пример: на листе2 есть пример простенькой суммы элементов с первой страницы, а так же ПРИМЕРНО то, что я хочу получить(без знака равно) (диапазон задан через функции), но непонятно, как правильно тогда задать ссылку на лист1. Просто так границы диапазона можно легко задать функциями, но с новым листом у меня появились проблемы.
Заранее спасибо. Автор - ololoshka
Дата добавления - 09.06.2013 в 22:38
Дата добавления - 09.06.2013 в 22:51
А какой-то еще способ записи есть? наверно, приведенный мной пример всем не удачен, чтобы описать стоящую задачу:
У меня на другом листе, назовем его Лист1, находится большой объем данных, который надо фильтровать через функции для другого листа, свой диапазон для каждой ячейки.
Вот диапазон, который я написал, и которая совершенно не работает, прошу подсказать, где тут ошибка.
От этого диапазона в свою очередь берутся функции
А какой-то еще способ записи есть? наверно, приведенный мной пример всем не удачен, чтобы описать стоящую задачу:
У меня на другом листе, назовем его Лист1, находится большой объем данных, который надо фильтровать через функции для другого листа, свой диапазон для каждой ячейки.
Вот диапазон, который я написал, и которая совершенно не работает, прошу подсказать, где тут ошибка.
От этого диапазона в свою очередь берутся функции ololoshka
От этого диапазона в свою очередь берутся функции Автор - ololoshka
Дата добавления - 09.06.2013 в 23:16
Читайте также: