0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как быстро выбрать ячейки именованного диапазона в Excel?

Как увидеть все диапазоны именованных ячеек в книге Excel

Именование диапазонов ячеек в Excel облегчает обращение к этим ячейкам в формуле. В рабочей книге с большим количеством данных на рабочих листах легче читать и понимать формулы, когда диапазонам ячеек присвоены имена.

Тем не менее, если у вас очень большая электронная таблица, вам может не хватить памяти, чтобы запомнить, какие имена соответствуют каким диапазонам. Мы покажем вам, как создать список имен и соответствующих диапазонов ячеек, к которому вы сможете обращаться при создании формул для этой электронной таблицы.

В зависимости от того, сколько имен у вас в рабочей книге, вы можете использовать новый рабочий лист для хранения списка. Наш список не очень длинный, но мы все равно хотим отделить его от остальных данных. Для этого щелкните правой кнопкой мыши по вкладкам рабочего листа в нижней части окна Excel и выберите в контекстном меню пункт «Вставить». Когда откроется диалоговое окно «Вставка», убедитесь, что вкладка «Общие» активна, а в правом окне выбран «Рабочий лист». Затем нажмите кнопку «OK».

Image

Перейдите на вкладку Формулы и выберите ячейку, с которой должен начинаться список имен. Ниже мы добавили несколько заголовков над списком.

Image

Затем в разделе «Конкретные имена» нажмите «Использовать в формуле» и выберите «Вставить имена». Вы также можете нажать «F3».

Н ЕТ. В книгах без именованных диапазонов ячеек кнопка Использовать в формуле недоступна.

Image

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

Image

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

Читайте так же:
Как вернуться к предыдущей активной ячейке листа после нажатия гиперссылки в Excel?

Image

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

Image

Обновленный список создается, когда вы удаляете созданный список и затем создаете его снова.

Автоматически изменяемые диапазоны ячеек в Excel

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

Как в Excel сделать автоматическое изменение диапазона

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

Составьте отчет о работе нашей компании, как показано на рисунке :

Доходы.

Эту проблему можно было бы решить, просуммировав весь столбец B, и после появления в нем записей общая сумма изменилась бы автоматически. Но это неправильный способ решения задач в Excel. Мы не могли ввести другие данные в ячейки столбца B. Во-вторых, таким образом функция суммирования нерационально потребляла бы больше памяти. В таких документах могут возникнуть серьезные проблемы. Динамические имена — наиболее рациональное решение.

  1. Выберите инструмент «Формулы»-«Определенные имена»-«Присвоить имя».
  2. Заполните поля диалогового окна «Создание имени» как на рисунке. Обратите внимание на то, что в поле «Диапазон:» мы используем функцию =СМЕЩ, а в одном из ее параметров используем функцию =СЧЕТ. Пример: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1) Создание имени.
  3. Переместите курсор в ячейку D2 и введите функцию =СУММ с именем «доход» в ее параметрах.
Читайте так же:
Как ВПР затем умножить в таблицах Excel?

Теперь мы можем увидеть, как постепенно меняется охват ячеек с названием «Доход» по мере заполнения ячеек в столбце B.

Функция СМЕЩ в Excel

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

= SMESH определяет наш диапазон на основе количества заполненных клеток в столбце B. В функции =MEMB есть пять параметров (начальная клетка, смещение строки, смещение столбца, смещение высоты, смещение ширины):

  1. «Начальная ячейка» — задает левую верхнюю ячейку, от которой диапазон динамически расширяется как вниз, так и вправо (при необходимости).
  2. «Смещение по строкам» — этот параметр определяет, насколько диапазон должен быть смещен по вертикали относительно начальной ячейки (первый параметр). Значения могут быть нулевыми и отрицательными.
  3. «Смещение по столбцам». — Этот параметр указывает, на сколько диапазон должен быть смещен по горизонтали относительно начальной ячейки. Значения могут быть даже нулевыми и отрицательными.
  4. «Размер диапазона по высоте» — Количество ячеек, на которое должна быть увеличена высота диапазона. На самом деле, название говорит само за себя.
  5. «Размер диапазона в ширину» — Количество ячеек, на которое должна быть увеличена ширина диапазона относительно начальной ячейки.

Последние два параметра функции являются необязательными. Если они не заполнены, диапазон будет состоять из 1-й ячейки. Например: =MEMB(A1;0;0) относится только к ячейке A1, а =MEMB(A1;2;0) относится к A3.

Давайте теперь разложим функцию: =Count, который мы указали в четвертом параметре функции: = САЙТ.

Что определяет функция СЧЕТ

СЧЕТ.

Функция =Count($B:$B) автоматически подсчитывает число заполненных ячеек в столбце B.

Поэтому мы используем функции =Count() и =MEMBERSHIP(), чтобы автоматизировать процесс создания диапазона для имени «доход», сделав его динамическим. Теперь давайте снова посмотрим на нашу формулу, которую мы назвали «доход»: =MEMB(Sheet1!$B$2;0;0;0;ACCOUNT(Sheet1!$B:$B);1)

Читайте так же:
Как быстро преобразовать фунты в унции / граммы / кг в Excel?

Эту формулу следует читать следующим образом: первый параметр указывает, что диапазон автоматических переменных начинается в ячейке B2. Следующие два параметра имеют значения 0;0 — это означает, что динамический диапазон не перемещается относительно начальной ячейки B2. Увеличивается только вертикальный размер, на что указывает четвертый параметр. Она содержит функцию CALCULATE и возвращает число, равное количеству заполненных ячеек в столбце B. Поэтому количество вертикально заполненных ячеек в диапазоне будет равно количеству, заданному функцией VALUE. Последний параметр 5 отвечает за ширину диапазона, содержащего число 1.

В результате работы функции Count в память загружаются только заполненные ячейки столбца B, а не весь столбец. Использование этого документа устраняет возможные ошибки памяти.

Динамические диаграммы в Excel

Для этого типа отчетов создадим динамическую диаграмму:

  1. Выделите диапазон B2:B6 и выберите инструмент: «Вставка»-«Диаграммы»-«Гистограмма»-«Гистограмма с группировкой».
  2. Щелкните левой кнопкой мышки по любому столбцу гистограммы и в строке формул отобразится функция диаграммы: =РЯД(). РЯД.
  3. В строке формул измените параметры функции: =РЯД(Лист1!$B$1;;Лист1!$B$2:$B$7;1) на =РЯД(Лист1!$B$1;;Лист1!доход;1).
  4. В отчет добавьте новую запись в ячейки A8 — «июль», B8 — «77000» соответственно. И убедитесь, что на диаграмме автоматически добавился новый столбец.

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector