Excel листы разные ячейка одна

Microsoft Excel

трюки • приёмы • решения

Как суммировать данные с разных листов книг 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 указать ссылки на одну и ту же ячейку, но с разных листов (Функция ДВССЫЛ)

Один из читателей нашего сайта SirExcel задал вопрос, который может встречаться очень часто. Вопрос заключался в следующем « Как сделать так, чтобы на одном листе показывалась информация из определенной ячейки к примеру $A$1 но с каждого листа из книги? Листов очень много надо сделать сводный список содержимого определенной ячейки со всех листов.»

Я как раз собирался написать о том, как работает функция ДВССЫЛ, почему бы не рассмотреть ее именно на этом примере.

Итак, мы имеем файл Excel, в котором много листов. Давайте рассмотрим простой пример, где названия листов у нас не менялись. То есть у нас есть Лист1, Лист2, Лист3 и так далее.

Допустим, что на каждом листе (Лист1-Лист5) у нас есть данные по выручке 5 различных магазинов в виде одинаковых таблиц данных.

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

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

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

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

ДВССЫЛ – функция, которая возвращает ссылку заданную текстовой строкой.

Чтобы понять принцип действия данной функцию, давайте рассмотрим такой пример.

Посмотрите на рисунок ниже. В ячейке А1 написан текст D4, а в самой ячейке D4 указано число 9999. Если мы напишем формулу ДВССЫЛ(A1), то в результате получим число 9999. То же самое мы получим если напишем формулу так:
=ДВССЫЛ(«D4»)
То есть мы написали текстом адрес ячейки D4 и функция ДВССЫЛ вернула нам то значение, которое находится по данному адресу (D4).

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

=Лист1!B2 (Выручка за январь магазина 1)

=Лист2!В2 (Выручка за январь магазина 2) и т.д

Но сложность заключается в том, что если ячейку просто протянуть вниз, то Лист1 не будет меняться на Лист2 и так далее.

Если мы пропишем формулу
ДВCСЫЛ(«Лист1!B2»), ДВCСЫЛ(«Лист2!B2»)
и так далее, то функция будет возвращать нам то же самое, но это так же не решает нашу проблему, ведь «Лист1!B2» прописан обычным текстом и так же не будет изменяться при протягивании.

Для решения нашей задачи мы разделим текст «Лист1!B2» на две части (отдельно «Лист1» и отдельно «!B2» — обратите внимание на восклицательный знак во второй части текста) и потом их склеим. Текст Лист1, Лист2 и так далее мы пропишем напротив соответствующих магазинов, при этом написав Лист1, мы сможем просто протянуть ячейку вниз и автоматически получить список Лист1, Лист2 и так далее.

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

Итоговая формула выглядит именно так, но я специально расписал с самого начала по шагам, чтобы было понятно из чего состоит данная формула. В принципе, этого уже достаточно, чтобы за короткий срок решить задачу, указанную в начале статьи. Чтобы заполнить данные за февраль, нам необходимо в ячейке D2 написать такую же формулу, но поменять текст «!B2» на «!B3»
=ДВССЫЛ(A2&»!B3″)
и протянуть вниз, аналогично за март.

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

Итак, мы решили задачу, как быстро заполнить данные за определенный месяц, но нам необходимо заполнить данные за остальные месяца. Мы знаем, что данные за Январь соответствуют ячейке B2, февраль — B3, Март — B4. То есть буква «B» остается неизменной, меняется лишь цифра. Зная это мы просто разобьем текст внутри ссылки ДВССЫЛ не на 2 части, а на три. Добавим дополнительно строку над таблицей и напишем сверху цифры соответствующие ячейки месяца. Пропишем формулу.

=ДВССЫЛ( A3 &» !B «& C1 ), где A3 — это текст « Лист1 «, « !B » — это неизменный текст и С1 — это цифра 2 . Все это объединено с помощью знака & в общий текст « Лист1 !B 2 «. Нам также потребуется закрепить столбец A (с помощью знака $), чтобы он не менялся при протягивании формулы вправо и строку 1, чтобы она не менялась при протягивании формулы вниз. Мы получаем следующую итоговую формулу.

=ДВССЫЛ($A3&»!B»&C$1)
которую можно протянуть вправо и вниз.

Заметки по теме:

• Если бы листы назывались у нас Магазин 1, Магазин 2 и так далее, то дополнительный столбец со словами Лист1, Лист2 и так далее нам бы не потребовался.
• Функцию ДВССЫЛ используют часто тогда, когда требуется изменить ссылку на ячейку в формуле, не изменяя саму формулу.

Если вам понравилась статья, пожалуйста, нажмите +1 и «Мне нравится». Так же подписывайтесь на нашу рассылку или вступайте в нашу группу ВКонтакте, чтобы не пропустить наши следующие уроки по Excel

SirExcel — безграничные возможности Excel

Источник

Excel листы разные ячейка одна

Как объединить ячейки с другого листа / разных листов в Excel?

В общем, мы применяем функцию СЦЕПИТЬ для объединения ячеек из одного листа. Но иногда вам может потребоваться объединить ячейки из разных листов в Excel, как это легко решить? В этой статье показаны два метода, которые помогут вам сделать это легко.

Обычно мы можем применить функцию СЦЕПИТЬ и указать ячейки из разных листов одну за другой, чтобы объединить их в Excel.

Выберите пустую ячейку, в которую вы поместите результат конкатенации, и введите формулу = CONCATENATE (Чай! A1, «», Кофе! A1, «», ‘Безалкогольный напиток’! A1, «», Молоко! A1, «», Вода! A1) (A1 — это ячейка, содержимое которой вы объедините, Чай / Кофе / Безалкогольные напитки / Молоко / Вода — это имена листов, содержащие конкретную ячейку, которую вам нужно объединить) в нее и нажмите Enter .

И тогда вы увидите, что содержимое указанных ячеек с разных листов объединено в одну ячейку. Смотрите скриншот:

Если существует множество листов, ячейки которых необходимо объединить в одну, приведенная выше формула может быть довольно утомительной. Здесь мы рекомендуем Kutools for Excel’s Динамически обращаться к рабочим листам Утилита для упрощения работы с конкатенацией в Excel.

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30 -день, кредитная карта не требуется! Бесплатная пробная версия сейчас!

Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Создайте новый лист в Excel, и на новом листе выберите ячейку с тем же адресом ячейки, что и те ячейки, которые вы объедините из других листов, говорит Ячейка A1, и нажмите Кутулс > Больше > Динамически обращаться к рабочим листам. Смотрите скриншот:

2. В открывшемся диалоговом окне Fill Worksheets References,

(1) Выберите Заполнить горизонтально ячейку за ячейкой из Заполнить заказ раскрывающийся список;

(2) Укажите рабочие листы, ячейки которых вы объедините в Список рабочих листов разделе.

(3) Щелкните значок Диапазон заполнения и закройте диалоговое окно «Заполнить ссылки на листы». Смотрите скриншот:

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30 -день, кредитная карта не требуется! Get It Now

И теперь все содержимое ячейки A1 указанных листов извлекается и перечисляется в новом листе.

3. Выберите пустую ячейку, в которую вы поместите результат конкатенации, и введите формулу = СЦЕПИТЬ (A1: E1 & «») (A1: E1 — это диапазон с содержимым ячеек, извлеченным из других листов), выделите в нем A1: E1 & «» в формуле и нажмите F9 key, затем удалите фигурные скобки <и >в формуле, наконец, нажмите Enter ключ. Смотрите скриншот:

И тогда вы увидите, что все ячейки из указанных листов сразу объединены в одну ячейку.

Источник

Excel листы разные ячейка одна

Как выбрать один и тот же диапазон ячеек на разных листах в Excel?

Можно ли быстро выбрать один и тот же диапазон ячеек на разных листах? Или даже быстро вводить одни и те же данные в один и тот же диапазон ячеек на разных листах, не вводя каждый лист один за другим? Да, следующие уловки помогут вам выбрать один и тот же диапазон ячеек на разных листах в Microsoft Excel.

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

Выберите один и тот же диапазон ячеек на разных листах в Excel

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

Шаг 1: На вкладке «Лист» в Microsoft Excel выберите несколько листов вместе, удерживая Ctrl ключа или Shift ключ. См. Следующий снимок экрана:

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

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

Примечание: делая это, он не может заставить все листы текущей книги указывать на один и тот же номер строки. Например, вы прокручиваете активный лист, чтобы выбрать диапазон (A500: G550), и на всех выбранных листах будет выбран один и тот же диапазон, но он не поместит все выбранные листы с одинаковым номером строки.

Выберите тот же диапазон ячеек на всех листах с помощью Kutools for Excel

Kutools for Excel‘s Синхронизировать рабочие листы Инструмент также может помочь вам быстро выбрать один и тот же диапазон ячеек на всех листах.

Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 30 дней. Получить сейчас.

Шаг 1: Выберите диапазон ячеек на одном листе.

Шаг 2: Нажмите Предприятие > Инструменты рабочего листа > Синхронизировать рабочие листы. Смотрите скриншот:

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

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

Источник