000818.ru

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

Как найти ссылку на именованный диапазон в Excel?

Как удалить именованные диапазоны в excel

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

В диалоговое окно Менеджер имен можно попасть, нажав кнопку Менеджер имен в группе Формулы на вкладке Отличительные имена.

Диалоговое окно

В диалоговом окне Name Manager отображается следующая информация для каждого имени в списке.

Одно из значений: 1.

Конкретное имя, которое обозначается значком конкретного имени;

Это имя таблицы, обозначенное значком имени таблицы.

Примечание: Имя таблицы — это имя таблицы Excel, которая является набором данных по определенной теме, сохраненной в записях (строках) и полях (столбцах). Excel создает таблицу Excel "Таблица1", "Таблица2" и так далее при каждой вставке Excel таблицы. Вы можете изменить имя таблицы, чтобы сделать ее более осмысленной. Дополнительные сведения о Excel таблицах см. в этой Excel таблицах.

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

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

Вы можете присвоить ячейке или диапазону ячеек значимые обозначения, чтобы упростить работу с формулами. При создании формул можно ввести имя в качестве аргумента вместо ссылки на диапазон ячеек. Например, назвав область ячеек Годовой_доход, вы можете ввести формулу =SUM(Годовой_доход) вместо =SUM(B1:B12). В результате формулы становятся более понятными. Если многие формулы ссылаются на один и тот же набор ячеек, эта функция также может оказаться полезной. Вы можете изменить адрес диапазона один раз в Менеджере имен вместо того, чтобы редактировать каждую формулу отдельно.

Имена могут быть одного из двух типов:

  • Определенное имя — это произвольное имя, которое может быть задано для диапазона ячеек. Определенные имена также включают имена, которые автоматически создаются при настройке областей печати.
  • Имя таблицы — это имя по умолчанию, которое автоматически присваивается вновь отформатированной таблице (Таблица1, Таблица2 и т.д.). Это имя можно отредактировать позже.

Область применения имени относится к региону, в котором оно распознается. Имя может быть определено для всей книги (имя будет распознаваться на каждой странице книги) или только для определенного листа. Каждое имя в данной области должно быть уникальным; одно и то же имя может появляться во многих разных областях.

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

Создание новых имен

Для нового специфического имени области:

  1. Выделите ячейку или диапазон ячеек, которым требуется присвоить имя.
  2. Откройте окно создания нового имени удобным для вас способом:
    • Щелкните по выделенной области правой кнопкой мыши и выберите из контекстного меню пункт Присвоить имя
    • или щелкните по значку Именованные диапазоныЗначок Именованные диапазонына вкладке Главная верхней панели инструментов и выберите из меню опцию Новое имя.

Откроется окно «Новое имя»:

Окно Новое имя

Имя не должно содержать цифр, пробелов или знаков препинания. Допускается использование знаков подчеркивания (_). Регистр не имеет значения.

Окно Выбор диапазона данных

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

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

Поле Имя

Управление именами

Менеджер имен предоставляет доступ ко всем существующим именам. Чтобы открыть его:

  • щелкните по значку Именованные диапазоныЗначок Именованные диапазонына вкладке Главная верхней панели инструментов и выберите из меню опцию Диспетчер имен
  • или щелкните по стрелке в поле «Имя» и выберите опцию Диспетчер имен.

Откроется окно Управление именем:

Окно Диспетчер имен

Кроме того, вы можете отфильтровать имена на основе категорий имен, которые вы хотите показать: Все, Определенные имена, Имена таблиц, Имена листов или Имена книг. Имена из выбранной категории будут отображаться, остальные будут скрыты.

Чтобы изменить порядок сортировки отображаемого списка, щелкните заголовок Именованные диапазоны или Область в этом окне.

Чтобы отредактировать имя, выделите его в списке и нажмите кнопку Edit (Редактировать). Откроется окно Edit Name (Редактировать имя):

Окно Изменение имени

Для конкретного имени можно изменить имя и диапазон данных, к которым оно относится. Для имени таблицы можно изменить только имя. Когда вы внесете все необходимые изменения, нажмите OK, чтобы применить их. Чтобы отменить изменения, нажмите Отмена. Если измененное имя используется в формуле, формула будет автоматически изменена соответствующим образом.

Чтобы удалить имя, выделите его в списке и нажмите Delete (Удалить).

Обратите внимание, что удаление имени из формулы приведет к тому, что формула не будет работать (будет возвращена ошибка. ).

Вы также можете создать новое имя в окне Менеджера имен, нажав кнопку Новое.

Использование имен при работе с электронной таблицей

Чтобы быстро перемещаться между диапазонами ячеек, нажмите стрелку в поле Имя и выберите нужное имя из списка — диапазон данных, соответствующий этому имени, будет выделен на листе.

Список имен

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

Чтобы добавить имя в качестве аргумента в формулу

  1. Установите курсор там, куда надо вставить имя.
  2. Выполните одно из следующих действий:
    • введите имя нужного именованного диапазона вручную с помощью клавиатуры. Как только вы введете начальные буквы, появится список Автозавершения формул. По мере ввода в нем отображаются элементы (формулы и имена), которые соответствуют введенным символам. Можно выбрать нужное имя из списка и вставить его в формулу, дважды щелкнув по нему или нажав клавишу Tab.
    • или щелкните по значку Именованные диапазоныЗначок Именованные диапазонына вкладке Главная верхней панели инструментов, выберите из меню опцию Вставить имя, выберите нужное имя в окне Вставка имени и нажмите кнопку OK:

Окно Вставка имени

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

Правила именования при создании именованных диапазонов в Google Таблицах

При создании именованных диапазонов в электронных таблицах Google необходимо следовать некоторым правилам.

  • Не может содержать пробелов и знаков препинания. Например, у вас не может быть «Данные о продажах». Однако вы можете использовать подчеркивание между словами. Следовательно, «Sales_Data» в порядке.
  • Может содержать только буквы, цифры и символы подчеркивания.
  • Нельзя начинать с числа или слов «правда» или «ложь».
  • Длина не должна превышать 250 символов.
  • Не может быть чем-то, что относится к диапазону. Например, вы не можете назвать его A1, так как он уже относится к ячейке. Точно нельзя назвать его A1: A100.

Метод 2. Использование инструментов на Ленте

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

  1. Начнем с того, что закроем информационное окно о наличии циклической ссылки.Закрытие окна, информирующего о наличии в таблице циклической ссылки
  2. Теперь переключаемся во вкладку “Формулы”. Обращаем внимание на раздел “Зависимости формул”. Здесь нас интересует кнопка “Проверка ошибок” (в некоторых случаях, когда размеры окна сжаты по горизонтали, отображается только значок кнопки в виде восклицательного знака). Щелкаем по небольшому треугольнику, направленному вниз, справа от кнопки. Откроется перечень команд, среди которых выбираем пункт “Циклические ссылки”, после чего откроется список всех ячеек, содержащих эти самые ссылки.Поиск циклической ссылки через функцию проверки ошибок
  3. Если мы щелкнем на адрес ячейки, программа сразу же выделит ее, независимо от того, в какой ячейке мы находились до того, как решили воспользоваться данной функцией.Поиск циклической ссылки через инструменты на ленте

Организация данных в Microsoft Excel

Знание перекрестных ссылок на ячейки в Excel позволяет более эффективно организовать данные. Вам не нужно работать на одном листе — вы можете использовать данные из одной ячейки в разных листах и ​​файлах электронных таблиц.

Затем вы можете взять эти данные и обратиться к ним в другом программном обеспечении. Например, вы можете начать встраивание Excel с заметками OneNote. Если вы новичок в Excel, не беспокойтесь — есть множество советов по Excel, которые помогут вам быстро освоиться.

4 способа поиска данных в таблице Excel

Function VPR s uslovismi 1 4 способа поиска данных в таблице Excel Добрый день уважаемый читатель!

Я хочу кратко напомнить вам о силе и полезности функции ВПР и показать 4 различных способа поиска данных в таблице Excel с помощью других функций и их комбинаций с несколькими условиями. Хотя функция ВПР способна на многое, у нее есть некоторые ограничения. Например, она может искать только по одному условию или по левой стороне. В связи с этим мы будем искать варианты и применять приемы для расширения ее базовой функциональности.

Я подробно описал, как работает функция VPR, в ранее упомянутой статье, поэтому нажмите на ссылку, чтобы лучше понять. Если вы встречаетесь с группой впервые, это очень важно. В дополнение к ВПР, его близнецу, я также подробно описал его, с преимуществами и недостатками. Те из вас, кто знаком с теорией, давайте обсудим применение.

Давайте теперь рассмотрим все 4 способа поиска данных в таблице Excel и сочетание функции БПФ с другими функциями на примерах:

  1. Работа с функцией SUMMPROIZE
  2. Работа с функцией SELECT
  3. Создание дополнительной колонки
  4. Работа с функциями SEARCHPROPOSE и INDEX.

Используем функцию СУММПРОИЗВ

Как я описывал в своей статье о СУММПРОИЗВ, это одна из самых мощных функций в Excel. Когда мы используем функцию SUMPROIZE, мы будем делать это с помощью возможностей формулы. В нашем случае формула будет выглядеть следующим образом:

Function VPR s uslovismi 2 4 способа поиска данных в таблице Excel

=SUMPROV((C2:C11=G2)*(B2:B11=G3);D2:D11) Формула работает следующим образом: создается условный массив, где значения в ячейке «G2» сравниваются с диапазоном «C2:C11», а ячейка «G3» — с диапазоном «B2:B11». Все эти две матрицы сравниваются и сопоставляются, а затем преобразуются в единицы и нули, причем значение один помещается в строку, где выполняются все условия формулы. Следующая операция — умножение полученной условной матрицы на диапазон «D2:D11», а так как в матрице только одна единица, то формула даст 146.

Обратите внимание, если текстовые значения находятся в диапазоне «D2:D11», формула откажется работать. Пожалуйста, прочитайте мою статью о СУММПРОИЗВ для более глубокого понимания этой функции.

Применение функции ВЫБОР

S ELECT был описан ранее, но я не упомянул его в этой версии. В нашем случае мы должны создать новую таблицу с колонками «Период» и «Месяц» вместе, что достигается с помощью функции SELECT. Следующая формула будет выглядеть следующим образом:

Function VPR s uslovismi 3 4 способа поиска данных в таблице Excel

<=ВПР(G2&G3;ВЫБОР(<1;2>;C2:C11&B2:B11;D2:D11);2;0)> Основная работа, которую проделывает функция ВЫБОР в своей части «ВЫБОР(<1;2>;C2:C11&B2:B11;D2:D11)» это объединение значений столбиков «Период» и «Город» в общий массив, значения в котором будут прописаны как: «МоскваЯнварь», «БрянскФевраль», …. и т.д. Получив такое объединённое значения столбиков мы сможем легко сделать просмотр и отбор нужного значения, вот теперь я думаю, формула стала ближе.

Поскольку мы работаем с матричной формулой, ввод должен осуществляться с помощью комбинации клавиш Ctrl+Shift+Enter. Система определит формулу как созданную для матриц и поместит квадратные скобки с обеих сторон формулы.

Создаем дополнительные столбики

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

Типичный пример включает определение продаж по двум показателям: «Период» и «Город». Поскольку в данном случае мы будем использовать функцию БПФ, ее применение будет неуместным, так как функция может вернуть значение более чем для одного условия. Поэтому мы должны создать новый столбец, который объединит оба критерия в один, поэтому мы добавим следующую формулу во вновь созданный столбец: =B2&C2. И теперь результат из столбца D мы можем использовать нашу формулу в ячейке H4:

=ВПР(H2&H3;D2:E11;2;0)

Function VPR s uslovismi 4 4 способа поиска данных в таблице Excel

Как видите, наши отдельные условия выбора значений также объединены аргументом H2&H3 в единый критерий. После поиска в указанном диапазоне D2:E11 формула возвращает значение, найденное в столбце 2.

Совмещаем функции ПОИСКПОЗ и ИНДЕКС для работы

В итоге у нас получился простой метод, который легко повторить, но, конечно, это будет не самый простой вариант. Мы снова воспользуемся формулой массива и объединим функции ПОИСК и ИНДЕКС вместе полезным способом. Подробнее о том, как работают эти функции, вы можете прочитать в моих отдельных статьях.

Для поиска данных в таблице Excel воспользуемся следующей формулой:

Что он делает, такой большой и непонятный. Ее можно рассматривать как несколько шагов или блоков. Формула функции — ПОИСК(1;(B2:B11=G3)*(C2:C11=G2);0) и происходит следующее, со значением в ячейке G3 последовательно сравниваем значения из диапазона B2:B11 и в случае совпадения условий получаем результат TRUE, а если есть различия — FALSE. Аналогичный процесс для значения G2 и диапазона C2:C11. Сравнивая эти массивы, состоящие из аргументов TRUE и FALSE, мы сравниваем значение 1, это TRUE*TRUE, а все остальные комбинации игнорируются.

Function VPR s uslovismi 5 4 способа поиска данных в таблице Excel

Теперь, когда функция ПОИСКПОЗ нашла в таблице значение, соответствующее «1», и указала его позицию в шестой строке, это означает, что аргумент «6» для диапазона D2:D11 передан функции ИНДЕКС.

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

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

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