Поиск

Полнотекстовый поиск:
Где искать:
везде
только в названии
только в тексте
Выводить:
описание
слова в тексте
только заголовок

Рекомендуем ознакомиться

'Документ'
Проектная деятельность обучающихся — совместная учебно-познавательная, творческая или игровая деятельность учащихся, имеющая общую цель, согласованны...полностью>>
'Пояснительная записка'
Необходимы огромные творческие усилия, чтобы от абстрактного представления перейти к конкретному проекту. В данном случае это трехкомнатная квартира,...полностью>>
'Лекция'
Рассмотрены сущность финансовых рисков, их классификация. Приведены основные показатели, характеризующие финансовые риски с использованием конкретног...полностью>>
'Книга'
Организация связи синтетических и аналитических счетов. Автоматизированное рабочее место бухгалтера Организация справочников условно-постоянной инфор...полностью>>

Главная > Лабораторная работа

Сохрани ссылку в одной из сетей:

Лабораторные работы по электронной таблице Excel 97–2000–XP

Лабораторная работа № 1

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

Электронная таблица состоит из строк и столбцов. Строки нумеруются, а столбцы обозначаются латинскими буквами. На пересечении строк и столбцов находятся ячейки. Каждая ячейка имеет уникальный адрес, состоящий из обозначения столбца и номера строки. Например: А1, А5, В4 и т.д. В ячейке могут храниться: число, текст или формула. Именно благодаря тому, что в ячейке можно записывать формулы, электронные таблицы могут использоваться для выполнения расчетов. MS Excel по содержимому, которое пользователь вводит в ячейку, пытается определить, что в ней будет. Так, например, если первым символом вводится цифра, знак плюс или минус, то считается, что в ячейке будет записано число. Для отличия формулы от текста формулу записывают, начиная со знака равно (=). Например:

=1,25*(В2+С2-А2)/Е2^2

означает, что нужно сложить содержимое ячеек В2 и С2, вычесть содержимое ячейки А2. Полученный результат следует умножить на коэффициент 1,25 и разделить на содержимое ячейки Е2, возведенное к квадрат. В приведенном примере использованы все арифметические операции и круглые скобки, которые меняют последовательность выполнения операций. Обращаем внимание, что целая часть от дробной отделяется не точкой, как это принято в большинстве языков программирования, а запятой (задается в настройках Windows).

При выполнении первой и второй лабораторных работ не придавайте никакого значения внешнему виду таблицы. Все внимание концентрируйте на правильности вычислений. Форматирование таблицы будет выполняться в работе №3.

  1. Проверьте наличие на диске вашей папки и создайте ее, если она отсутствует. Для этой цели воспользуйтесь программой Проводник.

  2. Вызовите MS Excel и ознакомьтесь с меню и панелями инструментов, которые выведены на экран. Состав панелей инструментов у каждого пользователя может быть свой. Управления панелями инструментов осуществляется через п. меню Вид –Панели инструментов.

  3. Перемещайте курсор по панели инструментов и задерживайте его на некоторое время у каждого инструмента. Обратите внимание, что при этом появляется краткая подсказка о назначении инструмента.

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

Создание таблицы

  1. Начиная с клетки А1, создайте следующую электронную таблицу (рис. 1). Названия столбцов таблицы и фамилии сотрудников, числа и формулы вводите с первой позиции. Обратите внимание, что числа автоматически будут выровнены по правой границе, а текст - по левой. В колонке D запишите формулы. На рисунке показана формула для ячейки D2

Д
иапазон D3:D8 заполняются путем копирования формулы из ячейки D2. Для этого нужно выделить блок ячеек, начиная с ячейки D2 до D8 включительно (Shift и стрека вниз) и вызвать операцию Правка – Заполнить - Вниз) или с использованием мыши путем протаскивания маленького квадрата в правом нижнем углу ячейки D2. Просмотрите формулы для всех сотрудников. Обратите внимание на автоматическое изменение адресов.

  1. Клетка В9 рассчитывается по формуле =СУММ(В2:В8) Ее можно ввести самостоятельно, а можно выделить диапазон ячеек В2:В8 и нажать на инструмент со знаком суммирования . В клетки С9 и D9 эта формула копируется (выделение блока, а затем – п. Правка – Заполнить - Вправо или с использованием мыши путем протаскивания вправо квадрата в правом нижнем углу ячейки. Опробуйте оба способа копирования формулы).

Сохранение таблицы

  1. Сохраните таблицу на магнитном диске в вашей папке под именем Задача1.XLS. Для этого воспользуйтесь п. меню Файл – Сохранить.

  2. Поменяйте у нескольких сотрудников заработную плату. Для редактирования содержимого ячеек используйте клавишу F2 или дважды щелкните мышкой на соответствующей ячейке. Обратите внимание на автоматическое изменение вычисляемых ячеек.

  3. Сохраните таблицу под прежним именем (п. Файл – Сохранить или инструмент ).

  4. Внесите еще несколько изменений в таблицу и постарайтесь их запомнить.

  5. Закройте таблицу без записи ее на диск (п. Файл - Закрыть).

Открытие существующей таблицы

  1. Откройте созданную вами таблицу (п. Файл –Открыть или инструмент ). Обратите внимание, ваши изменения, сделанные при выполнении п.11, в таблице отсутствуют.

  2. Сдайте работу преподавателю и закройте таблицу.

Вопросы к лабораторной работе №1

Как вызывается помощь?

Как создать новую таблицу?

Как записать таблицу на диск?

Как записать таблицу на диск под другим именем?

Как открыть существующую таблицу?

Как закончить работу с таблицей, не записывая ее на диск?

Как адресуются ячейки таблицы?

Что такое диапазон ячеек?

Как выделяется диапазон ячеек при помощи клавиатуры?

Как выделяется диапазон ячеек мышью?

Как адресовать фрагмент строки?

Как адресовать фрагмент столбца?

Как осуществляется ввод чисел?

Чем отделяется дробная часть от целой?

Как осуществляется ввод строк символов (текста)?

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

Как осуществляется ввод формул? Каким символом начинается формула?

Какая функция используется для суммирования? Ее конструкция?

Как редактируется содержимое ячеек?

Как копируются ячейки? Что при этом происходит с адресами ячеек?

Как очищается ячейка или диапазон ячеек?

Как пересчитывается таблица?

Профессор кафедры Информационных технологий Музычкин П. А.

Доцент кафедры Информационных технологий Щёлоков А. Ф.

Лабораторная работа № 2

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

Цели: знакомство с абсолютной адресацией ячеек, добавление строк и столбцов, удаление строк, редактирование формул, сортировка табличных данных, функция ЕСЛИ.

  1. Откройте таблицу Задача1 и сразу сохраните ее под именем Задача2. Напоминаем, что в данной работе внешнему виду таблицы не придавайте значения, все внимание концентрируйте на построении формул и выполняемых операциях.

  2. В
    ставьте три строки перед строкой заголовка таблицы. Для этого установите курсор в ячейку А1 и используйте п. меню Вставка – Строки. В ячейку А1 введите текст «Расчетная ведомость за январь», в ячейку А2 – текст «СтавкаНалога», в ячейку А3 - «Удерж. в ПФ», в ячейку В2 число 12%, в ячейку С2 число 15%, в ячейку В3 –число 1%.

  3. Создайте новые столбцы «Налог» и «Выплатить». При этом для столбца «Налог» задайте формулу, в которой используйте значение ячейки В2, поставив знак доллара перед обозначением столбца и перед номером строки ($В$2), как это показано на рисунке. Это будет абсолютная ссылка на ячейку. При копировании формулы для всех сотрудников адрес этой ячейки останется без изменения.

Внимание! Количество и расположение столбцов после выполнения этой лабораторной работы показано на рисунке в лаб. работе № 4.

  1. Формулы для вычисления столбца F и итоговых сумм создайте самостоятельно, руководствуясь смыслом задачи.

  2. Удалите одну строку из таблицы (уволен сотрудник). Проверьте формулы итоговой строки, обратите внимание на изменение диапазонов в формулах.

  3. Дополните таблицу еще тремя строками, включив их между, например, 7 и 8 строками (приняты на работу три новых сотрудника). Заполните эти строки - фамилии, заработную плату и премию введите, а расчетные формулы скопируйте путем протаскивания. Проверьте итоговую строку и обратите внимание на изменение диапазона суммируемых ячеек в формулах - они соответствуют всем ячейкам, которые находятся выше итоговой строки, то есть программа сама откорректировала вашу формулу.

ВНИМАНИЕ, "тонкий" момент.

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

  2. Вставьте перед колонкой «Налог» еще две новые колонки «Удер. в ПФ» и «Налог. база». Установите, что в пенсионный фонд удерживается 1% от начисленной заработной платы и премии. При этом в формулах ссылайтесь на ячейку В3.

  3. Отчисления в пенсионный фонд не входят в налогооблагаемую базу. Внесите необходимые изменения во все формулы, зависящие от этого.

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

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

Если налогооблагаемая база меньше или равна 30 000 рублей, то принимается ставка 12%, в противном случае налог равен - 3600 плюс 15%.от суммы, которая превышает 30000 руб. Формула должна использовать функцию ЕСЛИ:

=ЕСЛИ(условие; формула1; формула2)

Расчет по формуле 1 выполняется в том случае, если условие истинно, в противном случае для расчета используется формула 2.

Так, если налогооблагаемая база первого сотрудника находится в ячейке G5, то формула будет выглядеть следующим образом:
=ЕСЛИ(G5<30000; G5*$B$2; 3600+$C$2*(G5-30000))

ВНИМАНИЕ! У вас могут быть другие координаты ячеек.

  1. Установите 1-2 сотрудникам заработную плату более 30000. Проверьте правильность расчета налога.

  2. Вставьте перед столбцом фамилий новый столбец "Таб.номер" и заполните (автоматически) его значениями: 100, 101,102 и т.д. Для этого, установите табельный номер первого сотрудника равным 100. Выделите диапазон, начиная с этой ячейки, до номера последнего сотрудника и воспользуйтесь п. Правка – Заполнение -Прогрессия.

  3. Арифметическую прогрессию можно создать и другим способом. Задайте первому сотруднику табельный номер 100, второму 110 и выделите эти две ячейки. Захватив точку в правом нижнем углу выделенного диапазона, протащите значения до последнего сотрудника - получится арифметическая прогрессия с шагом 10.

  4. Сохраните исправленную таблицу (п. Файл - Сохранить ).

Сортировка таблицы

  1. Отсортируйте таблицу в алфавитном порядке по фамилиям сотрудников. Для этого выделите фрагмент, начиная с табельного номера первого сотрудника до ячейки "Выплатить" последнего сотрудника и воспользуйтесь п. Данные- Сортировка. Для усвоения материала самостоятельно выполните сортировку по другим полям.

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

  3. Сохраните исправленную таблицу в вашей папке.

  4. Сохраните таблицу и сдайте работу преподавателю.

Вопросы к лабораторной работе № 2

Какие адреса называют относительными, а какие абсолютными?

Зачем нужны относительные и абсолютные адреса?

Как вставляются новые строки и колонки?

Как удаляются строки и столбцы таблицы?

Что происходит с формулами при вставке и удалении строк?

Каким образом следует вставлять новые строки в таблицу, если в таблице имеются итоговые строки?

Какая функция используется для записи условных выражений? Ее конструкция?

Как заполнить фрагмент таблицы значениями, возрастающими по арифметической прогрессии?

Как выполняется сортировка данных?

Сколько уровней сортировки поддерживается?

Какой порядок сортировки поддерживается?

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

Профессор кафедры Информационных технологий Музычкин П. А.

Доцент кафедры Информационных технологий Щёлоков А. Ф.

Лабораторная работа № 3

Цель: форматирование таблицы, защита таблицы от редактирования

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

Форматирование таблицы должно проводиться с учетом ее вывода, как на экран, так и на принтер. Поэтому в данную лабораторную работу включен пункт настройки параметров страницы бумаги.

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

  1. Откройте таблицу Задача2.XLS и сразу сохраните ее под новым именем Задача3

  2. Сделайте две строки, содержащие процент подоходного налога и отчисления в пенсионный фонд, невидимыми. Для этого выделите их и воспользуйтесь п. Формат – Строки – Скрыть.

Установка ширины столбцов и высоты строк

  1. Установите ширину столбцов с учетом возможных их значений и ширины заголовков. Для изменения ширины столбца установите курсор на серую границу с названиями колонок (А,B,C,D и т.д.), нажмите и не отпускайте левую кнопку мыши и перемещайте появившуюся вертикальную линию влево или вправо.

  2. Измените высоту итоговой строки (сделайте ее "выше") и высоту строки, составляющей "шапку" таблицы (сделайте ее "ниже").

  3. Автоматически настройте высоту строки и ширину столбца (по самому “высокому” символу и “длинному” значению). Используйте для этого пункт меню Формат-Строка (Столбец) - Автободбор высоты (ширины), предварительно выделив нужную область. Аналогичный результат можно получить дважды щелкнув мышкой на серой границе между столбцами (строками).

Выравнивание данных и управление разрядностью

При выравнивании данных обычно соблюдают следующие правила:

  • ячейки, содержащие текст, выравниваются по левой границе;

  • ячейки, содержащие числа, выравниваются по правой границе;

  • названия столбцов таблицы выравниваются по центру.

  1. Отцентрируйте названия столбцов. Для этого выделите диапазон ячеек и щелкните по инструменту . Проведите несколько экспериментов с расположением фамилий (по центру, по правой границе, по левой границе).

  2. Установите формат графы С - целое число, а всех остальных граф - дробные числа с двумя разрядами после запятой (для учебных целей). Для этого выделите фрагмент таблицы и воспользуйтесь п. меню Формат – Ячейки –Число. Если при этом ширина некоторых граф окажется недостаточной, измените ее.

  3. Установите курсор в ячейку, в которой записан текст «Расчетная ведомость за январь 1999г». Выделите диапазон, начиная с этой ячейки до последней графы таблицы. Отцентрируйте заголовок в пределах этого диапазона (инструмент ). Установите для заголовка жирный шрифт размером 12 пунктов.

  4. Выделите "шапку" таблицы. Установите для нее шрифт, размером 8 или 9 пунктов. Замените в заголовке сокращения на полные слова. При необходимости измените ширину некоторых граф, чтобы таблица "смотрелась".

  5. Для ячеек “Табельный номер“, “Всего начислено”, “Пенсионный фонд” задайте формат вывода текста в несколько строк (Формат - Ячейка - вкладка Выравнивание - Переносить по словам). Добейтесь переноса слов путем сужения соответствующих столбцов до размера одного слова.

  6. Сохраните таблицу.

Нанесение границ и изменение цвета

  1. Войдите в режим Сервис - Параметры - вкладка Вид и сбросьте флажок у элемента "Сетка" (вертикальные и горизонтальные линии должны исчезнуть). Это позволит более отчетливо увидеть результаты следующих действий.

  2. Используя инструмент «Границы» , расчертите таблицу горизонтальными и вертикальными линиями. Используйте жирные, тонкие линии и двойные линии. Перед нанесением границ фрагмент таблицы нужно выделить.

  3. Измените цвет символов итоговой строки и заголовка (инструмент ). Измените цвет фона «шапки» и графы «Выплатить», например, сделайте ее светло-серой (инструмент ).

  4. Придайте таблице вид, наилучший с вашей точки зрения. Посоветуйтесь по этому вопросу с другими студентами и преподавателем.

Условное форматирование

  1. Обеспечьте вывод суммы подоходного налога, рассчитанного по прогрессивной шкале другим цветом, например, синим. Для этого установите курсор на ячейку с Налогом первого сотрудника, выберите п. Формат – Условное форматирование и сформируйте условие, показанное на следующем рисунке. Проверьте координаты, у вас налоговая база, возможно, находится не в столбце G! Сравнивать нужно налоговую базу, а окрашивать в другой цвет – налог. Кроме того, в условии адрес не должен быть абсолютным (не должно быть знаков $).

З
атем нажмите кнопку Формат и задайте нужный цвет и начертание символов (вкладка Шрифт). Можно задать несколько условий(вкладка А также). Скопируйте путем протаскивания формулу для всех сотрудников в графе налог. Ячейки этой графы окрасятся в разные цвета в соответствии с установленным форматом. В условии для форматирования можно задавать значение или формулу. Установите для графы Выплатить несколько условий для форматирования, используя значение. Скопируйте только форматы (инструмент Кисть).

Измените нескольким сотрудникам заработную плату и проверьте действие условного форматирования.

  1. Сохраните таблицу в вашей папке.

Подготовка таблицы к выводу на печать

  1. Выведите вашу таблицу на экран в режиме предварительного просмотра (инструмент, на котором изображена лупа) и научитесь изменять масштаб (увеличивать и уменьшать таблицу при просмотре).

  2. Войдите в режим Файл - Параметры страницы и изучите их. Поменяйте некоторые параметры (ориентацию листа бумаги, отступы от границ листа) и повторите просмотр таблицы перед выводом на печать. Обратите внимание на то, как повлияли ваши изменения на расположение таблицы на листе бумаги.

  3. Сохраните таблицу на диске.

Защита таблицы от изменений

  1. Защитите таблицу от изменений (пароль задавать не надо). Для этого используйте п. меню Сервис-Защита - Защитить лист. Попробуйте изменить какие-либо ячейки таблицы, у вас это не должно получиться. Снимите защиту с таблицы.

  2. Защитите от редактирования все ячейки таблицы за исключением фамилий, размера заработной платы и премии. Предварительно установите другой, например, зеленый цвет для ячеек, которые разрешено редактировать. Защита выполняется в два этапа:

а) сброс флажка защиты с тех ячеек, которые можно редактировать. Для этого нужно выполнить следующие действия: выделить диапазон ячеек, войти в п. Формат - Ячейки - вкладка Защита, стереть "галочку" и нажать Enter или OK (предварительно все ячейки таблицы считаются защищаемыми).

б) защита таблицы (как обычно). В данном случае защитите таблицу с паролем (п. Сервис – Защита - Защитить лист). Задайте пароль - QSW.

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

  2. Попробуйте удалить одну из строк и вставить одну строку в середину таблицы. Что при этом происходит?

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

Защита рабочей книги (файла) паролем

Н
ередко в компьютерах хранят конфиденциальную информацию. Для предотвращения несанкционированного доступа к данным следует при сохранении рабочей книги установить пароль.

  1. Выберите п. Файл - Сохранить как. В появившемся окне нажмите кнопку Параметры. Откроется подчиненное окно, которое показано на следующем рисунке. Первый пароль запрашивается при открытии рабочей книги, а второй влияет на режим, в котором рабочая книга будет открыта. Так, если второй пароль неизвестен, то рабочая книга может быть открыта только в режиме чтения. Вводимые вами символы не отображаются - вместо них выводятся звездочки, что не позволяет постороннему человеку подсмотреть пароль. Для надежности программа требует ввести пароль дважды. Запишите на бумаге введенные вами пароли.

  2. Закройте рабочую книгу, а затем откройте ее. Будут запрошены пароли.

  3. Сдайте работу преподавателю.

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

Вопросы к лабораторной работе № 3

Как скрыть строки таблицы? Как отобразить строки таблицы?

Как можно скрывать и отображать столбцы?

Когда может понадобиться срывать строки или столбцы?

Как изменить ширину столбца таблицы? Как изменить высоту строки таблицы?

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

Как форматируются ячейки, содержащие числа?

Что представляет собой формат числа: "проценты"?

Как устанавливается количество знаков в дробной части числа?

Как изменить размер шрифта ?

Как меняется цвет фона в ячейках? Как меняется цвет символов в ячейках?

Как отключить вывод "сетки" на экран?

Как провести горизонтальные и вертикальные линии в таблице?

Что понимается под условным форматированием?

Как просмотреть образ документа, который будет выведен на печать?

Какие параметры страницы существуют и могут быть изменены пользователем?

Зачем нужна защита ячеек от редактирования? Как защитить от редактирования ячейки? Как снять защиту от редактирования с ячеек?

Что происходит, если делается попытка отредактировать защищенную ячейку?

Что происходит при попытке удалить строку, имеющую защищенные ячейки?

Как предотвратить несанкционированный доступ к рабочей книге?

Профессор кафедры Информационных технологий Музычкин П. А.

Доцент кафедры Информационных технологий Щёлоков А. Ф.



Скачать документ

Похожие документы:

  1. Лабораторная работа №1 (1)

    Лабораторная работа
    VBA относится к языкам объектно- ориентированного программирования (ООП). ООП можно описать как методику анализа, проектирования и написания приложений с помощью объектов.
  2. Лабораторные работы (2)

    Документ
    Одним из основных методов обучения физике в школе является проведение лабораторных работ. Но в то же время этот вид урока – один из самых сложных и хлопотных для учителя.
  3. Лабораторная работа №1 (4)

    Лабораторная работа
    Замечание: В задачах 1 – 3 «длинные» натуральные числа представить в виде одномерных массивов. Будем считать, что такое число имеет не более 100 цифр.
  4. Лабораторная работа 1

    Лабораторная работа
    Построение графиков функций – это, хотя и трудоемкая (при ручном выполнении), но в то же время весьма полезная математическая операция. Часто графики используются как наиболее простое и наглядное средство, позволяющее быстро выявить
  5. Лабораторные работы (1)

    Практикум
    К третьей группе – работы, посвященные специальным методам исследования образцов, моделей, элементов конструкций или сооружений (оптический метод и др.

Другие похожие документы..