Формулы и функции в Excel

Формула представляет собой выражение, которое вычисляет значение ячейки. Функции – это предопределенные формулы и они уже встроены в Excel.

Например, на рисунке ниже ячейка А3 содержит формулу, которая складывает значения ячеек А2 и A1.

Формулы и функции в Excel

Ещё один пример. Ячейка A3 содержит функцию SUM (СУММ), которая вычисляет сумму диапазона A1:A2.

Формулы и функции в Excel

Введение

Этот раздел предназначен для пользователей, не знакомых с Excel.

Microsoft Excel – одно из самых широко используемых приложений в истории. Сотни миллионов людей во всём мире пользуются Microsoft Excel. Excel умеет работать с данными любого типа и выполнять финансовые, математические и статистические вычисления.

    : Диапазон в Excel – это набор из двух или более ячеек. В этой главе Вы найдёте обзор некоторых очень важных операций с диапазонами.Примеры: Последовательность Фибоначчи, Пользовательские списки, Примечания, Скрытие строк и столбцов, Пропускать пустые ячейки, Транспонирование, Объединение и пересечение. : Формула – это выражение, которое вычисляет значение ячейки. Функции – это предопределённые формулы, доступные в Excel.Примеры: Процентное изменение, Имена в формулах, Динамический именованный диапазон, Параметры вставки, Строка состояния, Быстрые операции.

Особенности трехмерных ссылок

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

  • Добавление/копирование листа. Если добавить новый лист или скопировать уже существующий и вставить его между первым и последним (используемых в формуле), то он автоматически будет участвовать в расчете.
    Т.е. сама формула не поменяется, а значение вполне может измениться, если на новом листе в тех же ячейках содержатся данные;
  • Удаление листа. Аналогичный случай примеру выше. При удалении листа формула не изменится, а расчет поменяется в зависимости от удаленных данных (если удалить начальный или конечный листы, то формула изменится, промежуточный — формула останется неизменной);
  • Перемещение листа. При перемещении листа в другое место книги формула не изменится, но поменяется набор листов между ними (либо добавятся новые, либо удалятся текущие).

Если описание покажется несколько сложным, в этом случае попробуйте поэкспериментировать с формулой и перестановкой листов на конкретном примере и поймите по какому принципу она работает.

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

Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — пишите и спрашивайте в комментариях.

Примеры использования функций ЛИСТ и ЛИСТЫ в формулах Excel

Вариант 1: Пользовательская функция VBA

в стандартном модуле:

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

Вариант 2: Именованная формула + XLM4.0 (макрофункции)

— меню Вставка-Имя-Присвоить.
— имя: SheetsList
— формула: =ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1+0*ТДАТА())
— кнопка: Добавить
— кнопка: ОК

см. примеры в приложении.

KL
[MVP — Microsoft Excel]

KL
[MVP — Microsoft Excel]

а если просто!

В чём разница?

Надо проверить, но возможно разницы не будет, т.к. Worksheets(lngIndex).Name вызываемый из стандартного модуля будет ссылаться на ActiveWorkbook по-умолчанию 🙂

Как суммировать данные с разных листов книг Excel

Формулы могут работать с ячейками из других листов. Вам просто нужно указать перед ссылкой на ячейку имя листа и восклицательный знак. Например, следующая формула добавляет 12 к значению в ячейке С1 , которая находится на листе Лист2: =Лист2!С1+12 .

Что делать, если вам нужно вычислить сумму всех значений в С1, начиная с листа Лист2 и заканчивая листом Лист6? Следующая формула решает эту задачу: =СУММ(Лист2:Лист6!C1) . В данном случае двоеточие разделяет название первого и последнего листов.

Для создания такой формулы выполните приведенные ниже действия.

  1. Активизируйте ячейку, которая будет содержать формулу, и наберите =СУММ( .
  2. Щелкните на вкладке первого листа (в нашем случае это Лист2) и выберите ячейку С1 .
  3. Нажмите Shift и щелкните на вкладке последнего листа (в нашем случае это Лист6).
  4. Нажмите Enter, и формула будет введена в ячейку.

В шаге 2 вы можете выбрать диапазон, а не одну-единствениую ячейку. Например, эта формула возвращает сумму C1:F12 для всех листов от Лист2 до Лист6: =СУММ(Лист2:Лист6!С1:Р12) .

Теперь я покажу вам интересный прием, который узнал при прочтении новостных конференций Excel и на обучение в Минске. Если вы хотите просуммировать одну и ту же ячейку со всех листов, кроме текущего, просто введите формулу наподобие этой: =СУММ(‘*’!C1) . Звездочка служит подстановочным символом, который интерпретируется как «все листы, кроме этого одного». Когда вы нажмете клавишу Enter после ввода этой формулы, Excel преобразует формулу, чтобы она использовала фактические имена листов. Это работает, даже если активный лист находится где-нибудь в середине, между другими листами. Например, если книга состоит из шести листов и вы введете приведенную выше формулу в ячейку листа Лист3, Excel создаст следующую формулу: =СУММ(Лист1:Лист2!С1;Лист4:Лист6!C1) .

Но это еще не все. Введите следующую формулу для нахождения суммы ячеек С1 во всех листах, которые начинаются со слова Регион: =СУММ(‘Регион*»!C1) . Excel может преобразовать данную формулу во что-то наподобие этого: =СУММ(Регион1:Регион4!C1) .

Вы также можете использовать подстановочный знак ? — он указывает на любой отдельный символ. Например, при вводе следующей формулы Excel создаст формулу, которая просуммирует значения, начиная с листа Лист1 и заканчивая листом Лист9 (названия, которые содержат одну цифру): =СУММ(‘Лист?’!C1). Этот прием не ограничивается функцией СУММ. Он работает и с другими функциями, такими как СРЗНАЧ, МИН, МАКС.

Как создать список всех листов, содержащихся в книге Excel при помощи макрофункции

Странно, но в Excel невозможно напрямую сгенерировать список имен листов из книги. В этой статье будет рассказано, как создать список всех листов, содержащихся в книге. Как и в предыдущей статье, здесь мы будем использовать макрофункцию из Excel 4 XLM в именованной формуле.

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

  1. Вставьте новый лист, на котором будет находиться список.
  2. Выполните команду Формулы ► Присвоить имя, чтобы открыть диалоговое окно Создание имени.
  3. Впишите в поле Имя название Список.
  4. Введите следующую формулу в поле Диапазон (рис. 9.1): =ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ(«]»;ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));»») .
  5. Осталось нажать ОК, чтобы закрыть диалоговое окно Создание имени.
Читайте также:  Специальная вставка в Excel для массового сложения значений

http://www.kids.ua/

Рис. 9.1. Работа с окном Создание имени для создания именованной формулы

Обратите внимание: в формуле используется функция ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ, которая не относится к числу стандартных функций рабочего листа. Это макрофункция, написанная в сравнительно старом XLM-стиле и предназначенная для применения на специальном листе макросов. При использовании аргумента 1 она возвращает массив имен листов, причем перед именем каждого листа указано название книги, откуда он взят. Функции ЗАМЕНИТЬ и НАЙТИ удаляют имя книги, сопутствующее названию листа. Чтобы сгенерировать имена листов, введите в ячейку А1 следующую формулу, а затем скопируйте ее ниже по столбцу: =ИНДЕКС(Список;СТРОКА()) .

На рис. 9.2 приведена данная формула для диапазона А1:А10. В книге семь листов, поэтому формула возвращает ошибку #ССЫЛКА! при попытке отобразить имя несуществующего листа. Чтобы избежать этого, измените формулу следующим образом: =ЕСЛИОШИБКА(ИНДЕКС(Список;СТРОКА());»») .

Рис. 9.2. Использование формулы для отображения списка имен листов

Рис. 9.2. Использование формулы для отображения списка имен листов

Список названий листов можно откорректировать, если вы будете добавлять, удалять или переименовывать листы, но это не происходит автоматически. Чтобы принудительно обновить формулы, нажмите Ctrl+Alt+F9. Если вы хотите, чтобы имена листов автоматически обновлялись при пересчете книги, измените именованную формулу следующим образом, чтобы сделать ее меняющейся: =ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ(«]»;ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));»»)&Т(ТДАТА()) .

Рис. 9.3. Создание списка гиперссылок

Рис. 9.3. Создание списка гиперссылок

Зачем может понадобиться список названий листов? На рис. 9.3 показано оглавление, созданное с помощью ссылки ГИПЕРССЫЛКА. В ячейке В1 записана следующая формула: =ГИПЕРССЫЛКА(«#»&A1&»!A1″;»Перейти по ссылке») . Щелкнув на гиперссылке, вы активизируете лист и выделяете ячейку А1. К сожалению, Excel не поддерживает гиперссылок, ведущих на лист с диаграммами, поэтому при попытке сослаться на такой лист программа выдаст ошибку.

Если вы пользуетесь такими приемами, то книгу нужно сохранить как файл с поддержкой макросов (в формате *.xlsm или *.xls).

Присвоение ячейке имени листа (Формулы/Formulas)

​ формул в VBA​​ там должна быть​ продвинутых. Мужчин и​ например, непонятен.​ сказать помягшее, -​

​ постараюсь свести к​​Так будет имя​
​Guest​

​ (Лист1 на Лист2​​ содержащихся между двумя​ЯЧЕЙКА(«имяфайла») – функция, возвращающая​

​ книге, будет возвращена​​.​
​ ячейке​Также предполагается, что имя​ дигонали», видят вопрос​ языке крутится список​ (типа прописать Application.Calculate)​ ссылка на ячейку​
​ женщин. Мужчин -​Но когда Вы​ своебразной дамскоантиэкселевской логики. ​ минимуму цитирование.​
​ листа.​: не-а, хотя за​ и т.д.), а​
​ указанными листами, запишем​
​ текст, в котором​ ошибка #НД.​Функция ЛИСТ в Excel​​B1​​ файла не содержит​ — дают ответ.​ листов книги, когда​ не помогают​ того листа, имя​​ больше. Причину мы​​ по выбранной Вами,​
​ И как потом​Юрий М​Werty​ помощь спасибо. Нужно​ чтоб имя листа​
​ формулу:​ функция ПСТР выполняет​

​Если в качестве аргумента​​ возвращает числовое значение,​формулу =ЯЧЕЙКА(«адрес»;лист2!A1) Формула​ символа закрывающая квадратная​Тут Вам замечание:​
​ просят имя листа​
​Vik_tor​

​ которого выводим -​​ (все) уже обсудили.​ ​ не лучшей, на​ ​ общие бабки подбивать,​: Перечитайте Правила и​: Вот с форума​ получить диапазон, точнее​

Как составить список листов через VBA

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

  1. Открываем VBA с помощью комбинации клавиш «Alt+F11». Если при нажатии F11 снижается яркость или срабатывает другая функция, установленная на эту кнопку, зажмите клавишу Fn.
  2. Нажмите «Вставить» (Insert) на верхней панели и выберите в открывшемся меню пункт «Модуль» (Module).

Как получить список листов книги Excel

  1. Вставляем в свободное поле этот текст:

Function SheetList(N As Integer)

SheetList = ActiveWorkbook.Worksheets(N).Name

End Function

  1. Далее можно закрыть окно Visual Basic, потому что этот инструмент больше не понадобится, а функция уже добавлена в программу.

Как получить список листов книги Excel

  1. Открываем лист для списка и вводим формулу в начальную ячейку. Теперь не нужно длинное выражение, чтобы создать список листов. Новая формула выглядит так: =SheetList(СТРОКА()).
  2. Нажимаем Enter и получаем название листа в ячейке. Маркером заполнения создаем список.

Как получить список листов книги Excel

  1. Для гиперссылок придется использовать ту же длинную формулу: =ГИПЕРССЫЛКА(“#”&A1&”!A1″;””&A1).

Листы Excel состоят из ячеек, которые сгруппированы по строкам и столбцам. Строкам присвоены номера, а столбцам — буквы A, B, C или цифры в зависимости от текущих настроек программы.

На листе больше миллиона строк и 16 000 столбцов, и вы можете вставлять формулы в любые ячейки на их пересечении.

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

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

Числовой тип данных содержит только цифры, запятую* (знак десятичной дроби) или знак минус (для отрицательного числа). Посторонние символы и пробелы не допускаются. Над числами могут производиться любые математические действия.

Числа отображаются в ячейках с правого края, бывают:

  1. Обычные (0,33; 256,44; 1768 и т.д. до 15 цифр);
  2. Дата (01.01.1900 – 31.12.9999, от 1 до 2958465);
  3. Время (от 00:00:00 до 23:59:59, от 0 до 0,99).

* Запятая, знак разделителя для программы на русском языке, для ENG версии разделителем будет точка.

На что способны скрытые функции — создание списка листов файла excel

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

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

Предположим, у нас есть файл с некоторым количеством листов:

Читайте также:  Как применить в условном форматировании несколько условий

Для того, чтобы создать список, перейдите на новый лист. Во вкладке Формулы нажмите Определенные имена -> Задать имя. В открывшемся окне "Создание имени" присвойте имя вашему списку, в строке "Диапазон" пропищите формулу: =ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));"") и нажмите ОК:

Функция ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ имеет два аргумента:

1. ном_типа — цифра, обозначающая тип информации, который мы хотим получить о книге excel. Один в нашем случае означает имена всех листов рабочей книги;

2. имя_текст — название рабочей книги, если оно опущено — используется активная рабочая книга.

После этого в ячейке, где будет находиться ваш список введите формулу: =ЕСЛИОШИБКА(ИНДЕКС(Список;СТРОКА());"") и протяните ее вниз:

Функция ЕСЛИОШИБКА оставит пустыми ячейки, для которых не хватило названий листов.

✔ Ищите ответы на свои вопросы в статье Фишки excel — подборка статей . Там я перечислила все свои статьи по разбору работы функций excel.

✔ Вот здесь есть подборка видео о том, как строить необычные диаграммы — Фишки Excel — подборка видеороликов .

Определяем имя листа в EXCEL

Если формула вводится в новую книгу, то ее сначала нужно сохранить. После сохранения, возможно, потребуется нажать клавишу F9 (Вкладка Формулы , Группа Вычисление , Пересчет ).

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

Для этого нужно, чтобы второй аргумент содержал ссылку на ячейку другого листа, имя которого и будет определено. Если формула =ЯЧЕЙКА(«адрес»;лист2!A1) находится на листе1 в ячейке B1 , то имя листа ( Лист2 ) можно определить по формуле:

В этом случае имя листа не должно состоять только из цифр, т.к. если в качестве названия листа использовано число, то функция ЯЧЕЙКА() возвращает название книги и листа в апострофах (‘) и формула вернет неправильный результат. Справиться с этим поможет формула =ПОДСТАВИТЬ(C1;»‘»;»») , которая удалит символ апострофа.

Также предполагается, что имя файла не содержит символа закрывающая квадратная скобка ( ] ).

ВНИМАНИЕ ! Иногда, когда открыто несколько книг, функция ЯЧЕЙКА() может работать некорректно. Для восстановления работоспособности формулы нужно нажать клавишу F9 ( Формулы/ Вычисления/ Пересчет ).

ПРИМЕНЕНИЕ: При изменении имени листа, все ссылки в формулах автоматически обновятся и будут продолжать работать. Исключение составляет функция ДВССЫЛ() , в которой имя листа может фигурировать в текстовой форме ДВССЫЛ(«Лист1!A1») . В статье Определение имени листа для использования в функции ДВССЫЛ() показано как использовать функцию ЯЧЕЙКА() , чтобы сохранить работоспособность формулы с функцией ДВССЫЛ() .

Определение имени листа в EXCEL для использования в функции ДВССЫЛ()

При изменении имени листа, все ссылки в формулах автоматически обновятся и будут продолжать работать. Исключение составляет функция ДВССЫЛ() , в которой имя листа может фигурировать в текстовой форме ДВССЫЛ(«Лист1!A1») . В статье показано как использовать функцию ЯЧЕЙКА() , чтобы сохранить работоспособность формулы с функцией ДВССЫЛ() .

Имя листа можно определить с помощью функции ЯЧЕЙКА() , об этом читайте в статье Определяем имя листа .

Предположим, что в ячейке B 4 на Листе1 имеется формула =ДВССЫЛ(«лист2!A1») . Если Лист2 переименовать в Лист3 , то вышеуказанная формула работать не будет. Чтобы работоспособность формулы сохранилась — определим имя листа с помощью функции ЯЧЕЙКА() (см. файл примера ).

  • Запишем на Листе1 в ячейке B1 формулу =ЯЧЕЙКА(«адрес»;лист2!A1) Формула вернет результат [_Определяем_имя_листа.xlsx]Лист2!$A$1 , т.е. полный адрес ячейки с указанием названия книги и имени листа (ссылка должна быть на столбец с названием из одной буквы, например, ссылка лист2!AВ1 не годится);
  • Предполагая, что название книги не содержит квадратных скобок [ ], запишем формулу для изъятия из полученного результата имени листа: =ПСТР(B1;ПОИСК(«]»;B1)+1;ДЛСТР(B1)-ПОИСК(«]»;B1)-5) ;
  • Если в качестве названия листа использовано число, то функция ЯЧЕЙКА() возвращает название книги и листа в апострофах (‘), например, ‘[_Определяем_имя_листа.xlsx]123456’!$A$1 , что может привести к ошибке при определении имени листа;
  • Записав в ячейке В2 формулу =ЕСЛИОШИБКА(ПОИСК(«‘»;B1);0) , получим, что если название листа – число, то результат =1, если текст, то 0;
  • Слегка модифицируем формулу в ячейке B3 для определения названия листа: =ПСТР(B1;ПОИСК(«]»;B1)+1;ДЛСТР(B1)-ПОИСК(«]»;B1)-5-B2) ;

Теперь, записав вместо формулы =ДВССЫЛ(«лист2!A1″) формулу =ДВССЫЛ(B3&»!A1″) , мы решим задачу: изменение имени Листа2 не повлияет на работоспособность формулы.

Внимание! Иногда, когда открыто несколько книг, функция ЯЧЕЙКА() может работать некорректно. Для восстановления работоспособности формулы нужно нажать клавишу F9 ( Формулы/ Вычисления/ Пересчет ).

ПРИМЕЧАНИЕ : С помощью обычных формул (не VBA) невозможно определить имя активного листа и адрес активной ячейки .

Справочник по часто употребляемым функциям в Microsoft Excel

Формулы (функции) в Excel представляют собой конструкции, позволяющие выполнять вычисления, возвращать данные, манипулировать данными ячеек, проверять условия и т. д. В Microsoft Excel в формулах используются четыре типа операторов: арифметические, текстовые, операторы сравнения и операторы ссылок.

Формула в Microsoft Excel всегда начинается со знака равенства (=). Знак равенства свидетельствует о том, что следующие за ним знаки составляют формулу. Формула в Excel вычисляется слева направо в соответствии с порядком, определенным для каждого оператора в формуле.

Ниже представлена группировка формул в соответствии с Microsoft Excel. На каждую формулу имеется авторская статья о применении этой функции.

Формула. Как получить диапазон листов с условием в Excel?

Как получить диапазон листов со следующим условием для формулы в Excel? :
листы, имя которых содержит определённую подстроку , равную значению из некоторой ячейки.

5bd2c98029934868171869.png

P.S Т.е пользователь вводит значение в заранее определенную ячейку метку для поиска листов и автоматически формула применяется только к определенным листам. Для фото выше это будет например слово «Компонент», т.е это будут все листы содержащие подстроку — Компонент

Покажите пример такой формулы пожалуйста.

  • Вопрос задан более двух лет назад
  • 181 просмотр
  • Facebook
  • Вконтакте
  • Twitter
  • Google

bopoh13

bopoh13

Вообще мне известно четыре способа получения имени листов таблицы.
Три тут:
https://www.datanumen.com/blogs/3-quick-ways-to-ge.
— вручную
— через name manager и формулы
— через VBA

Выбираете любой, создаете диапазон с именами листов, сравниваете с целевым значением.

bopoh13

Да, но она штатная. С ее помощью вполне легко именно автоматически получить список листов.
Кодинга не требуется. Макросы разрешать не нужно, емнип. Содержащая такое решение книга сохраняется в xlsx, нее в xlsb. Так что сложностей с ним не вижу.

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

А вообще вопрос задан очень обще, не совсем понятно, что автору нужно на выходе — названия листов или сами листы в отдельном файле )

Вообще по всем признакам похоже на домашнее задание 🙂

Xambey, честно, не очень представляю
^)

Если это практическая задача, неплохо бы понять, зачем именно так, и для чего — тогда можно предложить подходящее решение.

John Smith, нет, это не домашнее задание:) Пытаюсь автоматизировать отчет по тестированию и есть необходимость не писать некоторые формулы по 50 раз. И если получиться использовать список листов для СЧЁТЕСЛИ(range, object), то это сильно упростит работу моему отделу.

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

Раз такое дело, даже скину конкретный файл. На листе Summary в таблице Тесты функционала для второго столбца не хочется добавлять по отдельной формуле для каждого листа. Хотелось бы просто на листе Initial добавить 1 ячейку, куда записывается метка для поиска листов по имени — типо как «Компонент». И тогда все листы имеющие в имени слово Компонент обрабатываются в таблице тестов. Как то так:) Файл: https://yadi.sk/d/RS_MmckmDDOfNA

bopoh13

Метод через name manager не очень подходит, т.к., как выяснилось при натурных испытаниях, результат при изменении названий\удалении или добавлении листов обновляется, только если зайти в эти ячейки и нажать enter. Это может быть неудобно. Пробовал другими способами, но рабочего способа не нашел.

Я бы делал через макрос VBA, тем более, что у вас уже xlsm — это гораздо удобнее и эффективнее (не говоря о том, что если формулами что-то такое и можно сделать, в чем я начинаю сомневаться, то это будет монструозно).

Решение легко написать, предварительно погуглив
«excel vba enumerate sheets»
«excel vba index sheet»
Затем в код добавить проверку на наличие подстроки «Компонент», и обработку таких листов.

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

KEugene

Допустим, у нас есть два листа:
Лист1
Лист2
На листе Лист1 в А1 пишем «Лист2» без кавычек.
На листе Лист2 в А1 пише «ФФФ» без кавычек
На листе Лист1 в А2 пишем формулу
=ДВССЫЛ(A1&»!A1″)
Получаем в ячейке значение ФФФ.

Работа с формулами Excel предполагает не только написать формулу, но и заменить ссылки в формулах, при необходимости.

Чтобы поменять в формулах Excel ссылки на другой лист, нужно воспользоваться функцией «Найти и выделить».

Допустим, мы копируем лист Excel (лист 1) с таблицей, в которой есть формулы со ссылкой на «лист 2». Нам надо поменять в скопированной таблице в формулах ссылку «лист 2» на ссылку «лист 3».

Удобнее здесь выделить диапазон и заменить разом ссылку во всех нужных нам формулах. Делают это с помощью макросов.

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

Но, здесь есть нюансы, поэтому рассмотрим подробно на примере. В примере все таблицы придуманы для этого примера, поэтому не ищите в них бухгалтерскую логику. Итак, есть первый лист «январь», в нем, список сотрудников и их доход.

Второй лист «касса январь», в нем — суммы по сотрудникам.

Третий лист «Итого», в нем таблица «итого январь».

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

Смотрите строку формул. Здесь стоит абсолютная ссылка, но может стоять и относительная. Есть еще такие же два листа, но: «февраль» и «касса февраль».

Для этих таблиц мы на листе «Итого» копируем таблицу «итого январь» и назовем ее «итого февраль». В этой, скопированной таблице «итого февраль» на листе «Итого», нам надо изменить ссылки с январских листов на февральские листы.

  1. В таблице, в которой будем менять ссылки, выделяем нужные ячейки (диапазон ячеек). Диапазон ячеек быстрее и удобнее выбрать по имени диапазона, особенно, если ячейки не смежные.
  2. На закладке «Главная» в разделе «Редактирование» нажимаем кнопку «Найти и выделить». В строке «Найти» пишем ссылку, которую будем менять.

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

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

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

Если нужно поменять ссылку в формуле Excel на всем листе, то нажмем кнопку «Заменить все», и заменятся ссылки во всех формулах листа, даже в тех, которых нет в списке. Можно настроить список для удобной ориентировки в нем – это кнопка «Параметры».

Здесь можно настроить просмотр списка по строкам или по столбцам, искать формулы на этом листе или в книге. Всё, ссылки в формулах заменены. Также меняем ссылки «`касса январь`!» на ссылку «`касса февраль`!».

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

В таблице Excel можно закрепить верхние строки (шапку таблицы) и левые столбцы в таблице. И, тогда, при прокрутке таблицы вниз или в сторону, эти строки и столбцы всегда будут видны на экране монитора. Так удобнее работать с большой таблицей.

Ссылка на основную публикацию