Как вставить имя листа в ячейку Excel

Обновлено: 21.11.2024

In Excel Excel для начинающих Это руководство по Excel для начинающих научит вас всему, что вам нужно знать о электронных таблицах и формулах Excel для выполнения финансового анализа. Посмотрите видео и узнайте все, что нужно знать новичку, от того, что такое Excel, до того, почему мы его используем, и каковы наиболее важные сочетания клавиш, функции и формулы, если вы хотите отобразить имя листа в ячейке, вы можете использовать комбинацию формул для его отображения. Использование формулы Excel с кодом имени листа требует объединения функций MID, CELL и FIND в одну формулу.

Например, если вы распечатываете финансовую модель. Типы финансовых моделей К наиболее распространенным типам финансовых моделей относятся: модель с тремя отчетами, модель DCF, модель M&A, модель LBO, бюджетная модель. Откройте для себя 10 лучших типов на бумаге или в формате PDF, затем вы можете отобразить название листа вверху каждой страницы. Теперь это легко сделать с помощью этой формулы.

Код имени листа Формула Excel

=СРЕДН(ЯЧЕЙКА("имя файла",A1),НАЙТИ("]",ЯЧЕЙКА("имя файла",A1))+1500)

(См. скриншоты ниже)

Пример создания кода имени листа Excel

Шаг 1. Введите «ЯЧЕЙКА («имя файла», A1)». Функция ячейки используется для получения полного имени файла и пути. Эта функция возвращает имя файла рабочей книги .xls, включая имя листа. Это наша отправная точка, а затем нам нужно удалить часть имени файла и оставить только имя листа.

Шаг 2. Введите "НАЙТИ("]",ЯЧЕЙКА("имя файла")+1,500″. По сути, формула заходит в имя файла и находит "]", где заканчивается имя книги. там он перемещается на один символ (+1) туда, где начинается имя листа. Наконец, мы добавляем в конец 500 или любое число, которое больше, чем реальное количество символов, которое может содержать имя листа.

Поздравляем, теперь вы отобразили имя листа в Excel!

Шаблон Excel с кодом имени листа

Загрузить бесплатный шаблон

Введите свое имя и адрес электронной почты в форму ниже и загрузите бесплатный шаблон прямо сейчас!

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

Функция CELL() в этом случае возвращает полный путь\[Имя файла]ИмяЛиста. Глядя на закрывающую квадратную скобку, вы можете понять, где встречается имя листа.

Билл Джелен является автором / соавтором:
Экономия времени с помощью Excel - онлайн-курс

Повысьте эффективность работы с Excel с помощью этих методов, позволяющих сэкономить время в Excel.

Предстоящее событие

Веб-семинар Excel для главы Space Coast IIA
Мыс Канаверал, Флорида, 23 марта 2022 г.

Актуальные темы

Здравствуйте, эксперты по коду. Я использую один из кодов Johnnyl, редактирую его и использую для другого проекта. Мне нужна помощь в некоторых местах. Во-первых, я не умею

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

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

Здравствуйте, у меня есть документ Excel, содержащий дату дефектов. Мне нужно сгенерировать электронное письмо, в котором будет указана дата дефекта, и отправить мне письмо за 4 месяца до этого

Привет всем, мне нужна помощь в редактировании приведенной ниже формулы.Я хочу автоматически заполнять формулы, начиная с IFERROR (G2) в столбце G до последней строки, которая соответствует

Здравствуйте, я пытаюсь создать формулу для ежедневной инвентаризации, которая вычисляет количество поддонов (обозначается как P) в столбце. Как вы можете видеть ниже

При работе с электронными таблицами Excel иногда может потребоваться получить имя рабочего листа.

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

Поэтому, если вы хотите получить имя листа, чтобы оно автоматически обновлялось при изменении имени, вы можете использовать простую формулу в Excel.

В этом уроке я покажу вам, как получить имя листа в Excel с помощью простой формулы.

Это руководство охватывает:

Получить имя листа с помощью функции CELL

Функция ЯЧЕЙКА в Excel позволяет быстро получить информацию о ячейке, в которой используется функция.

Эта функция также позволяет нам получить полное имя файла в результате формулы.

Предположим, у меня есть рабочая книга Excel с именем листа "Данные о продажах"

Ниже приведена формула, которую я использовал во всех ячейках рабочего листа «Данные о продажах»:

Как видите, он дал мне полный адрес файла, в котором я использую эту формулу.

Но мне нужно было только имя листа, а не весь адрес файла,

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

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

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

Обратите внимание, что формула CELL работает, только если вы сохранили книгу. Если вы этого не сделаете, он вернет пустое значение (поскольку он не знает, что такое путь к книге)

Интересно, как работает эта формула? Позвольте мне объяснить!

Формула CELL дает нам полный адрес книги вместе с именем листа в конце.

Одно из правил, которому всегда следует следовать, — указывать имя листа после квадратной скобки (]).

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

И это именно то, что делает эта формула.

Часть формулы НАЙТИ ищет «]» и возвращает ее позицию (число, обозначающее количество символов, после которого находится квадратная скобка)

Мы используем это положение квадратной скобки в формуле ПРАВИЛЬНО, чтобы извлечь все после этой квадратной скобки

Одна из основных проблем с формулой CELL заключается в том, что она динамическая. Поэтому, если вы используете его на Листе 1, а затем переходите на Лист 2, формула на Листе 1 обновится и покажет вам имя как Лист 2 (несмотря на то, что формула находится на Листе 1). Это происходит, когда формула CELL рассматривает ячейку на активном листе и дает имя для этого листа, независимо от того, где он находится в книге. Обходной путь — нажать клавишу F9, когда вы хотите обновить формулу CELL на активном листе. Это приведет к перерасчету.

Альтернативная формула для получения имени листа (формула MID)

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

Вместо функции RIGHT используется функция MID.

Ниже приведена формула:

Эта формула работает аналогично формуле ПРАВИЛЬНО, где она сначала находит положение квадратной скобки (с помощью функции НАЙТИ).

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

Извлечение имени листа и добавление к нему текста

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

Например, если у вас есть имя листа 2021, вы можете получить результат как "Сводка 2021" (а не просто название листа).

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

Ниже приведена формула, которая добавит текст «Сводка» перед названием листа:

Оператор амперсанда (&) просто объединяет текст перед формулой с результатом формулы. Вы также можете использовать функцию CONCAT или CONCATENATE вместо амперсанда.

Точно так же, если вы хотите добавить какой-либо текст после формулы, вы можете использовать ту же логику амперсанда (т. е. поставить амперсанд после формулы, за которым следует текст, который вы хотите добавить).

Итак, это две простые формулы, которые можно использовать для получения имени листа в Excel.

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

Скачать рабочую тетрадь

Три способа использования имени листа Excel из значения ячейки

1. Использование функций MID, CELL и FIND

Используя функцию MID, функцию CELL и функцию FIND вместе, вы можете вставить имя листа Excel в качестве значения ячейки. Рассмотрим следующий набор данных. Здесь мы хотим вставить имя листа Excel «Mark» в качестве имени продавца в ячейку B6.

Введите формулу в ячейку B6,

После нажатия клавиши ВВОД вы получите имя листа Excel в качестве значения ячейки.

Если вы измените имя листа, значение вашей ячейки изменится автоматически.

2. Использование функции ДВССЫЛ

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

Рассмотрите следующий набор данных. Здесь мы хотим узнать количество ноутбуков, проданных разными продавцами. У нас есть разные листы, названные в соответствии с продавцами. Имена листов вставляются в ячейки B6 и B7. На каждом листе у нас есть количество различных товаров, проданных этим конкретным продавцом. Теперь мы извлечем количество проданных ноутбуков из разных листов Excel, используя это имя листа Excel в качестве значений ячеек.

Теперь введите формулу в ячейку C6,

=ДВССЫЛ(B6&"!D6")

После нажатия Enter вы получите значение ячейки D6 из листа с именем «Jhon»

Аналогичным образом вы можете получить значение для листа с именем «Antony»

3. Имя листа из значения ячейки с помощью VBA

Мы можем создать имя листа Excel из любого значения ячейки с помощью приложения Visual Basic (VBA). Рассмотрим следующий набор данных. Здесь мы назовем лист Excel именем продавца в ячейке B6.

Сначала щелкните правой кнопкой мыши имя листа на вкладке имени листа и выберите "Просмотреть код".

Появится новое окно с именем Microsoft Visual Basic для приложений. Введите следующий код в этом окне,

Сохраните окно и закройте его.

После этого имя листа Excel будет изменено на значение ячейки B6.

Заключение

Надеюсь, теперь вы можете использовать имена листов Excel из значений ячеек. Если у вас возникнут какие-либо затруднения, оставьте комментарий, и я постараюсь устранить ваше недоразумение.

Читайте также: