Лабораторные (практические) работы в программе MS Excel (2 курс)

_{Табличный процессор} Microsoft Excel

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

Создание таблиц. Ввод данных. Форматирование и редактирование данных

Задание 1.

1. Запустите Microsoft Excel, и в созданной новой книге переименуйте листы: «Лист 1 » - в «Задание 1» и т. д.

2. Откройте лист «Задание 1». Задайте для столбца А ширину 20, а для столбцов В-N – 4.

Подсказка:

Выберите пункт меню «Справка».

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

Выберите пункт «ширина» и прочитайте, как изменить ширину сразу нескольких столбцов.

1. Задайте для первой строки высоту 45, а для строк с номерами 2 – 12 – высоту 21.

2. В ячейке D2 получите число – возраст Екатеринбурга. Для этого в ячейки В2 и С2 введите соответственно год основания города (1723) и текущий год, а в ячейку D2 – формулу =С2-В2. Попробуйте ввести в ячейки В2 и С2 другие числа. Посмотрите, как реагирует на это содержимое ячейки D2.

3. Выровняйте содержимое ячеек по центру. Для этого выделите их и используйте пункт меню Формат - Ячейки.

Задание 2.

1. На листе «Задание 2» подготовьте таблицу следующего вида:

   1 квартал 2003 года			Для объединения ячейки используйте кнопку Для оформления таблицы используйте кнопку
   январь	февраль	март

2. Создайте на листе «Задание 2» три копии получившейся таблицы.

3. Продолжите ряд одной из таблиц с помощью маркера заполнения , получив 2,3 и 4 кварталы.

Задание 3.

1. На листе «Задание 3» подготовьте таблицу следующего вида:

Рис.1

1. Дополните Вашу таблицу еще тремя предметами и добавьте столбец для подсчета среднего балл, как показано на рисунке 2. Подсказка: для вставки строк и столбцов используйте пункт меню «Вставка».

2. Подсчитайте средний бал по арифметической формуле.

ИТОГОВЫЕ ОТМЕТКИ

за 2 полугодие 2003 года

Рис.2

Задание 4.

1. Создайте новый лист в своей книге с помощью меню «Вставка» / «Лист».

2. Создайте на новом листе копию таблицы «Итоговые отметки» (рис.2).

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

Подсказка.

Для ввода функции используйте кнопку . В появившемся окне мастера функций из категории Статистические выберите функцию СРЗНАЧ. Охватите с помощью мышки диапазон ячеек с отметками по алгебре. Нажмите ОК. Далее заполните средний балл по остальным предметам. Для этого встаньте на ячейку со средним баллом по алгебре и, держа ее мышкой за правый нижний угол, растяните вниз на пустые ячейки. Как видите, формула скопировалась, причем адреса ячеек с отметками изменились автоматически. Здесь мы применили так называемый принцип относительной адресации.

Задание 5.

Составьте таблицу значений линейной функции У=3,5*Х-9,5.

Для заполнения ячеек значения Х введите первые два значения, выделите обе ячейки и протяните маркер заполнения вправо.

Таблица значений функции У = 3,5*Х - 9,5.

Задание 6.

Оформите таблицу, позволяющую рассчитать расход материалов для покраски, в зависимости от площади поверхностей. Введите формулы в столбцы «Расход». Обратите внимание на форматирование рамок таблицы. Индекс (м²) выполняется следующим образом: выделите «2» в Строке формул и выполните команду «Формат – Ячейки», выберите вкладку «Шрифт» и поставьте флажок «Верхний индекс».

Задание 7.

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

1. «Процент депозита годовой» = 30 %

2. «Сумма % %» = «Сумма вклада» * «Процент депозита годовой»

3. «Сумма к выплате» = «Сумма вклада» + «Сумма % %»

Получение денежных средств по депозитным вкладам за месяц

Задание 8.

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

Проверочные вопросы:

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

2. Как задать для листа другое имя?

3. Как разместить содержимое ячейки по центру?

4. Как скопировать группу ячеек?

5. Как добавить в готовую таблицу строки или столбцы?

6. Как сделать невидимой сетку листа?

7. Как произвести заполнение ячеек в геометрической прогрессии?

8. С помощью какой команды меню можно установить верхний или нижний индекс числа?

Табличный процессор Microsoft Excel

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

Относительная и абсолютная адресация.

Задание 1.

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

________________________________________________

Подсказка.

Подсчитайте стоимость комплектующих материалов, в отведенном для этого столбце. Сначала займемся системным блоком. Если курс доллара у Вас указан в ячейке С2, а стоимость системного блока в долларах - в ячейке С4, то в ячейку D4 нужно ввести формулу=С2*С4.

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

Чтобы отменить автоматическое изменение адреса ячейки, нужно назначить ей абсолютный адрес. Для этого необходимо проставить перед номером строки и (или) перед номером столбца знак $. Удалите скопированные формулы, а в формулу для системного блока внесите необходимые изменения. В нашем случае при копировании формулы изменяется только номер строки, поэтому окончательная формула должна выглядеть так:=С$2*С4. Теперь скопируйте формулу на нужные ячейки.

1. В графе «ИТОГО» подсчитайте общую сумму в долларах и в рублях, для этого используйте кнопку

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

_____________________________________________________________________________

САЛАТ ИЗ ПОМИДОРОВ С СЫРОМ

Задание 3.

Подготовьте таблицу следующего вида, введите формулу для расчёта стоимости товара в рублях.

Задание 4.

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

Проверочные вопросы:

1. Что такое относительная адресация и для чего она применяется?

2. Что такое абсолютная ссылка, какими они бывают?

3. Каким значком отмечается абсолютная ссылка?

4. Какой вид имеет формула частичной абсолютной ссылки для столбца?

5. Какой вид имеет формула частичной абсолютной ссылки для строки?

Табличный процессор Microsoft Excel

Формулы и функции

Задание 1.

Постройте таблицу, произведите все необходимые расчёты в построенной таблице.

Ход работы:

1. Введите наименование колонок: Номер, Фамилия, Зарплата и т.д.

2. Для заполнения ячеек А3:А7 используйте автозаполнение, для этого:

   • введите в колонку А2 значение 1;

   • выделите ячейку А2 , установите указатель в нижний правый угол и протащите маркер «+» правой кнопкой мыши до ячейки А7;

   • в выпавшем контекстном меню выберите команду заполнить

3. Введите в ячейки В2:В7 фамилии сотрудников.

4. Запишите в ячейки С2:С7 размер зарплаты, указанный в таблице.

5. Введите в ячейку D2 размер премии 100

6. Для заполнения ячеек D3:D7 используйте автозаполнение, для этого:

   • выделите ячейку D2 , установите указатель в нижний правый угол и протащите маркер «+» правой кнопкой мыши до ячейки D7;

   • в выпавшем контекстном меню выберите пункт Прогрессия, в окне диалога установите параметр геометрическая с шагом 1,15.

7. Введите в ячейку Е2 формулу для расчёта начислений по районному коэффициенту:=15%*(С2+D2)

8. Скопируйте формулу для ячеек Е3:Е7, для этого:

   • установите указатель в правый нижний угол ячейки Е2 и протащите маркер «+» левой клавишей мыши до ячейки Е7.

9. Введите в ячейку F2 размер подоходного налога по формуле: =13%*(С2+D2+E2).

10. Скопируйте формулу для ячеек F3:F7.

11. Введите в ячейку G2 формулу: =С2+D2+E2-F2.

12. Скопируйте формулу для ячеек G3:G7.

13. Для заполнения строки Общая сумма выделите ячейки G2:G7 и на панели инструментов нажмите кнопку автосуммирование

14. Рассчитайте в ячейке С9 Среднее значение по колонке Зарплата, для этого:

    • выделите ячейку С9;

• На панели инструментов нажмите кнопку вставка функции ;

• Выберите категорию Статистические и функцию СРЗНАЧ, нажмите ОК.

• Укажите диапазон значений, для этого в таблице с помощью мыши выделите ячейки С2:С7, затем нажмите ОК.

• Скопируйте формулу для ячеек D9:G9.

1. По алгоритму из п. 14 заполните строки с наибольшими значениями, используя функцию МАКС из категории Статические.

2. По алгоритму из п. 14 заполните строки с наименьшими значениями, используя функцию МИН из категории Статические.

Задание 2.

Создайте таблицу по образцу; заполните пустые ячейки.

Задание 3.

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

Проверочные вопросы:

1. Что такое мастер функций и для чего он нужен?

2. Какие функции вы знаете?

3. Укажите наиболее простой способ подсчитать сумму содержимого несколько ячеек?

Табличный процессор Microsoft Excel

Построение диаграмм

Задание 1.

Постройте круговую диаграмму по данным столбца «Зарплата», предварительно подсчитав «Сумму к выдаче».

Ход работы.

1. Выделите ячейки столбца «Зарплата», С3:С13, начиная с названия.

2. На панели инструментов нажмите кнопку Мастер диаграмм

3. На вкладке Стандартные выберите тип диаграммы Круговая и вид диаграммы – номер 2, нажмите кнопку Далее.

4. На вкладке Диапазон данных установите переключатель Ряды в столбцах.

5. В этом же окне щёлкните по вкладке Ряд.

6. Установите курсор в строке Подписи категорий и выделите в таблице ячейки например В3:В13.

7. Нажмите кнопку Далее.

8. В поле Название диаграммы введите «Данные о зарплате сотрудников».

9. На вкладке Подписи данных выберите подписи значений - доля.

10. Нажмите кнопку Далее, а затем Готово.

Задание 2.

Постройте гистограмму по данным столбцов Премия и Сумма к выдаче.

Ход работы.

1. Выделите ячейки D3:D13 и, удерживая нажатой клавишу Ctrl, ячейки F3:F13.

2. На панели инструментов нажмите кнопку Мастер диаграмм

3. На вкладке Стандартные выберите тип диаграммы Гистограмма и вид диаграммы – номер 4 (объемный вариант), нажмите кнопку Далее.

4. На вкладке Ряд в поле Ряд выберите Ряд1, в поле Имя установите курсор и выделите ячейку D1, аналогично установите название для Ряда2.

5. В поле Подписи оси Х установите курсор и выделите ячейки В3:В13.

6. Введите название диаграммы «Сравнительные показатели выплат сотрудникам», установите подписи данных – значение (см. Задание 1).

Продемонстрируйте свою работу преподавателю.

Задание 3.

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

Производство основных видов продукции

Черной металлургии в Пермской области в 1992 году.

Подсказка.

Выделите данные столбцов. Вызовите мастер диаграмм. Выберите график. Далее на вкладке «Ряд» укажите «Подписи оси Х» год выпуска продукции, протянув мышью столбец. Укажите «Имя» рядов (для каждого ряда свое имя). Далее напишите название графика. Укажите «Подписи данных».

Задание 4.

Используя набор данных «Затраты на посадку», составить таблицу, построить круговую диаграмму, отражающую оплату труда при посадке ягодных культур – крыжовника, земляники, малины и чёрной смородины.

Затраты на посадку:

Оплата труда при посадке крыжовника – 167 руб. Горючее, ядохимикаты и гербициды для посадки земляники – 115 руб. Удобрения при посадке чёрной смородины – 585 руб. Горючее, ядохимикаты и гербициды для посадки чёрной смородины – 585 руб. Посадочный материал при посадке земляники – 1750 руб. Оплата труда при посадке чёрной смородины – 150 руб. Удобрения при посадке малины – 532 руб. Удобрения при посадке крыжовника – 555 руб. Горючее, ядохимикаты и гербициды для посадки малины – 89 руб. Посадочный материал при посадке крыжовника – 594 руб. прочие расходы при посадке земляники – 584 руб. Оплата труда при посадке малины – 235 руб. Горючее, ядохимикаты и гербициды для посадки крыжовника – 92 руб. Удобрения при посадке земляники – 313 руб. Прочие расходы при посадке чёрной смородины – 260 руб. Посадочный материал при посадке малины – 1200 руб. Оплата труда при посадке земляники – 316 руб. Прочие расходы при посадке крыжовника – 388 руб. Посадочный материал при посадке чёрной смородины – 1100 руб. Прочие расходы при посадке малины – 474 руб.

Задание 5.

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

Динамика показателей актива баланса предприятия за год.

Подсказка. Прирост = На конец года – На начало года. % прироста = Прирост / На начало года

Табличный процессор Microsoft Excel

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

Логические функции в Excel

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

Примерные виды логических формул:

• ЕСЛИ (А12;’’Сдал’’;’’Не сдал’’)

• ЕСЛИ (А10;’’положительное’’; (A1

• И(лог_выражение1;лог_выражение2;…)

• ИЛИ (лог_выражение1;лог_выражение2;…)

• ЕСЛИ (И(А10;A1

• ЕСЛИ (И(С220;С275; “30”))

• ЕСЛИ (А150; “2”; ЕСЛИ (А151; “3”))