При создании новой рабочей книги нам часто приходится обращаться к уже существующим таблицам. Например, начальство затребовало от нас отчет по всем проектам за прошедшее полугодие. И вот мы открываем несколько таблиц и собираем нужные для отчета данные в одну таблицу. То есть у нас будет новая рабочая книга.
По окончании этого урока вы сможете:
- Перемещать и копировать рабочие листы между файлами
- Связывать данные из разных рабочих книг
1. Перемещение и копирование рабочих листов между файлами
В статье «Excel 7. Рабочие листы» я рассказала, как можно работать с листами в пределах одной книги. Но листами можно обмениваться и между другими книгами. В той статье я предложила вам работать с командой «Перемесить или скопировать…» из контекстного меню имени (ярлычка) листа. Как и практически все команды в прикладных программах Microsoft Office эта команда продублирована в другом месте.
Скачайте файл тут и откройте его. Заодно создайте новый файл, который назовите «Отчет», или «Новая рабочая книга», или как-нибудь на свое усмотрение.
Шаг 1. Лента Главная → группа команд Ячейки → команда Формат → команда «Перемесить или скопировать…» из раскрывающего меню:
Появится знакомое уже окно:
В раскрывающемся списке есть только наш рабочий файл и Новая книга. Как видите, нам нет необходимости создавать новую таблицу. Мы просто перенесем нужные листы в новую книгу, а потом не забудем сохранить её. Для того, чтобы работать с уже имеющейся книгой, откроем уже созданный файл под названием «Отчет», а потом повторим Шаг 1.
Шаг 2. В раскрывающемся списке появилась рабочая книга «Отчет», которую мы выбираем. Обязательно поставим галочку «Сделать копию»:
| Понять и запомнить! | Если мы забудем поставить галочку, то лист в исходной книге исчезнет, что очень нехорошо. То есть мы осуществим операцию «вырезать → вставить». Будьте внимательны! |
Смотрим на картинку:
Обратили внимание, что тип шрифта слова «Курсы» изменился? И цвет шрифта изменился. Что подводит нас мысли, что хорошо бы иметь готовый шаблон, в котором бы в обязательном порядке работали бы все сотрудники. Но об этом позже.
Эта операция сэкономит нам время в том случае, если нам нужно собрать копии листов из нескольких книг в одной книге. Но часто нам требуется объединить на одном листе данные некоторых ячеек. В первую очередь мы обращаемся к буферу обмена.
Вырезая и копируя данные из одной рабочей книги в буфер и вставляя их затем в другую книгу, вы можете легко собрать необходимую информацию в одном месте.
Здесь есть, существенный недостаток – при изменении значения в исходных ячейках, скопированные в другие ячейки значения останутся прежними.
| Понять и запомнить! | Копирование и вставка из буфера не обеспечивает связи между исходной ячейкой и той, в которую скопированы данные. |
Чтобы данные в новых ячейках изменились так же, как и данные исходных ячеек, можно создать связь между двумя ячейками.
2. Связь с данными из других рабочих книг или 3D-ссылки
Работаем с двумя книгами. В моем случае это «Е47_Учебный файл – копия» и «Е47_Отчет».
Получаем задание от начальства: показать, какую прибыль мы получили по каждому курсу и итоговую сумму. То есть нам от книги исходника нужны два диапазона:
- Наименование или список курсов
- Стоимость
Рассмотрим исходник.
Стоимость прибыли с проведения конкретного курса определяется по формуле:
| (3)=(1)×(2) | D4=C4*$D$2 |
Обращаем внимание, что в формуле будет меняться адрес только первого сомножителя, а вот второй сомножитель будет постоянный или абсолютный (статья «Excel 10. Диапазон и вычисления в нем»).
А теперь активируем книгу «Отчет» и создаем там новый лист (можете создать новую книгу).
Шаг 1. В книге «Отчет» на листе 1 выбираем ячейку A2 и вводим знак «=»:
Шаг 2. Отмечаем в исходной книге ячейку B4 (первое имя курса в списке) и нажимаем Enter:
Шаг 3. А поскольку у нас 4 курса, то в книге Отчет» протягиваем курсором заполнения ячеек на 4 строчки вниз:
И видим неприятную картину. Все дело в том, что по умолчанию ссылка на ячейку в другой книге или на другом листе вставляется с абсолютным адресом:
| ='[Е47_Учебный файл – копия.xlsx]Курсы выездные’!$B$4 |
Эта ссылка несет информацию о рабочей книге, рабочем листе и ячейке, по которой мы щелкнули.
- Первый элемент ссылки, имя рабочей книги, заключен в квадратные скобки
- Конец второго элемента обозначен восклицательным знаком
- Третий элемент, ссылка на ячейку, содержит значки доллара перед номерами строки и столбца.
| Понять и запомнить! | Такая ссылка называется 3D-ссылкой. Она отражает три измерения (рабочая книга, рабочий лист и ячейка), которые нужны нам, чтобы указать на определенную ячейку в другой рабочей книге. |
Поэтому ссылка всегда будет на одну и ту же ячейку. Что делать?
Давайте пройдем шаги 1 и 2 заново, но шаг 2 сделаем таким образом:
Шаг 2. Выделяем весь диапазон имен курсов B4:B7:
И нажимает Enter:
Вот теперь замечательно!
Конечно, надо задать перенесение по словам (статья «Excel 22. Объединение ячеек») поправить внешний вид, отрегулировать высоту и ширину ячеек (статья «Excel 23. Размер ячеек»). Заодно определить диапазон стоимости, который мы перенесем из книги в книгу.
Чтобы сделать ссылку на весь диапазон «Стоимость», надо проделать предыдущие шаги:
- Выделить ячейку в книге «Отчет» и ввести знак «=»
- Выделить диапазон D4:D7
- Нажать Enter:
Но мы попробуем по-другому. Но об этом в следующей статье.
Теперь вы сможете:
- Перемещать и копировать рабочие листы между файлами
- Связывать данные из разных рабочих книг
Комментировать
Для отправки комментария вам необходимо авторизоваться.