Между листами можно установить необходимые
Урок № 7.
РАБОЧАЯ КНИГА EXCEL. СВЯЗЬ ТАБЛИЦ,
До сих пор вы работали только с одним листом рабочей книги. Часто бывает полезно использовать несколько рабочих листов.
В нижней части экрана видны ярлычки листов. Если щелкнуть на ярлычке левой клавишей мыши, то указанный лист становится активным и перемещается наверх. Щелчок правой кнопкой на ярлычке вызовет контекстно-зависимое меню для таких действий с листом, как перемещение, удаление, переименование и т.д.
В левом нижнем углу окна рабочей книги находятся кнопки прокрутки ярлычков, с помощью которых можно переходить от одного рабочего листа к другому.
Щелкнув правой кнопкой мыши на кнопках прокрутки ярлычков, можно открыть контекстно-зависимое меню для выбора нужного рабочего листа.
Постановка задачи.
Необходимо создать классный журнал. Для простоты ограничимся тремя предметами: литература, алгебра, геометрия. Отельный лист рабочей книги отводится для каждого предмета. Он содержит список класса (ограничимся пятью учениками), текущие оценки и итоговую оценку за 1 четверть. На отдельном листе должна быть представлена ведомость итоговых оценок за 1 четверть, которая должна быть заполнена оценками с использованием ссылок на соответствующие листы по предметам.
ХОД РАБОТЫ
ЗАДАНИЕ 1. На заполните и оформите таблицу согласно рисунку:
Для чисел в ячейках, содержащих даты проведения занятий, задайте формат Дата (Код: Д.ММ).
Оценки за 1 четверть вычислите по формуле как среднее арифметическое текущих оценок, используя функцию СРЗНАЧ.
ЗАДАНИЕ 2. Сохраните таблицу в личном каталоге рабочего диска под именем jurnal.xks.
ЗАДАНИЕ 3. Создайте аналогичные листы для предметов алгебра и геометрия, для чего:
3.1.Скопируйте таблицу Литература на следующий лист, используя команды меню: Правка – Переместить/скопировать
Для этого дважды щелкните на ярлычке соответствующего листа и задайте в диалоговом окне новое имя. Можно один раз щелкнуть по ярлычку правой кнопкой мыши и открыть контекстно-зависимое меню, в котором выбрать пункт Переименовать.
ЗАДАНИЕ 5. На листах и в таблицах соответственно измените названия предметов, текущие оценки, даты.
Связь рабочих листов
Excel позволяет использовать в таблице данные с других листов и из других таблиц. Связь между двумя листами достигается за счет введения в один лист формулы связи со ссылкой на ячейку на другом листе.
ЗАДАНИЕ 6. На создайте таблицу – Ведомость итоговых оценок за 1 четверть, для чего:
6.1. Переименуйте в Лист
6.2.Заполните таблицу ссылками на соответствующие ячейки других листов:
В ведомости заполнятся колонки № и Фамилия учащегося.
Экран после выполнения указанных действий примет вид, представленный на рисунке.
Активным всегда является только одно из окон. Для активизации другого окна нужно щелкнуть по нему.
Связь между файлами
ЗАДАНИЕ 10. Осуществите связь между листами разных рабочих книг. Заполните столбец в Ведомости оценками по литературе, взяв их из файла jurnal.xls.
10. 1. Очистите от оценок столбец Литература, используя команды меню: Правка – Очистить содержимое.
10.2.В ячейку СЗ занесите формулу: =’A:\PETROV\[jurnal1.хls]Литература ‘!L3,
ЗАДАНИЕ 11. Самостоятельно заполните ведомость 1 четверть по предметам алгебра и геометрия.
ЗАДАНИЕ 15. Сохраните таблицу на диске в личном каталоге под именем work7.xls.
ЗАДАНИЕ 17. Подведите итоги.
Проверьте:
знаете ли вы, что такое:
умеете ли вы:
Если нет, то еще раз внимательно перечитайте соответствующие разделы работы.
Операции над рабочими листами в MS Excel. Практическая работа
Операции над рабочими листами
Цель занятия: изучить основные операции над рабочими листами MS Exсel, научиться использовать связи между листами.
Задачи занятия: приобрести практическое умение использования связи рабочих листов.
Порядок выполнения работы:
Выполните задания, следуя ходу их работы и используя нижеприведенные теоретические сведения.
При щелчке по листу панели инструментов «Стандартная» открывается новая рабочая книга. Она состоит из рабочих листов, имена которых Лист1, Лист2. Эти имена выведены на ярлыках в нижней левой части окна рабочей книги.
Перемещение между листами
Щелкая по ярлыкам, можно переходить от листа к листу внутри рабочей книги.
Переход к первому листу
Контекстное меню появляется при щелчке по ярлыку листа правой кнопкой мыши.
Копирование рабочего листа
Открыть контекстное меню листа, который копируем;
Выбрать пункт Переместить/скопировать;
В окне «перед листом» выбрать лист, перед которым хотите сделать копию;
Щелкнуть в окне Создавать копию;
Совместное использование нескольких рабочих листов
(связь рабочих листов)
Формулы могут ссылаться на ячейки, которые находятся в других рабочих листах. Формат такой ссылки:
При вводе формулы со ссылкой надо:
Нажать клавишу = в требуемой ячейке листа, в которую вставляется формула;
Щелкнуть на ярлыке листа, в котором берется адрес ячейки;
Щелкнуть на требуемой ячейке в этом листе;
На отдельных листах рабочей книги требуется набрать данные о приходе, расходе товаров и получить данные об остатке. Вначале создается шапка листа, с нее делаются две копии листа. На лист, содержащий приход товаров, вносятся данные по наименованию, цене и количеству товара. Через связь рабочих листов данные по наименованию и цене переносятся на листы с расходом и остатками товаров. Количество остатка также вычисляется через связь рабочих листов.
1. Используя контекстное меню для ярлыка Лист 1,переименуйте этот лист, дав ему имя Приход.
2. Наберите на этом листе шапку для данных о приходе товаров:
3. Создайте копию листа Приход после него (перед листом 2).
4. Переименуйте копию, дав имя Расход.
5. Создайте копию листа Расход после него.
6. Переименуйте копию, дав имя Остатки.
7. Введите на лист Приход наименования и цены товаров:
Клей по цене 30 руб. 50 коп. Кнопки по цене 20 руб. 75 коп. Скрепки по цене 10 руб. 90 коп.
8. Используя связь рабочих листов, перенесите для первого товара введенные данные
с листа Приход на листы Расход и Остатки
9. Для переноса данных остальных товаров используйте маркер заполнения
10. Введите любые числа в графу Количество на листы Приход и Расход
11. Используя связь рабочих листов, найдите величину остатка товаров на листе Остатки
12. Подсчитайте стоимость каждого вида товаров на листе Остатки
Аптека имеет 3 филиала. Требуется создать сводную ведомость, отражающую итоги недельных продаж лекарств для этих филиалов.
1. На Листе 1 создайте шаблон следующей таблицы:
Продажа лекарств филиалами аптеке № 7
2. Сделайте 2 копии данного листа.
3. Переименуйте эти листы, задав им имена «Филиал 1», «Филиал 2», «Филиал 3».
4. Внесите исходные данные в эти листы (выборочно).
5. Найдите итоговые суммы за неделю.
6. На новом листе создайте сводную ведомость по филиалам, отображающую только их итоги по каждому виду товара. При создании ведомости использовать связь рабочих листов.
Рассчитать квартальную потребность в кормах для каждого вида корма (вес каждого корма и стоимость). Расчет произвести на отдельном рабочем листе с именем Квартал.
1. Шаблон таблицы разработать только для листа Январь по нижеприведенному образцу. Отформатировать данные самостоятельно.
2. Шаблоны таблиц для листов Февраль, Март, Квартал – это копии листа Январь. Откорректировать их самостоятельно.
3. Вид корма и цену брать из листа Январь.
4. Исходные данные на эти листы (а также расчеты по ним) внести самостоятельно.
5. Исходные данные для квартального расчета брать из рабочих листов с именами Январь, Февраль, Март.
Потребность в кормах
В отделе кадров предприятия создается таблица со сведениями о работниках, в отделе учета рабочего времени – таблица с табелем, в бухгалтерии производится расчет зарплаты и оплаты за обучение, используя данные этих таблиц.
На рабочем листе создать таблицу со сведениями о работниках:
Переименовать данный лист в Отдел кадров.
На Листе 2 создать таблицу с табелем рабочего времени, пользуясь связью с листом Отдел кадров.
Переименовать данный лист в Учет времени.
Используя связь рабочих листов, на Листе 3 произвести расчет зарплаты сотрудникам, считая, то зарплата рассчитывается как ставка * количество отработанных дней.
В отдельной колонке произвести расчет оплаты за обучение, считая, что плата вычисляется как 2% от минимального оклада (считать, что он равен 4000 рублей) + 3% от зарплаты. Величины процентов и минимального оклада занести в отдельные ячейки и при расчетах использовать абсолютную адресацию.
Курс повышения квалификации
Дистанционное обучение как современный формат преподавания
Курс профессиональной переподготовки
Математика и информатика: теория и методика преподавания в образовательной организации
Курс профессиональной переподготовки
Методическая работа в онлайн-образовании
Ищем педагогов в команду «Инфоурок»
Разработка предназначена для учащихся при изучении темы «Рабочие листы MS Excel». Эта практическая работа дает возможность ознакомиться с основными операциями над рабочими листами, изучить алгоритм действия межлистовых ссылок, получить практический навык использования связи рабочих листов и закрепить этот навык, используя разнообразные задания. В процессе выполнения заданий учащиеся также повторяют основные виды форматирования ячеек, вспоминают о необходимости преобразования исходных данных в требуемый для расчетов формат, а также абсолютную и относительную адресацию ячеек.
Номер материала: 467526
Международная дистанционная олимпиада Осень 2021
Не нашли то что искали?
Оставьте свой комментарий
Авторизуйтесь, чтобы задавать вопросы.
Безлимитный доступ к занятиям с онлайн-репетиторами
Выгоднее, чем оплачивать каждое занятие отдельно
Рособрнадзор откажется от ОС Windows при проведении ЕГЭ до конца 2024 года
Время чтения: 1 минута
Минпросвещения будет стремиться к унификации школьных учебников в России
Время чтения: 1 минута
В российских школах оборудуют кабинеты для сообщества «Большой перемены»
Время чтения: 1 минута
Попова предложила изменить школьную программу по биологии
Время чтения: 1 минута
В России выбрали топ-10 вузов по работе со СМИ и контентом
Время чтения: 3 минуты
Минпросвещения разрабатывает образовательный минимум для подготовки педагогов
Время чтения: 2 минуты
Подарочные сертификаты
Ответственность за разрешение любых спорных моментов, касающихся самих материалов и их содержания, берут на себя пользователи, разместившие материал на сайте. Однако администрация сайта готова оказать всяческую поддержку в решении любых вопросов, связанных с работой и содержанием сайта. Если Вы заметили, что на данном сайте незаконно используются материалы, сообщите об этом администрации сайта через форму обратной связи.
Все материалы, размещенные на сайте, созданы авторами сайта либо размещены пользователями сайта и представлены на сайте исключительно для ознакомления. Авторские права на материалы принадлежат их законным авторам. Частичное или полное копирование материалов сайта без письменного разрешения администрации сайта запрещено! Мнение администрации может не совпадать с точкой зрения авторов.
Excel At Excel вып.3: Собираем данные с разных листов
Дано: 22 таблицы унифицированного формата с перечнем помещений от 22 дочерних предприятий.
Задача: сделать сводную таблицу с данными всех 22 таблиц
Что мы имеем? 22 одинаковых по формату таблицы на отдельных листах и с разным количеством строк в каждой. Соответственно, для построения общей таблицы нам надо решить следующие «проблемы»:
1) как заставить общую таблицу «переключаться» с одного листа данных на другой?;
2) как заставить ее делать это в нужный момент (когда закончились строки на одном листе)?
Начнем со второго вопроса, т.к. ответ на него мы уже знаем. Это циклы, о которых мы подробно говорили в выпуске 2 «Циклы в Excel без VBA». Для решения проблемы достаточно в наш файл добавить лист с перечнем всех обществ, соответствующими названиями листов и количеством подсчетом количества строк на каждом таком листе.
Обратите внимание на формулу в столбце Кол-во записей. Мы могли бы использовать просто COUNTA (СЧЁТА) со ссылкой на каждый лист. Но это потребует времени для «линковки» каждого листа. Плюс мы с вами готовим универсальные решения, которые будут работать в независимости от количества листов.
Именно для этого в функцию COUNTA (СЧЁТА) и внедряется эта чуднАя функция INDIRECT (ДВССЫЛ). Остановимся на ней подробнее.
Соответственно, зная несложные правила работы с текстом в Excel, мы можем легко сделать текст внутри изменяемым, а значит можем сделать и изменяемыми ссылки, которые будет возвращать функция INDIRECT (ДВССЫЛ).
Разберем пример выше. Функция INDIRECT (ДВССЫЛ) имеет следующий вид:
Чтобы понять, что означает текст внутри нее, давайте вспомним, как выглядит в Excel ссылка на ячейку на другом листе. Вот так:
Соответственно, в нашем случае у нас неизменен столбец B:B, а вот название листа меняется. Для «автоматизации» формулы заменяем Название листа на ссылку на название листа с данными по соответствующему обществу (столбец Название листа). Получаем следующее:
ВАЖНО! Обратите внимание на кавычки и конкатенацию («склеивание») при помощи амперсента. Не забудьте, что текст вносится в кавычках и соединяется со ссылками при помощи символа &.
Получается, что INDIRECT (ДВССЫЛ) получает название листа из ячеек в столбце А:А (Название листа), а диапазон у нас прописан фиксированно текстом (В:В). COUNTA (СЧЁТА) же просто считает количество строк в столбце В:В на соответствующем листа за вычетом заголовка.
=IF(A2=»»;»»;IF(COUNTIF($A$2:A2;A2)=OFFSET(‘Список обществ’!$E$2;A2-1;0);IF(A2+1>COUNTA(‘Список обществ’!A:A)-1;»»;A2+1);A2))
Затем пишем формулу второго цикла:
Далее при помощи уже знакомой нам функции OFFSET (СМЕЩ) заполняем столбцы из таблицы на листе Список обществ.
Осталось подтянуть данные с 22 листов по обществам. Тут нам опять поможет OFFSET (СМЕЩ), но уже в паре с INDIRECT (ДВССЫЛ).
ВАЖНО! Обратите внимание, что OFFSET (СМЕЩ) вкладывается внутрь INDIRECT (ДВССЫЛ), а не наоборот.
Основы работы с электронными таблицами
Назначение электронных таблиц
Электронные таблицы предназначены для обработки числовой информации. С помощью электронных таблиц можно решать целый спектр задач из различных предметных областей, выполнять различные экономические, бухгалтерские и инженерные расчеты, а также строить диаграммы, проводить сложный экономический анализ, моделировать и оптимизировать решение различных хозяйственных ситуаций и т.д.
Microsoft Excel — табличный процессор, который позволяет создавать электронные таблицы и автоматизировать обработку данных.
Табличный процессор обеспечивает:
ввод, хранение и корректировку данных;
автоматическое проведение вычислений по заданным формулам;
создание итоговых и сводных таблиц;
построение диаграмм и графиков на основе табличных данных;
оформление и печать электронных таблиц;
работу с электронными таблицами, как с базами данных: сортировку таблиц, выборку данных по запросам и т.д.
Запуск программы MS Excel
Щелкните кнопку Пуск, в меню Программы выберите Microsoft Excel.
И
нтерфейс 1
Лист – электронная таблица, состоящая из столбцов и строк. Пересечение строк и столбцов формирует ячейку.
Имя листа записывается на ярлычке.
Пример: Лист1, Смета.
Имя составляется из буквы (букв) латинского алфавита (A, B, C, D, …, Z, AA, …, AZ, …, IA, …, IV), соответствующей столбцу, и числа в диапазоне от 1 до 65536, являющегося номером строки.
Пример: A8, В10, IV3476.
Пример: R1C1, R4563C234.
Текущей (активной) называется ячейка электронной таблицы, в которой в данный момент находится курсор. Активная ячейка обычно выделяется жирной рамкой. Адрес текущей ячейки выводится в поле имени (слева от строки формул), содержимое ячейки – в строке формул.
Часть электронной таблицы, которую мы видим на экране монитора, называется текущим (активным) экраном.
Окно, с которым в настоящий момент работает пользователь, называется текущим (активным).
Создание нового документа
В меню Файл выберите команду Создать.
В диалоговом окне укажите Книга.
Щелкните по кнопке ОК.
Щелкните по кнопке Создать на Стандартной панели инструментов.
В меню Файл выберите команду Открыть (ИЛИ щелкните по кнопке Открыть на Стандартной панели).
В диалоговом окне Открытие документа укажите, с какого диска, из какой папки (могут быть вложения папок) и какой документ открыть.
Щелкните по командной кнопке Открыть (ИЛИ выполните двойной щелчок мыши по ярлыку документа).
Откройте документ из программы Проводник.
Примечание: Можно открыть сразу несколько документов. Для этого выделите все необходимые документы и щелкните по командной кнопке Открыть. Чтобы выделить:
Переключение между несколькими документами внутри программы осуществляется через меню Окно либо через панель задач.
Для многократного сохранения файла под тем же именем, в той же папке
В меню Файл выберите команду Сохранить (ИЛИ щелкните по кнопке Сохранить на Стандартной панели инструментов).
Для сохранения файла впервые, либо когда файлу Вы хотите дать другое имя, либо сохранить в другую папку
В меню Файл выберите команду Сохранить как…
В
диалоговом окне Сохранение документа укажите, на какой диск, в какую папку (могут быть вложения папок) и под каким именем Вы хотите сохранить данный документ (введите с клавиатуры в соответствующее текстовое поле).
Щелкните по кнопке Сохранить (в диалоговом окне).
В меню Файл выберите команду Закрыть.
Щелкните левой кнопкой мыши по кнопке с крестиком в правом углу в строке заголовка.
В активную (выделенную) ячейку электронной таблицы можно ввести текст, числовое значение или формулу.
Символьные (текстовые) данные — это любая последовательность символов, состоящая из букв, цифр и специальных символов
Пример: Сведения по аттестации учреждения, 5 класс, 123А-Р.
Виды функций: математические, финансовые, статистические, логические, текстовые и т.д.
=A1+B2 вычисляет сумму значений ячеек A1 и B2.
=C10/D5 + F6 к частному ячеек C10 и D5 прибавляется значение ячейки F6.
=СУММ(B2:F23) вычисляет сумму чисел в диапазоне B2:F23.
Кроме этого, каждая ячейка имеет сложную структуру, содержащую пять уровней (слоев). Каждый из них предназначен для хранения своего вида информации:
вашего текста, результатов вычислений, простых числовых значений,
формул и математических выражений,
вашего комментария к содержимому ячейки (примечания).
Работа с листами в Microsoft Excel
Каждый пользователь, который регулярно работает в редакторе таблиц Microsoft Excel, стремится сделать свою работу в программе более комфортной, удобной и эффективной. Разработчики Microsoft реализовали множество функций и настроек, позволяющих пользователю решать поставленные задачи максимально продуктивно. Давайте разберёмся, как работать в Excel с листами, что это такое и для чего они нужны.
Пользователи могут упорядочить данные в пределах одной книги
Листы необходимы для того, чтобы пользователю было удобнее оперировать данными в пределах одной книги. Такой подход позволяет избежать создания множества отдельных документов, кроме того, это даёт более широкие возможности для работы с разными таблицами и данными. Если у вас информация содержится в отдельных файлах, взаимодействие с ней будет затруднительным.
Чтобы легче было ориентироваться, вы можете переименовать листы, подписав их по своему усмотрению. Делается это очень просто. Щёлкните правой клавишей мыши по ярлыку текущей страницы и нажмите на пункт «Переименовать». После этого текущее название выделится чёрным, и вы сможете ввести туда свой вариант названия.
Если трёх имеющихся по умолчанию страниц вам недостаточно для работы, вы можете добавить ещё столько, сколько нужно. Внизу, рядом с вкладками каждой страницы, есть специальная пиктограмма. Щёлкните по ней либо нажмите комбинацию клавиш Shift+F11. Вы также можете установить другое начальное число листов в каждой создаваемой книге Excel. По умолчанию их три, но перейдя в окно параметров, вы сможете установить любое другое количество.
Удаление выполняется в пару кликов. Нажмите правой кнопкой мыши на вкладке страницы, в появившемся меню выберите пункт «Удалить». Копирование листа выполняется в том же меню, просто выберите пункт «Переместить или скопировать». В открывшемся окне поставьте галочку «Создать копию» и укажите книгу, в которую будет перемещена страница, либо выберите расположение относительно остальных листов. Перемещать страницу можно простым перетаскиванием вкладки внизу.
Чтобы пользователю было проще ориентироваться в книге Excel, разработчиками была реализована функция выделения ярлыка страницы цветом. Щёлкнув по вкладке правой кнопкой мыши, выберите в меню «Цвет ярлычка». Во всплывшем окне укажите желаемый цвет.
С листами можно работать не только отдельно, но и группируя их между собой. То есть изменения, касающиеся одного, будут применены и ко всем остальным страницам группы. Для этого отметьте один лист и нажмите Ctrl. Держа клавишу зажатой, продолжайте отмечать каждый ярлык. Закончив выделение, отпустите клавишу Ctrl. Для отмены нажмите правой кнопкой мыши и в появившемся списке выберите «Разгруппировать».
В случае, если необходимо закрепить столбцы и строки, выделите строчку, расположенную под теми, которые вы бы хотели закрепить. Далее перейдите на вкладку «Вид» и нажмите кнопку «Закрепить области», в выплывающем списке выберите одноимённый пункт. После этого вы обнаружите, что на странице появилась линия, отделяющая строки. Закрепление столбцов выполняется точно так же. Только выделять нужно следующую колонку справа от тех, что вы хотите закрепить. Для отмены перейдите на вкладку «Вид», нажмите «Закрепить области» и в открывшемся меню выберите пункт «Снять закрепление областей».
Как видите, можно значительно повысить эффективность работы в программе. Научившись правильно работать с листами в Microsoft Excel, решать определённые задачи станет гораздо проще, кроме того, вы сэкономите немало времени. Пишите в комментариях, помогла ли вам статья освоить операции с листами в Экселе и спрашивайте всё, что вас интересует по рассмотренной теме.
