Связывание рабочих листов
Если на одном рабочем листе используются данные из другого листа, то эти листы считаются связанными. С помощью связывания можно свести воедино значения клеток из нескольких разных таблиц на одном рабочем листе.
Изменение содержимого клетки на одном листе (листе-источнике) рабочей книги приводит к изменению связанных с ней клеток в листах-приемниках. Этот принцип отличает связывание листов от простого копирования содержимого клеток из одного листа в другой. В зависимости от техники исполнения связывание бывает “прямым“ и через команду СПЕЦИАЛЬНАЯ ВСТАВКА.
Прямое связываниелистов используется при вводе формулы в клетку одного листа, в которой в качестве одного из операндов используется ссылка на клетку другого листа. Если в клетке таблицы (например, в рабочем Листе2) содержится формула, в которой используется ссылка на клетку другого рабочего листа (например, на клетку А1 рабочего Листа1) и оба листа загружены, то такое связывание указанных листов называется “прямым”. Термин “прямое” связывание обозначает, что пользователь сам непосредственно при вводе формулы указывает имя листа и абсолютный адрес клетки из него, разделенные знаком «!».
= C5*Лист1! A1
= Лист3! В2*100
= Лист1! A1- Лист2! A1
Для указания ссылки на листы, находящиеся в незагруженных рабочих книгах, в формуле нужно без пробелов задать полный путь местонахождения файла. Путь задается в одинарных кавычках, где указывается название диска, каталога, имя рабочей книги в квадратных скобках и имя листа, на который идет ссылка.
Например = ‘D:\ EXCEL\[ КНИГА1.XLS] Лист1’!A1*С5
Связывание через команду СПЕЦИАЛЬНАЯ ВСТАВКА производится, если какая либо клетка таблицы на одном рабочем листе должна содержать значение клетки из другого рабочего листа.
Чтобы внести в Лист2 значение клетки A1 из исходного Листа1, нужно выделить эту клетку и выбрать команду ПРАВКА-КОПИРОВАТЬ. На втором листе поставить курсор на ту клетку, куда нужно копировать, и выполнить команду ПРАВКА-СПЕЦИАЛЬНАЯ ВСТАВКА — ВСТАВИТЬ СВЯЗЬ. На втором листе появится указание на клетку исходного первого листа, например:
= Лист1!$A$1 .
При таком связывании EXCEL автоматически переводит относительный адрес в абсолютный, т.к. если адрес обращения не абсолютный, это может привести к ошибкам, особенно если обращаться к незагруженным файлам.
Источник
Связывание таблиц, листов.
Excel позволяет динамически связать одну электронную таблицу с другой так, что при внесении изменений в одну из таблиц эти изменения автоматически будут переноситься и во вторую. Можно выполнять
1) связывание рабочих листов, когда вся информация с одного листа при его заполнении автоматически переносится на второй лист. Для связывания листов необходимо нажать клавишу Ctrl и, не отпуская ее, нажать название другого листа. При этом оба листа становятся активными.
2) связывание ячеек, находящихся в различных таблицах. Для того, чтобы понять как выполняется связывание таблиц, выполним следующий пример. Пусть в одной таблице вводятся данные за первое полугодие (рис. 1), во второй таблице — данные за второе полугодие, а в третьей — должны находиться данные за первое и второе полугодия, и итог за год.
Для реализации такого примера необходимо выполнить следующие действия:
a) заменить названия листов Лист1 – I полугодие, Лист2 – II полугодие, Лист3 – За год(для этого встать мышкой на слово Лист1 и дважды щелкнуть левой кнопкой мыши);
b) на листе I полугодиесоздать таблицу с данными по месяцам – январь — июнь; на листе II полугодие— с данными по месяцам июль — декабрь, на листе За год таблицу с колонками – I полугодие, II полугодие, итого;
c) заполнить первую и вторую таблицы;
d) когда исходные таблицы сформированы, приступаем к созданию связей между таблицами, чтобы получить данные для третьей таблицы За год, для этого:
e) активизировать ячейку в третьей таблице в колонке I полугодие и набрать знак “=“;
f) выделить ячейку Итого в таблице I полугодие(Лист I полугодие), после чего координаты этой ячейки появятся в строке формул, если выражение сформировано верно, нажмите Enter;
g) аналогичные операции выполнить и для ячейки II полугодие для таблицы За год.
3) связывание групп ячеек с подсчетом результатов по определенным функциям – консолидация. При консолидации данных объединяются значения из нескольких диапазонов данных. Например, если имеется лист расходов для каждого регионального представительства, консолидацию можно использовать для преобразования этих данных в лист корпоративных расходов.
Консолидировать данные в Microsoft Excel можно несколькими способами. С помощью трехмерных ссылок (трехмерная ссылка — ссылка на диапазон, включающий более одного листа книги.) в формулах, по положению или по категории.
Консолидация с помощью формул проводится в следующем порядке.
- На листе консолидации скопируйте или задайте надписи для данных консолидации.
- Укажите ячейку, в которую следует поместить данные консолидации.
- Введите формулу, включающую ссылки на исходные ячейки каждого листа, содержащего данные, для которых будет выполняться консолидация.
Например, чтобы объединить данные в ячейке B3 листов с Лист2 по Лист7, введите =СУММ(Лист2:Лист7!B3). Если данные, которые требуется объединить, находятся в разных ячейках на разных листах, введите формулу в формате=СУММ(Лист3!B4; Лист4!A7; Лист5!C5). Чтобы ввести ссылку (например Лист3!B4), не используя клавиши на клавиатуре, введите формулу до того места, где требуется вставить ссылку, а затем укажите на листе нужную ячейку.
Для проведения консолидации другими способами необходимо выполнить следующие действия.
1. Настройте данные для консолидации.
§ Убедитесь, что все диапазоны данных представлены в формате списка: первая строка каждого столбца содержит подпись, остальные строки — однотипные данные, пустые строки или столбцы в списке отсутствуют.
§ Разместите каждый диапазон на отдельном листе. Не помещайте диапазоны на лист, на котором должна выполняться консолидация.
§ Если консолидация выполняется по положению, убедитесь, что макеты всех диапазонов совпадают.
Если консолидация выполняется по категории, убедитесь, что подписи столбцов или строк, которые требуется объединить, совпадают с учетом регистра букв.
§ Назначьте имя каждому диапазону: выделите диапазон, укажите в меню Вставка на пункт Имя, выберите команду Присвоить и введите имя для данного диапазона.
2. Щелкните левый верхний угол области, в которой требуется разместить консолидированные данные.
3. В меню Данные выберите команду Консолидация.
4. Выберите из раскрывающегося списка Функциявыберите тип функции, которую требуется использовать для консолидации данных.
5. Щелкните поле Ссылка, откройте лист, содержащий первый диапазон данных для консолидации, введите имя этого диапазона и нажмите кнопку Добавить. Повторите этот шаг для всех диапазонов.
6. Если таблицу консолидации требуется обновлять автоматически при каждом изменении данных в каком-либо исходном диапазоне, и позже точно не потребуется изменять или добавлять диапазоны исходных данных для консолидации, установите флажок Создавать связи с исходными данными.
7. Если консолидация выполняется по положению, оставьте все поля в группе Использовать в качестве имен пустыми. В Microsoft Excel подписи исходных строк и столбцов не копируются в консолидированные данные. Если требуется скопировать подписи в консолидированные данные, сделайте это вручную.
Если консолидация выполняется по категории, в группе Использовать в качестве имен установите флажки, соответствующие расположению подписей в исходных диапазонах: в верхней строке, в левом столбце или в верхней строке и в левом столбце одновременно. Все подписи, не совпадающие с подписями в других исходных областях, в консолидированных данных будут расположены в отдельных строках или столбцах.
Подготовка к печати.
С помощью Excel можно поменять внешний вид данных в электронной таблице, изменяя шрифт, размер, стиль и цвет информации, которая выводится в ячейки и текстовые поля.
Все действия по форматированию можно выполнить, пользуясь командами Шрифт и Число меню Формат. Кроме того, чаще всего использующиеся операции форматирования вынесены в качестве кнопок инструментов Стандартной панели инструментов и панели инструментов Форматирование.
Обрамление таблицы
Excel позволяет затенить некоторые ячейки или заключить их в рамку. Можно рисовать произвольные комбинации горизонтальных и вертикальных линий слева, справа, вверху или внизу ячейки, а также задавать двойное подчеркивание и пунктирное. Эти операции выполняются в режиме Ячейки меню Формат.
Для оформления таблиц также можно использовать автоформатирование (режим Автоформат), которое позволяет выбрать стандартный формат для вашей таблицы.
Для подготовки таблиц к печати необходимо выполнить следующие действия:
Определить параметры страницы (задать формат бумаги: А4; размеры полей: верхнее, нижнее — 3 см., слева, справа — 2 см.; расположение таблицы на листе: выравнивание по горизонтали).
Задать верхние и нижние колонтитулы (верхний колонтитул: “Лабораторная работа № 1. Задание №….Выполнил (ФИО)”, нижний колонтитул: “лист #”).
Сформировать общий заголовок таблицы и выровнять его по центру листа.
Просмотр готового листа
После того, как таблица подготовлена, целесообразно выполнить ее просмотр в том виде, в каком она будет выводиться на печать. Для этого выбрать режим Просмотр в меню Файл. В этом режиме можно изменить параметры страницы, полей, колонтитулов, центрирования, а также отменить вывод сетки на печать и др.
Контрольные вопросы.
1. Перечислите категории вводимых в ячейку данных.
2. Как производится фиксация введенных данных в ячейке?
3. Что называется адресом ячейки? Виды адресов.
4. Как производится копирование и вставка данных в ячейки?
5. Как в EXСEL производится проверка орфографии?
6. Каким образом произвести форматирование чисел в ячейках?
7. Что входит в понятие форматирование ячейки?
8. Как сделать разграфление и обрамление таблицы?
9. Как провести графический анализ данных?
10. Какие виды диаграмм можно использовать в EXСEL?
11. Иногда после создания диаграммы числовые значения требуется изменить. Как обновить такую диаграмму?
12. Назовите способы связывания и консолидации рабочих листов
13. Для чего предназначено связывание рабочих листов?
14. Как подготовить таблицы к печати?
ЗАДАНИЯ.
Часть 1
1. В личной папке создайте папку «Лабораторная работа 3».
2. Откройте рабочую книгу EXСEL. Составить на листе 1 таблицу статистической информации за первое полугодие. На листе 2 – таблицу статистической информации за второе полугодие.
3. В каждой таблице подсчитать по строчкам и столбцам сумму, а также определить с помощью Мастера функций максимальное, минимальное, среднее значение (по строкам, столбцам и за полугодие). По данным каждого месяца посчитать отклонение максимального и минимального значений от среднего значения.
Таблица № 1 . Сводка за первое полугодие.
| Месяц Наименование | Январь | … | Июнь | Итого | Среднее Значение по позиции | Максимальное значение | Минимальное значение |
| Итого за месяц | |||||||
| Среднее значение за месяц | |||||||
| Ввести значения используя функцию Если (Макс.– Ср.)>0 то напечатать «Превышение среднего», иначе печатать «Ниже среднего» |
4. Для каждой таблицы по месячным данным на соответствующем листе построить гистограммы типа №3 и типа №4.
5. На листе 3 построить таблицу статистической информации за год, используя связанные ячейки с итоговыми данными по полугодиям с ячейками первой и второй таблиц, а также трехмерную консолидацию для подсчетов итогов за год.
Таблица №3. Итоговые значения за год.
| Полугодие Наименование | 1-е полугодие | 2-е полугодие | Итого за год |
6. Создайте отчет по лабораторной работе в текстовом редакторе и поместите туда созданные таблицы и диаграммы.
Часть 2.
1. Постройте на новом листе диаграмму Ганта, а затем сравнительную диаграмму, используя изложенные ниже алгоритмы.
Источник
СВЯЗЫВАНИЕ РАБОЧИХ ЛИСТОВ
Связывание рабочих листов осуществляется специальными способами и с помощью формул, содержащих адресные ссылки не только из текущей, но и из других рабочих листов данной рабочей книги.
= Янв!012+Февр!012+Март!Б12 = СУMM(Янв:Март! D12).
C помощью первой формулы вычисляется произведение чисел, содержащихся в ячейке ВЗ текущего рабочего листа и ячейке СЗ листа «Лист2». Между именем рабочего листа и адресом ячейки ставится восклицательный знак.
Во второй и третьей формулах вычисляется сумма чисел, находящихся в ячейках D12 на рабочих листах «Янв», «Февр», «Март».
Пример. Составим таблицу, которая подсчитывала бы облагаемый доход за каждый месяц и накапливала бы его с начала года. Для простоты положим, что в облагаемый доход заносится вся начисленная сумма за минусом 1% в пенсионный фонд и необлагаемого минимума в размере одного минимального оклада на работника.
В рабочем листе «Итого» подсчитаем суммарные значения за квартал.
Решение. Переименуем рабочие листы «Листі», «Лист2», «ЛистЗ» и «Лист4» соответственно в «Янв», «Февр», «Март», «Итого» с помощью контекстно-зависимого меню.
Выделим рабочие листы «Янв», «Февр», «Март», «Итого» с помощью мыши и клавиши «Shift». При этом ярлычки выделенных рабочих листов станут белыми. Это свидетельствует о том, что мы включили групповой режим работы. Все, что мы будем набирать на рабочем листе «Янв», автоматически будет набираться на всех выделенных рабочих листах. В целях экономии времени имеет смысл в групповом режиме создать таблицу «Ведомость» с общими для всех выделенных рабочих листов элементами. Это прежде ^ всего заголовок, сведения о минимальной зарплате, список работников, шапка таблицы, формулы подсчета итоговых сумм по столбцам, а для первых трех таблиц и формулы для расчета отчислений в пенсионный фонд и текущего облагаемого дохода.
В колонку «Пенсионный фонд» (ячейка D4) занесем формулу =С4*0,01 и размножим ее в ячейки D5:D6 в таблице, представленной на рис.
Рис. 3.10.4. Рабочий лист с формулами за январь
В ячейку Е4 («Текущий облагаемый доход») внесем формулу =C4-D4-$D$2 и размножим ее в ячейки Е5:Е6.
В ячейках C7.G7 вычислим суммы. В ячейках A3:G6 установим сетку.
Отменим выделение группы. Для этого достаточно щелкнуть мышью по невыделенному листу или с помощью контекстно-зависимого меню «Разгруппировать листы». Обратите внимание, что таблица одновременно создавалась на всех выделенных рабочих листах.
Запишем формулу для облагаемого дохода с начала года. В январе она равна текущему облагаемому доходу, т.е. в клетке F4 запишем формулу =Е4, в клетке F5 — формулу =Е5 и т.д.
В результате получим данные об облагаемом доходе за январь (рис. 3.10.5).
В феврале облагаемый доход равен текущему облагаемому доходу плюс облагаемый доход за январь. Формула в феврале в клетке F4 будет иметь вид: =Е4+Янв!Е4. Скопируем данную формулу в диапазон F5:F6 (рис. 3.10.6). В результате получим данные об облагаемом доходе за февраль (рис. 3.10.7).
В марте аналогичная формула в клетке F4 будет иметь вид: =Е4+СУММ(Янв:Март!Е4) (рис. 3.10.8). Результаты вычислений за март представлены на рис. 3.10.9.
Рис. 3.10.5. Рабочий лист со значениями за январь
Рис. 3.10.6. Рабочий лист с формулами за февраль
Рис. 3.10.7. Рабочий лист со значениями за февраль
Рис. 3.10.8. Рабочий лист с формулами в марте (столбец D спрятан)
Рис. 3.10.9. Рабочий лист со значениями в марте
В рабочем листе «Итого» запишем формулы для подсчета итоговых сумм (рис. 3.10.10):
• по колонке «Начислено» — =СУММ(Янв:Март!С4);
• по колонке «Пенсионный фонд» — =СУММ(Янв:Март!Н4);
• по колонке «Облагаемый доход с начала года» — =СУММ(Янв: Март!Е4).
Рис. 3.10.10. Рабочий лист «Итого» с формулами (столбец В спрятан)
Размножим данные формулы.
Результаты вычислений в рабочем листе «Итого» представлены нарис. 3.10.11.
Связывание рабочих листов можно осуществлять тремя способами:
• с помощью формул, прямо осуществляя их написание;
• с помощью копирования и специальной вставки с использованием кнопки «Вставить ссылку»;
• с помощью консолидации рабочих листов.
Рис. 3.10.11. Рабочий лист «Итого» со значениями
Источник
