Нельзя использовать в качестве диапазона в Excel
Обновлено: 21.11.2024
Создавайте имена Excel, которые ссылаются на ячейки, постоянное значение или формулу. Используйте имена в формулах или быстро выберите именованный диапазон.
Имена Excel — Введение
В Excel вы можете создавать имена, которые относятся к:
- Ячейки на листе
- Конкретное значение
- Формула
После того, как вы определите имена Excel, вы сможете:
- Используйте эти имена в формуле вместо постоянного значения или ссылки на ячейку.
- Введите имя, чтобы быстро перейти к именованному диапазону ячеек.
Приведенные ниже инструкции показывают, как создавать имена и использовать имена в файлах Excel. Получите образец рабочей книги Excel, чтобы следовать инструкциям.
ПРИМЕЧАНИЕ. Чтобы создать краткий список всех имен в книге, см. инструкции «Быстрый список имен — без макросов».
Как назвать ячейки
Посмотрите это короткое видео, чтобы узнать, как назвать группу ячеек. Затем перейдите к этой именованной группе ячеек или используйте имя в формуле. Письменная инструкция находится под видео. ТОП
Ячейки с именами — Поле с именами
Вы можете быстро назвать выбранные ячейки, введя текст в поле имени. ПРИМЕЧАНИЕ. Существует несколько правил для имен Excel
- Выберите ячейки, которым нужно присвоить имя.
- Нажмите в поле "Имя" слева от строки формул.
- Введите допустимое название списка, состоящее из одного слова, например, Список фруктов.
- Нажмите клавишу Enter.
Правила создания имен
На сайте Microsoft есть правила для имен Excel, и они кратко изложены ниже.
Для более подробного ознакомления с тем, какие символы разрешены, см. проект excel-names Мартина Труммера на GitHub — в нем есть письменные примеры и файл Excel для загрузки.
Следуйте этим правилам Microsoft при создании имени в Excel.
- Первый символ имени должен быть одним из следующих символов:
- письмо
- подчеркивание (_)
- обратная косая черта (\).
- буквы
- числа
- периоды
- символы подчеркивания
- Пробелы не допускаются в составе имени.
- Имена не могут выглядеть как адреса ячеек, например 35 австралийских долларов или R2D2.
- C, c, R, r — нельзя использовать в качестве имен — Excel использует их как ярлыки для выбора.
Посмотрите имена
ПРИМЕЧАНИЕ. Чтобы создать краткий список всех имен в книге, см. инструкции «Быстрый список имен — без макросов».
Чтобы увидеть некоторые из именованных диапазонов на листе, используйте этот прием:
- В правом нижнем углу окна Excel выберите параметр "Уровень масштаба".
- В диалоговом окне "Масштаб" выберите "Пользовательский".
- Введите 39 в процентное поле и нажмите "ОК".
Имена некоторых диапазонов будут отображаться на листе синим текстом, как MonthList на этом снимке экрана.
- Имена, созданные с помощью формулы, например YearList, отображаться не будут.
- Некоторые диапазоны могут быть слишком маленькими, чтобы отображать их названия.
Изменить именованный диапазон
После создания именованного диапазона может потребоваться изменить ячейки, на которые он ссылается. В этом коротком видео показаны шаги, а под видео есть письменные инструкции.
Выполните следующие действия, чтобы изменить ссылку на диапазон:
- На ленте откройте вкладку "Формулы".
- Нажмите "Диспетчер имен".
- В списке нажмите на имя, которое хотите изменить.
- В поле "Относится к" измените ссылку на диапазон или перетащите лист, чтобы выбрать новый диапазон.
- Нажмите на галочку, чтобы сохранить изменения
- Нажмите "Закрыть", чтобы закрыть ВЕРХНЮЮ часть диспетчера имен.
Создать имена из текста ячейки
Чтобы быстро назвать отдельные ячейки или отдельные диапазоны, можно использовать в качестве имен текст заголовка ячейки. Посмотрите это видео, чтобы увидеть шаги. Письменные инструкции находятся под видео.
Создать имена из текста ячейки
Быстрый способ создать имена – создать их на основе текста заголовков ячеек (меток листа). В показанном ниже примере ячейки в столбце E будут названы на основе меток в столбце D.
Чтобы назвать ячейки или диапазоны на основе меток рабочего листа:
-
Выберите метки и ячейки, которые должны быть названы.Метки могут располагаться выше, ниже, слева или справа от ячеек, которые нужно назвать. В этом примере метки находятся в столбце B слева от ячеек, которым будут присвоены имена.
Создать имя для значения
Большинство имен Excel относятся к диапазонам на листе, но имена также могут использоваться для хранения значений.
Часто используемые значения
Например, создайте имя для хранения процентной суммы, которую вы часто используете, например ставки розничного налога:
Затем используйте это имя в формулах вместо ввода значения
Специальные значения
Вы также можете создавать имена для хранения значений, которые трудно ввести. Например, в некоторых формулах используется это странное число. Согласно спецификациям Excel на сайте Microsoft, это максимальное положительное число, которое можно ввести в ячейку Excel.
Вместо того, чтобы вводить это число в свои формулы, вы можете определить имя, используя это значение (скопируйте число с этой страницы, прежде чем создавать имя):
Затем используйте имя XL_Max в формулах, например эту формулу ПРОСМОТР, которая находит последнее число в столбце.
=ПРОСМОТР(9.99999999999999E+307, Данные о весе[Вес])
Как использовать имена Excel
После создания имен вы можете их использовать:
Использовать имена для быстрой навигации
Если имя относится к диапазону, вы можете выбрать это имя в раскрывающемся списке Поле имени, чтобы выбрать именованный диапазон на листе.
ПРИМЕЧАНИЕ. Если имя не отображается в раскрывающемся списке, вместо него можно ввести имя
Использование имен в формулах
Вы также можете использовать имена в формулах. Например, у вас может быть группа ячеек с количеством проданных товаров. Назовите эти ячейки Количество, а затем используйте эту формулу для расчета общей суммы:
=СУММ(Количество)
Трюки с полем имени
Помимо использования поля имени для создания именованного диапазона или выбора именованного диапазона, есть еще несколько приемов с полем имени.
Изменить размер поля имени
В старых версиях Excel поле имени имело заданную ширину, и изменить ее было нельзя. Вот как можно настроить ширину окна имени в новых версиях:
- Наведите указатель мыши на кнопку с тремя точками справа от поля имени.
- Когда указатель примет вид двунаправленной стрелки, перетащите его влево или вправо, чтобы изменить ширину.
Выбрать ячейки
Еще один удобный прием заключается в том, что вы можете использовать поле имени для выбора безымянных ячеек. Вот несколько способов, которыми этот трюк может быть полезен: показать столбцы или заполнить большой диапазон ячеек.
Показать столбцы
Вот быстрый способ отобразить определенные столбцы и оставить другие скрытыми.
Заполнить ячейки
С помощью функции автозаполнения Excel вы можете очень быстро создать список дат, чисел или других последовательностей. Просто введите одно или два значения в качестве начальной последовательности, выберите эти ячейки и дважды щелкните маркер заполнения, чтобы заполнить последнюю строку данных.
Иногда в соседнем столбце нет данных, поэтому автозаполнение не работает при двойном щелчке. Вы можете перетащить маркер заполнения вниз, но это не очень эффективно, если вам нужно создать длинную серию.
Вот как создать список из 1000 чисел в столбце A:
- Нажмите в поле имени.
- Введите a1:a1000 в поле имени и нажмите Enter.
- Выделив ячейки, введите цифру 1 и нажмите Ctrl + Ввод.
- Далее выберите ячейку A1 и введите первое число в своей серии, например 5
- Выберите ячейку A2 и введите второе число в ряду, например 10
- Выберите ячейки A1 и A2 и дважды нажмите маркер заполнения, чтобы создать серию из 1000 чисел.
Создать динамический именованный диапазон
Если список, который вы хотите назвать, будет часто меняться, добавляя и удаляя элементы, вам следует создать динамический именованный диапазон. Размер динамического именованного диапазона будет автоматически изменяться при изменении списка. Вот два способа создания динамического именованного диапазона:
Использование именованной таблицы Excel
Самый простой способ создать динамический именованный диапазон — начать с создания именованной таблицы Excel. Затем определите диапазон на основе одного или нескольких столбцов в этой таблице.
В этом примере на листе есть список частей, а также будут созданы именованная таблица и динамические именованные диапазоны. Позже, если вы добавите в таблицу новые элементы, именованный диапазон автоматически расширится.
Сначала создайте таблицу:
- Выберите ячейку в списке деталей.
- На вкладке "Вставка" ленты нажмите "Таблица".
- Убедитесь, что выбран правильный диапазон, и установите флажок "Моя таблица имеет заголовки".
- Нажмите "ОК", чтобы создать таблицу.
Далее создайте динамический список идентификаторов деталей:
- Выберите ячейки A2:A9, которые содержат идентификаторы частей (но не заголовок)
- Нажмите на панели формул и введите имя диапазона, состоящее из одного слова: PartIDList
- Нажмите клавишу Enter, чтобы завершить ввод имени.
Чтобы увидеть определение имени, выполните следующие действия:
- Перейдите на вкладку "Формулы" ленты и выберите "Диспетчер имен".
- В списке есть два именованных элемента:
- таблица Parts с именем по умолчанию Table1 (или именем, которое вы дали таблице)
- PartIDList, основанный на поле PartID в таблице 1.
Текст динамического диапазона
Поскольку именованный диапазон PartIDList основан на именованной таблице, размер списка автоматически изменится, если вы добавите или удалите идентификаторы частей в списке.
- Добавить новый элемент в список идентификаторов деталей.
- В поле «Имя» выберите имя PartIDList.
- Именованный диапазон выбран и включает новый идентификатор детали. ТОП
Динамический именованный диапазон — формула
Когда вы создаете именованный диапазон в Excel, он не включает новые элементы автоматически. Если вы планируете добавлять новые элементы в список, вы можете использовать динамическую формулу для определения именованного диапазона Excel. Затем, когда в список добавляются новые элементы, именованный диапазон будет автоматически расширяться, чтобы включить их.
Письменные инструкции находятся под видео.
Динамический именованный диапазон на основе формулы
Если вы не хотите использовать именованную таблицу, вы можете использовать динамическую формулу для определения именованного диапазона. По мере добавления новых элементов диапазон будет автоматически расширяться.
Примечание. Динамические именованные диапазоны не отображаются в раскрывающемся списке Поле имени. Однако вы можете ввести имена в поле имени, чтобы выбрать этот диапазон на листе.
- На ленте откройте вкладку "Формулы".
- Нажмите "Определить имя".
- Введите имя диапазона, например Список имен
- Оставьте для параметра "Область" значение "Книга".
- Справочная ячейка: Sheet1!$A$1
- Строки для смещения: 0
- Колонки для смещения: 0
- Количество строк: COUNTA(Лист1!$A:$A)
- Количество столбцов: 1
- Примечание: для динамического количества столбцов замените 1 на:
COUNTA(Sheet1!$1:$1)
Получить образец файла
Чтобы следовать инструкциям на этой странице, загрузите образец файла имен Excel. Заархивированный файл имеет формат xlsx и не содержит макросов. ТОП
Office 365 профессиональный плюс переименовывается в Приложения Microsoft 365 для предприятий. Для получения дополнительной информации об этом изменении прочитайте эту запись в блоге.
Симптомы
Вы получаете следующее сообщение об ошибке:
Excel не может выполнить эту задачу с доступными ресурсами.Выберите меньше данных или закройте другие приложения.
Ошибка возникает, когда вы:
- Открыть или сохранить книгу Excel
- Открыть книгу Excel, которая ссылается на относительное имя из другой книги
- Использование определенного имени в формуле в книге Excel
- Определение или удаление имени в книге Excel
Разрешение
Примечание. Прежде чем переходить к методам разрешения, убедитесь, что у вас установлены последние обновления Office: обновляйте Windows с помощью последних обновлений.
После установки доступных обновлений проверьте, сохраняется ли проблема. Перейдите к методам решения, если у вас все еще есть проблемы.
Мы рекомендуем вам следовать приведенным в этой статье методам по порядку. Однако, если вы ранее пробовали один из способов исправить эту ошибку, и он не помог, вы можете быстро перейти к другому методу из этого списка:
- Способ 1. Проверьте, не превышаете ли вы лимиты.
- Способ 2. Убедитесь, что активен только один экземпляр Excel
- Способ 3. Закройте все приложения.
- Способ 4. Протестируйте Excel в безопасном режиме.
- Способ 5. Отключите панель предварительного просмотра в проводнике Windows (только для Windows 7)
- Способ 6. Сохраните файл книги Excel, если вы используете относительные имена.
- Способ 7. Измените определенные имена на ссылки непосредственно на ячейки
Способ 1. Проверьте, не превышаете ли вы лимиты
Ошибка может возникнуть, если вы превысите определенные ограничения Excel 2010, например, выполните слишком много вычислений в книге. Вот некоторые из этих ограничений:
- Максимальный размер листа – 1 048 576 строк на 16 384 столбца.
- Общее количество символов, которое может содержать ячейка, составляет 32 767 символов.
- Максимальный диапазон, выбранный для расчета, – 2048.
- Максимальный уровень вложенности функций в вычисление – 64.
Полный список спецификаций и ограничений Excel 2010 см. в этой статье на веб-сайте Office:
Если вы проверили и рабочий лист или рабочая книга не превышают ограничения Excel, перейдите к следующему способу.
Способ 2. Убедитесь, что активен только один экземпляр Excel
Ошибка может возникнуть, если запущено несколько экземпляров Excel. Обычно это происходит, если одновременно открыто несколько книг Excel. Мы рекомендуем вам закрыть все экземпляры Excel, а затем снова открыть книгу Excel для проверки. Если вы не уверены, запущено ли у вас несколько экземпляров Excel, выполните следующие действия для проверки:
Откройте диспетчер задач. Для этого выполните одно из следующих действий:
- Нажмите CTRL + ALT + Delete, а затем нажмите "Запустить диспетчер задач".
- Нажмите CTRL + Shift + Esc.
- Щелкните правой кнопкой мыши пустую область панели задач и выберите "Запустить диспетчер задач".
Открыв диспетчер задач, перейдите на вкладку "Приложения".
Нажмите на панель задач, чтобы отсортировать приложения в алфавитном порядке.
Если вы видите более одной строки с Microsoft Excel, вы используете несколько его экземпляров. Мы рекомендуем вам вернуться в Excel, сохранить книгу и закрыть ее. Повторяйте этот процесс до тех пор, пока Excel не перестанет отображаться в диспетчере задач.
После закрытия всех экземпляров Excel откройте книгу Excel и протестируйте ее. Если ошибка продолжает возникать, перейдите к следующему способу.
Способ 3. Закройте все приложения
Ошибка может возникнуть, если другие приложения активны и используют память компьютера, пока вы пытаетесь использовать, открывать или сохранять книгу Excel. Мы рекомендуем вам закрыть и выйти из всех приложений, кроме книги Excel.
Вы можете закрыть приложения вручную или выполнить шаги "чистой загрузки", описанные в одной из следующих статей:
После закрытия всех приложений откройте книгу Excel и проверьте. Если ошибка продолжает возникать, перейдите к следующему способу.
Способ 4. Протестируйте Excel в безопасном режиме
Эта ошибка может возникнуть, если запущено слишком много надстроек Excel. Чтобы проверить, не является ли надстройка причиной проблемы, запустите Excel в безопасном режиме:
Нажмите "Пуск" .
В Windows 7 введите excel /s в поле Поиск программ и файлов и нажмите Enter. В Windows Vista введите excel /s в поле «Начать поиск» и нажмите клавишу ВВОД.
Проверьте заголовок. Должно быть написано «Книга 1 — Microsoft Excel (безопасный режим)».
Нажмите "Файл" и выберите "Открыть".
Найдите рабочую книгу Excel для тестирования и откройте ее.
Откройте книгу Excel и проверьте. Если ошибка больше не возникает, возможно, у вас слишком много надстроек или конкретная надстройка может вызвать эту ошибку. Мы рекомендуем выполнить действия, описанные в этой онлайн-статье Microsoft, чтобы выгрузить надстройки:Если ошибка продолжает возникать, перейдите к следующему способу.
Способ 5. Отключите панель предварительного просмотра в проводнике Windows (только для Windows 7)
Панель предварительного просмотра используется для просмотра содержимого большинства файлов в проводнике Windows.Например, если щелкнуть изображение, видео или текстовый файл, можно просмотреть его содержимое, не открывая файл. По умолчанию панель предварительного просмотра отключена в Windows 7. Однако, если она включена, это может вызвать конфликт с книгой Excel, которую вы пытаетесь открыть, что приведет к этой ошибке. Мы рекомендуем отключить панель предварительного просмотра и протестировать открытие книги Excel. Для этого:
Если ошибка продолжает возникать, перейдите к следующему способу.
Способ 6: сохранить как файл книги Excel, если вы используете относительные имена
Ошибка может возникнуть, когда вы создаете книгу, содержащую относительное имя, а затем заполняете диапазон ячеек, ссылающихся на это относительное имя, в новой книге. Например, вы создаете книгу, содержащую относительное имя, а затем в другой книге нажимаете Ctrl + Enter, чтобы заполнить диапазон ячеек ссылкой на относительное имя. Вы сохраняете вторую книгу как файл «Книга Excel 97-2003 (*.xls)», а затем закрываете обе книги.
Чтобы обойти эту проблему, воспользуйтесь одним из следующих вариантов:
Вариант 1
- Сначала откройте книгу Excel, содержащую относительное имя.
- Затем откройте книгу Excel, содержащую ссылку на относительное имя.
Вариант 2
Сохраните обе книги как файлы книг Excel (.xlsx). Для этого:
- Нажмите "Файл", а затем "Сохранить как".
- Выберите Книга Excel (*.xlsx) в поле Тип файла и сохраните файл.
Если ошибка продолжает возникать, перейдите к следующему способу.
Способ 7. Измените определенные имена на прямые ссылки на ячейки
Возможно, вы использовали определенное имя для представления ячейки, диапазона ячеек, формулы или постоянного значения. Ошибка может возникнуть, если вы определяете имена, которые косвенно ссылаются на другие вложенные имена, имеющие глубину более 20 уровней, и выполняете одно из следующих действий:
- Вы добавляете или используете имя, которое превышает уровень косвенности в формуле.
- Вы удаляете имя, на которое ссылаются более 20 уровней определенных имен.
Чтобы решить эту проблему, измените определенные имена, чтобы они напрямую ссылались на заданные ячейки.
Если ошибка продолжает возникать, перейдите к разделу "Ссылки" этой статьи.
Ссылки
Если информация в этой статье базы знаний не помогла устранить ошибку в Excel 2010, выберите один из следующих вариантов:
Office 365 профессиональный плюс переименовывается в Приложения Microsoft 365 для предприятий. Для получения дополнительной информации об этом изменении прочитайте эту запись в блоге.
Симптомы
При попытке скопировать несмежные ячейки или диапазоны может появиться следующее сообщение об ошибке:
Эту команду нельзя использовать для множественного выбора.
Причина
Эти сообщения об ошибках могут появляться, когда выбранные вами ячейки или диапазоны находятся в разных столбцах или разных строках. Однако вы можете копировать и вставлять несмежные диапазоны без получения сообщения об ошибке, если ваш выбор содержит что-либо из следующего:
Несколько строк в одном столбце, например, A1, A3, A5.
Несколько столбцов в одной строке, например A2, C2, E2.
Несколько строк и столбцов в пределах одних и тех же столбцов и строк, например, A1, C1, A3, C3, A5, C5.
Подробнее
Когда вы делаете несмежный выбор, а затем нажимаете «Копировать» в меню «Правка», Excel пытается определить тип структуры выделения. Поскольку Excel «сдвигает» диапазоны вместе и вставляет их как один прямоугольник, непрерывный прямоугольник должен оставаться, если строки и столбцы между выбранными ячейками свернуты или установлены на размер 0.
Например, если вы хотите выполнить операцию со следующими образцами данных:
Поскольку при сворачивании строки 2 и столбца B остается непрерывный прямоугольник, вы можете выбрать ячейки A1, C1, A3, C3 по отдельности и скопировать их без ошибок. (Чтобы сделать несмежный выбор, удерживайте клавишу CTRL при выборе дополнительных ячеек или диапазонов.)
Это выделение будет вставлено как один прямоугольник:
Однако вы не можете добавить ячейку B2 к этому несмежному выделению, потому что Microsoft Excel не может определить, в каком направлении вы хотите сдвинуть ячейки. Например, B2 может скользить между A1 и A3, C1 и C3, A1 и C1 или A3 и C3. В результате получаются прямоугольники разной формы и размера. Поскольку вы не можете указать, как вы хотите расположить прямоугольник, Excel возвращает сообщение об ошибке. В этом случае диапазон ячеек должен быть одним выделением или ячейки должны быть скопированы по отдельности.
Именованный диапазон — это краткое текстовое описание, которое можно использовать вместо адреса ячейки для ссылки на отдельные ячейки или диапазоны ячеек.
Предоставление ячейкам фактических описательных имен может использоваться для упрощения формул и увеличения их объема. более удобным для пользователя.
Описательные имена также намного легче запомнить, чем фактические адреса ячеек.
Вы также можете давать описательные имена для своих диаграмм и фигур, хотя это не обсуждается в этом разделе.
Вы также можете давать описательные имена постоянным значениям, а также формулам.
Любые именованные диапазоны которые создаются, относятся к конкретной рабочей книге и могут использоваться только в рабочей книге, в которой они созданы.
Существует два типа именованных диапазонов, и оба типа можно использовать в одной рабочей книге.
Рабочая книга. На них можно ссылаться с любого рабочего листа рабочей книги.
Рабочий лист. Они зависят от рабочего листа, и на них можно ссылаться только на этом конкретном рабочем листе.Использование именованных диапазонов
Если вы определяете имя для диапазона ячеек, вы можете использовать это имя вместо адреса ячейки.
Именованные диапазоны можно использовать вместо ссылок на адреса ячеек в расчетах и формулах.
Вы можно определить более одного имени для одной и той же ячейки или диапазона ячеек, хотя, если вы используете одно и то же имя для определения двух разных диапазонов, предыдущее имя будет перезаписано.
Возможно вставить именованные диапазоны непосредственно в формулу Бар, когда вы заполняете формулу.
Именованные диапазоны могут быть присвоены ячейкам, содержащим как числовые, так и текстовые значения.
Предположим, вы используете рабочий лист для вычисления процента от определенного числа.Ячейка "C5" содержит формулу =C2*C3.
Этот метод использует ссылки на ячейки в формуле, что дает правильный результат.
Для пояснения этой формулы можно использовать именованные диапазоны.
>Вы можете вставить именованный диапазон в ячейку или формулу, используя диалоговое окно (Вставка > Имя > Вставить) и нажав OK.
Вы также можете использовать горячую клавишу (F3), чтобы отобразить (Вставка > Имя > Вставить). ) при редактировании ячейки.
Вы можете определить описательное имя для ячейки, содержащей сумму, и другое имя для ячейки, содержащей процентную скидку.
Предположим, что ячейке "C2" было присвоено описательное имя "Итого" и что ячейке "C3" было присвоено имя "Процент".
Формула в ячейке "C5" теперь может быть записана как "=Итого*Процент", что значительно упрощает понимание формулы. .Преимущества использования именованных диапазонов
Помимо предоставления альтернативы многократному вводу адресов ячеек и диапазонов ячеек, использование именованных диапазонов имеет ряд других преимуществ.
0) Они улучшают читаемость и делают ваши формулы более понятными, что снижает вероятность ошибки.
1) Перемещение или смещение ячеек с именованным диапазоном означает, что формулы корректируются автоматически. Не нужно беспокоиться о том, используют ли формулы абсолютные или относительные ссылки.
2) Вставка и удаление ячеек, строк или столбцов не изменит расположение именованных диапазонов. Однако перемещение ячеек, строк или столбцов будет происходить.
3) Вводить описательное имя намного проще, чем запоминать конкретный адрес ячейки, что упрощает ваши формулы.
4) Вы можете быстро перемещаться в определенные области своей книги. (или рабочий лист) очень быстро, используя поле имени или диалоговое окно (Правка > Перейти).
5) Вы также можете создавать трехмерные именованные диапазоны, представляющие одну и ту же ячейку или диапазон ячеек на нескольких рабочих листах.
6) Позволяет вам определять именованные константы, которые представляют собой одиночные, часто используемые значения.
7) Позволяет вам определять именованные формулы, которые являются общими часто используемыми формулами (за исключением их повторного ввода).Правила для именованных диапазонов
Именованный диапазон может иметь длину до 255 символов и может содержать буквы, цифры, точки и символы подчеркивания (без пробелов или специальных знаков препинания).
Именованные диапазоны не чувствительны к регистру и могут содержать как прописные, так и строчные буквы. дела буквы. Они не могут напоминать реальные адреса ячеек, такие как "B3" или "AA12".
Все именованные диапазоны должны начинаться с буквы, символа подчеркивания "_" или обратной косой черты "\".
Именованные диапазоны могут включать числа, но не могут включать пробелы.
Вы не можете использовать именованные диапазоны, которые напоминают фактические адреса ячеек (например, A$5 или R3C8).
Вы не можете использовать какие-либо символы, кроме нижнего подчеркивания и точки . Можно использовать обратную косую черту и вопросительный знак, если они не являются первыми символами.
Именованные диапазоны могут состоять только из отдельных букв, за исключением букв R и C.
При добавлении именованный диапазон именуется ячейкой, а не ее содержимым.
Они нечувствительны к регистру. У вас не может быть другого именованного диапазона с теми же буквами, но в другом регистре.
По умолчанию именованные диапазоны создаются как абсолютные ссылки.
Ячейка (или диапазон) может иметь более одного именованного диапазон, поэтому ввод нового имени в поле имени не изменит именованный диапазон, а создаст новый.Параметры
(вкладка "Расчет", "Принимать метки в формулах") – позволяет использовать формулы на естественном языке.
Если вы работаете с электронными таблицами Excel, это может означать большую экономию времени и эффективность.
В этом руководстве вы узнаете, как создавать именованные диапазоны в Excel и как использовать их для экономии времени.
Это руководство охватывает:
Именованные диапазоны в Excel — введение
Если кому-то нужно позвонить мне или обратиться ко мне, они будут использовать мое имя (вместо того, чтобы говорить, что мужчина находится в таком-то месте с таким-то ростом и весом).
Аналогично в Excel вы можете дать имя ячейке или диапазону ячеек.
Теперь вместо ссылки на ячейку (например, A1 или A1:A10) вы можете просто использовать присвоенное ей имя.
Например, предположим, что у вас есть набор данных, как показано ниже:
В этом наборе данных, если вам нужно обратиться к диапазону с датой, вам придется использовать A2:A11 в формулах. Точно так же для торговых представителей и продаж вам придется использовать B2:B11 и C2:C11.
Хотя это нормально, когда у вас есть только несколько точек данных, но если у вас огромные сложные наборы данных, использование ссылок на ячейки для ссылки на данные может занять много времени.
Именованные диапазоны Excel упрощают обращение к наборам данных в Excel.
Вы можете создать именованный диапазон в Excel для каждой категории данных, а затем использовать это имя вместо ссылок на ячейки. Например, даты могут называться «Дата», данные о торговых представителях могут называться «Представители по продажам», а данные о продажах могут называться «Продажи».
Вы также можете создать имя для отдельной ячейки. Например, если в ячейке указан процент комиссии за продажу, вы можете назвать эту ячейку «Комиссия».
Преимущества создания именованных диапазонов в Excel
Вот преимущества использования именованных диапазонов в Excel.
Используйте имена вместо ссылок на ячейки
При создании именованных диапазонов в Excel можно использовать эти имена вместо ссылок на ячейки.
Например, вы можете использовать =СУММ(ПРОДАЖИ) вместо =СУММ(C2:C11) для приведенного выше набора данных.
Посмотрите на приведенные ниже формулы. Вместо ссылок на ячейки я использовал именованные диапазоны.
- Количество продаж со значением более 500: =СЧЁТЕСЛИ(Продажи,">500″)
- Сумма всех продаж, совершенных Томом: =СУММЕСЛИ(СУММЕСЛИ(Представитель по продажам, "Том",Продажи)
- Комиссия, полученная Джо (продажи Джо, умноженные на комиссионный процент):
=СУММЕСЛИ(SUMIF(SalesRep»,Joe»,Sales)*Commission
Вы согласитесь, что эти формулы легко создавать и легко понимать (особенно когда вы делитесь ими с кем-то еще или пересматриваете сами).
Нет необходимости возвращаться к набору данных для выбора ячеек
Еще одно существенное преимущество использования именованных диапазонов в Excel заключается в том, что вам не нужно возвращаться назад и выбирать диапазоны ячеек.
Вы можете просто ввести пару букв этого именованного диапазона, и Excel покажет соответствующие именованные диапазоны (как показано ниже):
Именованные диапазоны делают формулы динамичными
Используя именованные диапазоны в Excel, вы можете сделать формулы Excel динамическими.
Например, в случае комиссии с продаж вместо значения 2,5 % можно использовать именованный диапазон.
Теперь, если позже ваша компания решит увеличить комиссию до 3%, вы можете просто обновить Именованный диапазон, и все расчеты будут автоматически обновлены, чтобы отразить новую комиссию.
Как создавать именованные диапазоны в Excel
Вот три способа создания именованных диапазонов в Excel:
Вот шаги для создания именованных диапазонов в Excel с помощью определения имени:
Это создаст именованный диапазон SALESREP.
Это рекомендуемый способ, когда у вас есть данные в табличной форме и вы хотите создать именованный диапазон для каждого столбца/строки.
Например, в приведенном ниже наборе данных, если вы хотите быстро создать три именованных диапазона (Date, Sales_Rep и Sales), вы можете использовать метод, показанный ниже.
Вот шаги, чтобы быстро создать именованные диапазоны из набора данных:
Это создаст три именованных диапазона — Date, Sales_Rep и Sales.
Обратите внимание, что он автоматически выбирает имена из заголовков. Если между словами есть пробел, вставляется символ подчеркивания (поскольку в именованных диапазонах пробелов быть не может).
Соглашение об именах для именованных диапазонов в Excel
Существуют определенные правила именования, которые необходимо знать при создании именованных диапазонов в Excel:
- Первым символом именованного диапазона должна быть буква и символ подчеркивания (_) или обратная косая черта (\). Если это что-то другое, он покажет ошибку. Остальные символы могут быть буквами, цифрами, специальными символами, точкой или символом подчеркивания.
- Вы не можете использовать имена, которые также представляют ссылки на ячейки в Excel. Например, вы не можете использовать AB1, так как это также ссылка на ячейку.
- Вы не можете использовать пробелы при создании именованных диапазонов. Например, у вас не может быть торгового представителя в качестве именованного диапазона. Если вы хотите объединить два слова и создать именованный диапазон, используйте для его создания символ подчеркивания, точку или символы верхнего регистра. Например, у вас может быть Sales_Rep, SalesRep или SalesRep.
- При создании именованных диапазонов Excel одинаково обрабатывает прописные и строчные буквы. Например, если вы создадите именованный диапазон ПРОДАЖИ, вы не сможете создать другой именованный диапазон, такой как «продажи» или «Продажи».
Слишком много именованных диапазонов в Excel? Не волнуйтесь
Иногда при работе с большими наборами данных и сложными моделями в Excel может создаваться множество именованных диапазонов.
Что делать, если вы не помните название созданного вами именованного диапазона?
Не волнуйтесь — вот несколько полезных советов.
Получение имен всех именованных диапазонов
Вот как получить список всех созданных вами именованных диапазонов:
Это даст вам список всех именованных диапазонов в этой книге. Чтобы использовать именованный диапазон (в формулах или ячейке), дважды щелкните по нему.
Отображение совпадающих именованных диапазонов
Если вы уже создали именованный диапазон, вы можете изменить его, выполнив следующие действия:
Полезные ярлыки именованных диапазонов (сила F3)
Вот несколько полезных сочетаний клавиш, которые пригодятся при работе с именованными диапазонами в Excel:
- Чтобы получить список всех именованных диапазонов и вставить его в формулу: F3.
- Чтобы создать новое имя с помощью диалогового окна диспетчера имен, нажмите Control + F3.
- Чтобы создать именованные диапазоны из выделенного: Control + Shift + F3.
Создание динамических именованных диапазонов в Excel
До сих пор в этом руководстве мы создавали статические именованные диапазоны.
Это означает, что эти именованные диапазоны всегда будут ссылаться на один и тот же набор данных.
Например, если A1:A10 называется "Продажи", это всегда будет относиться к A1:A10.
Если вы добавите больше данных о продажах, вам придется вручную обновить ссылку в именованном диапазоне.
В мире постоянно расширяющихся наборов данных это может занять у вас много времени. Каждый раз, когда вы получаете новые данные, вам, возможно, придется обновлять именованные диапазоны в Excel.
Чтобы решить эту проблему, мы можем создать динамические именованные диапазоны в Excel, которые будут автоматически учитывать дополнительные данные и включать их в существующий именованный диапазон.
Например, например, если я добавлю две дополнительные точки данных о продажах, динамический именованный диапазон будет автоматически ссылаться на A1:A12.
Этот вид динамического именованного диапазона можно создать с помощью функции Excel ИНДЕКС. Вместо указания ссылок на ячейки при создании именованного диапазона мы указываем формулу. Формула автоматически обновляется при добавлении или удалении данных.
Давайте посмотрим, как создавать динамические именованные диапазоны в Excel.
Предположим, у нас есть данные о продажах в ячейке A2:A11.
Вот шаги для создания динамических именованных диапазонов в Excel:
Теперь у вас есть динамический именованный диапазон с названием "Продажи". Он будет автоматически обновляться всякий раз, когда вы добавляете в него данные или удаляете из него данные.
Как работают динамические именованные диапазоны?
Чтобы объяснить, как это работает, вам нужно немного больше узнать о функции ИНДЕКС в Excel.
Большинство людей используют ИНДЕКС для возврата значения из списка на основе номера строки и столбца.
Но у функции ИНДЕКС есть и другая сторона.
Его можно использовать для возврата ссылки на ячейку, когда она используется как часть ссылки на ячейку.
Например, вот формула, которую мы использовали для создания динамического именованного диапазона:
INDEX($A$2:$A$100,COUNTIF($A$2:$A$100,"<>"&"") –> Ожидается, что эта часть формулы вернет значение (которое будет 10-м значение из списка, учитывая, что элементов десять).
Однако при использовании перед ссылкой (= $A$2: ИНДЕКС($A$2:$A$100,СЧЁТЕСЛИ($A$2:$A$100,"<>"&"")) возвращается ссылка на ячейку вместо значения.
Следовательно, здесь возвращается =$A$2:$A$11
Если мы добавим два дополнительных значения в столбец продаж, он вернет =$A$2:$A$13
При добавлении новых данных в список функция Excel СЧЁТЕСЛИ возвращает количество непустых ячеек в данных. Этот номер используется функцией ИНДЕКС для извлечения ссылки на ячейку последнего элемента в списке.
Примечание:
- Это будет работать, только если в данных нет пустых ячеек.
- В приведенном выше примере я назначил большое количество ячеек (A2:A100) для формулы именованного диапазона. Вы можете изменить это в зависимости от вашего набора данных.
Вы также можете использовать функцию OFFSET для создания динамических именованных диапазонов в Excel, однако, поскольку функция OFFSET нестабильна, это может привести к медленной работе книги Excel. ИНДЕКС, с другой стороны, является полузависимым, что делает его лучшим выбором для создания динамических именованных диапазонов в Excel.
Читайте также: