Excel 47. Рабочая книга

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

По окончании этого урока вы сможете:

  1. Перемещать и копировать рабочие листы между файлами
  2. Связывать данные из разных рабочих книг

1. Перемещение и копирование рабочих листов между файлами

В статье «Excel 7. Рабочие листы» я рассказала, как можно работать с листами в пределах одной книги. Но листами можно обмениваться и между другими книгами. В той статье я предложила вам работать с командой «Перемесить или скопировать…» из контекстного меню имени (ярлычка) листа. Как и практически все команды в прикладных программах Microsoft Office эта команда продублирована в другом месте.

Скачайте файл тут и откройте его. Заодно создайте новый файл, который назовите «Отчет», или «Новая рабочая книга», или как-нибудь на свое усмотрение.

Шаг 1. Лента Главная → группа команд Ячейки → команда Формат → команда «Перемесить или скопировать…» из раскрывающего меню:

рабочая книга Excel

Появится знакомое уже окно:

рабочая книга Excel

В раскрывающемся списке есть только наш рабочий файл и Новая книга. Как видите, нам нет необходимости создавать новую таблицу. Мы просто перенесем нужные листы в новую книгу, а потом не забудем сохранить её. Для того, чтобы работать с уже имеющейся книгой, откроем уже созданный файл под названием «Отчет», а потом повторим Шаг 1.

Шаг 2. В раскрывающемся списке появилась рабочая книга «Отчет», которую мы выбираем. Обязательно поставим галочку «Сделать копию»:

рабочая книга Excel
Понять и запомнить!Если мы забудем поставить галочку, то лист в исходной книге исчезнет, что очень нехорошо. То есть мы осуществим операцию «вырезать → вставить». Будьте внимательны!

Смотрим на картинку:

рабочая книга Excel

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

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

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

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

Понять и запомнить!Копирование и вставка из буфера не обеспечивает связи между исходной ячейкой и той, в которую скопированы данные.

Чтобы данные в новых ячейках изменились так же, как и данные исходных ячеек, можно создать связь между двумя ячейками.

2. Связь с данными из других рабочих книг или 3D-ссылки

Работаем с двумя книгами. В моем случае это «Е47_Учебный файл – копия» и «Е47_Отчет».

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

  1. Наименование или список курсов
  2. Стоимость

Рассмотрим исходник.

рабочая книга Excel

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

(3)=(1)×(2)D4=C4*$D$2

Обращаем внимание, что в формуле будет меняться адрес только первого сомножителя, а вот второй сомножитель будет постоянный или абсолютный (статья «Excel 10. Диапазон и вычисления в нем»).

А теперь активируем книгу «Отчет» и создаем там новый лист (можете создать новую книгу).

Шаг 1. В книге «Отчет» на листе 1 выбираем ячейку A2 и вводим знак «=»:

ссылка на ячейку Excel

Шаг 2. Отмечаем в исходной книге ячейку B4 (первое имя курса в списке) и нажимаем Enter:

ссылка на ячейку Excel

Шаг 3. А поскольку у нас 4 курса, то в книге Отчет» протягиваем курсором заполнения ячеек на 4 строчки вниз:

ссылка на ячейку Excel

И видим неприятную картину. Все дело в том, что по умолчанию ссылка на ячейку в другой книге или на другом листе вставляется с абсолютным адресом:

='[Е47_Учебный файл – копия.xlsx]Курсы выездные’!$B$4

Эта ссылка несет информацию о рабочей книге, рабочем листе и ячейке, по которой мы щелкнули.

  1. Первый элемент ссылки, имя рабочей книги, заключен в квадратные скобки
  2. Конец второго элемента обозначен восклицательным знаком
  3. Третий элемент, ссылка на ячейку, содержит значки доллара перед номерами строки и столбца.
Понять и запомнить!Такая ссылка называется 3D-ссылкой. Она отражает три измерения (рабочая книга, рабочий лист и ячейка), которые нужны нам, чтобы указать на определенную ячейку в другой рабочей книге.

Поэтому ссылка всегда будет на одну и ту же ячейку. Что делать?

Давайте пройдем шаги 1 и 2 заново, но шаг 2 сделаем таким образом:

Шаг 2. Выделяем весь диапазон имен курсов B4:B7:

3D-ссылка Excel

И нажимает Enter:

3D-ссылкой

Вот теперь замечательно!

Конечно, надо задать перенесение по словам (статья «Excel 22. Объединение ячеек») поправить внешний вид, отрегулировать высоту и ширину ячеек (статья «Excel 23. Размер ячеек»). Заодно определить диапазон стоимости, который мы перенесем из книги в книгу.

Чтобы сделать ссылку на весь диапазон «Стоимость», надо проделать предыдущие шаги:

  1. Выделить ячейку в книге «Отчет» и ввести знак «=»
  2. Выделить диапазон D4:D7
  3. Нажать Enter:
3D-ссылкой

Но мы попробуем по-другому. Но об этом в следующей статье.

Теперь вы сможете:

  1. Перемещать и копировать рабочие листы между файлами
  2. Связывать данные из разных рабочих книг

Оставьте первый комментарий

Комментировать