gototopgototop

Материалы Excel

Сейчас на сайте

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

Поменять ссылки на другие листы в формулах Excel.

     Работа с формулами Excel предполагает не только написать формулу, но и заменить ссылки в формулах, при необходимости. Как написать разные формулы, где применить формулы, кроме подсчета данных, смотрите в статье "Формула в Excel".
      Чтобы поменять в формулах Excel ссылки на другой лист, нужно воспользоваться функцией "Найти и выделить". 
     Допустим, мы копируем лист Excel (лист 1) с таблицей, в которой есть формулы со ссылкой на «лист 2». Нам надо поменять в скопированной таблице в формулах ссылку «лист 2» на ссылку «лист 3». Удобнее здесь выделить диапазон и заменить разом ссылку во всех нужных нам формулах. Делают это с помощью макросов.
     Но мы применим другой вариант, немного трудоемкий, но все равно легче и быстрее, чем вручную писать ссылку в каждой формуле. Делаем замену ссылки функцией «Найти и выделить», расположенной на закладке "Главная".
     Но, здесь есть нюансы, поэтому рассмотрим подробно на примере. В примере все таблицы придуманы для этого примера, поэтому не ищите в них бухгалтерскую логику.
     Итак, есть первый лист «январь», в нем, список сотрудников и их доход.
Второй лист «касса январь», в нем - суммы по сотрудникам.
Третий лист «Итого», в нем таблица «итого январь».
     В таблицу "итого январь" переносится по формуле из листа «январь»  итоговая сумма по каждому сотруднику и из листа «касса январь» переносится сумма по каждому сотруднику.
     Получается, в таблице "итого январь" на странице «Итого» стоят в формулах ссылки на страницы «январь» и «касса январь». Смотрите строку формул. Здесь стоит абсолютная ссылка, но может стоять и относительная.
     Есть еще такие же два листа, но: «февраль» и «касса февраль».
Для этих таблиц мы на листе «Итого» копируем таблицу «итого январь» и назовем ее «итого февраль».
     В этой, скопированной таблице «итого февраль» на листе «Итого», нам надо изменить ссылки с январских листов на февральские листы.
     1. В таблице, в которой будем менять ссылки, выделяем нужные ячейки (диапазон ячеек). Диапазон ячеек быстрее и удобнее выбрать по имени диапазона, особенно, если ячейки не смежные. Читайте в статье  "Что такое диапазон в Excel".
     2. На закладке «Главная» в разделе «Редактирование» нажимаем кнопку «Найти и выделить». Смотрите статью  "Поиск в Excel" тутВ строке «Найти» пишем ссылку, которую будем менять.
Нажимаем кнопку «Найти все». Вышел список ячеек с формулами с ссылкой «январь!».
     Нюанс!
Благодаря тому, что мы предварительно отметили диапазон с нужными нам ячейками, в списке будут показаны ячейки только из отмеченного диапазона. Если бы не выделяли предварительно диапазон, то в списке были бы ячейки со всего листа. В т.ч., и за январь, где нам не надо менять ссылки. Это бы затруднило нашу работу.
Нажимаем вкладку «Заменить» и в строке «Заменить на» пишем новую ссылку, которую нам надо. 
А вот теперь придется поработать над каждой ячейкой. Наводим курсор на каждую строку списка ячеек и затем нажимаем кнопку «Заменить».
      Если нужно поменять ссылку в формуле Excel на всем листе, то нажмем кнопку «Заменить все», и заменятся ссылки во всех формулах листа, даже в тех, которых нет в списке.
Можно настроить список для удобной ориентировки в нем – это кнопка «Параметры». Здесь можно настроить просмотр списка по строкам или по столбцам, искать формулы на этом листе или в книге.
     Всё, ссылки в формулах заменены.
Также меняем ссылки «'касса январь'!» на ссылку «'касса февраль'!». В этом названии ссылки нужно указывать и одинарные кавычки, писать строго, как написано в формуле.
Посмотреть о видах ссылок можно в статье «Относительные и абсолютные ссылки в Excel».
     В таблице Excel можно закрепить верхние строки (шапку таблицы) и левые столбцы в таблице. И, тогда, при прокрутке таблицы вниз или в сторону, эти строки и столбцы всегда будут видны на экране монитора.  Так удобнее работать с большой таблицей.
     Как сделать это, смотрите в статье "Как закрепить строку в Excel и столбец".