Практические занятия по созданию таблиц Microsoft Excel

Задание 14

1. Переименовать Лист 1 в "Таблица", остальные листы книги удалить.

2. В столбец А, начиная с ячейки А1, ввести четные числа от 2 до 20.

3. Создать список из элементов: первый, второй, третий... десятый.

4. В столбец В, начиная с ячейки В1, внести созданные элементы списка.

5. В столбец С внести даты: 1 янв 06, 1 фев 06, 1 мар 06... 1 дек 06.

6. Между столбцами В и С добавить новый столбец.

7. Строку "6" скрыть.

8. Содержимое ячеек В2 и В3 скопировать в ячейки Е4 и Е5.

9. Добавить новый лист и назвать его Копия.

10. Содержимое листа Таблица скопировать на лист Копия.

Указания

1. Для переименования листа щелкните по корешку листа правой кнопкой мыши выполните команду контекстного меню Переименовать. Введите новое название листа.

3. Для быстрого заполнения ячеек Excel однотипными данными можно пользоваться заранее подготовленными списками, например, первый, второй и т. д.

Для создания списка выполните команду меню Сервис-Параметры и перейдите на вкладку Списки. Введите в поле "Элементы списка"сами элементы через запятую или нажимая клавишу Enter. После внесения всех элементов списка нажмите кнопку Ок.

4. Введите в ячейку В1 первый элемент: "первый". Заполнением вниз введите остальные элементы (аналогично п. 2).

В ячейку С1 введите дату в следующем формате: 01.01.06, а в ячейку С2 – 01.02.06.

Для ввода других дат в столбец С используйте заполнение аналогично п. 2.

7. Для скрытия шестой строки выделите ее и выполните команду контекстного меню Скрыть.

Для отображения шестой строки выделите строки 5 и 7 и выполните команду контекстного меню Отобразить.

8. Выделите ячейки В2 и В3 и выполните команду Копировать. Выделите ячейку Е4 и выполните команду Вставить.

9. На корешке любого листа выполните команду контекстного меню Добавить, в открывшемся окне выделите пиктограмму Лист и нажмите кнопку Ок.

Задание 15

Создайте таблицу по приведенному образцу и сохраните книгу под именем фамилия15. Значения в ячейках "Итоги" и "Всего за квартал" рассчитайте по формулам. Проведите сортировку записей по отделам.

	A	B	C	D	E
	Фамилия	Отдел	январь	февраль	март
	Андропов А.А.
	Брежнев Б.Б.
	Иванов И.И.
	Иванов И.И.
	Петров П.П.				1 200
	Сидоров С.С.
	Черненко Ч.Ч.				1 100
	Итого
	Всего за квартал

Указания

1. Для объединения ячеек с В10 по Е10 выделите эти ячейки и нажмите кнопку Объединить и поместить в центр на панели инструментов.

2. Для ввода формулы в ячейку С9 выделите ее и нажмите кнопку Автосумма на панели инструментов. Убедитесь, что диапазон суммируемых ячеек выделен правильно, после чего повторно нажмите кнопку Автосумма.

Введите формулы в ячейки D9 и Е9 заполнением.

3. Для ввода формулы в ячейку В10 выделите ее и введите формулу, начинающуюся со знака "равно": = С9+D9+E9.

Для этого введите знак "=", затем щелкните мышью по ячейке С9, затем введите знак "+", затем щелкните мышью по ячейке D9 и так далее. После ввода текста формулы нажмите клавишу Enter.

Задание 16

Создайте таблицу и сохраните ее по именем фамилия16. Значения в столбце "Цена в руб." рассчитайте по формулам. Постройте гистограмму "Телевизоры-Цена в руб.".

Указания

2. Введите в ячейку С4 формулу = В4*С2. Для того, чтобы ввести формулы без изменения адреса С2 в другие ячейки заполнением или копированием необходимо в формуле в ячейке С4 относительный адрес ячейки С2 заменить на абсолютный. Для этого установите курсор в формуле на адресе С2 и нажмите клавишу F4. После этого адрес в формуле измениться на следующий: = В4*$С$2. Нажмите клавишу Enter.

Введите формулы в другие ячейки заполнением.

2. Для построения гистограммы выделите ячейки, значения в которых войдут в гистограмму – ячейки А3:А10 и ячейки С3:С10.

Для того, чтобы выделить несмежные ячейки сначала выделите ячейки столбца А, а затем при нажатой клавише Ctrl – ячейки столбца С.

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

Задание 17

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

Указания

Постройте гистограмму "Месяц-Прибыль".

Перейдите на вкладку Параметры и в поле "Прогноз" установите значение "вперед на 2".

Нажмите кнопку Ок.

Задание 18

Создайте таблицу и сохраните ее под именем фамилия18.

Выполните следующие условия.

1. Для столбца "Дата рождения" установите проверку вводимых значений: интервал вводимых значений - от 01.01.20 до 01.01.98.

При ошибке установите вывод сообщения "Дата рождения не верна!"

2. Для столбца "Разряд" примените условное форматирование:

1- красный шрифт, 2 - зеленый, 3 - синий, остальные черный.

Указания

Проверьте работу проверки даты, введя неверную дату, например: 01.01.01.

2. Выделите столбец С и выполните команду меню Формат-Условное форматирование. Установите в полях для цифры 1 значения согласно рисунку.

В окне Условное форматирование нажмите кнопку А также>> и введите условия для цифры 2, а затем цифры 3.

После ввода всех трех условий нажмите кнопку Ок.

Проверьте работу условного форматирования, меняя разряды работников.

Задание 19

Создайте таблицу и сохраните ее под именем фамилия19.

Значения в столбцах с С по G рассчитайте по приведенным ниже формулам.

Столбец С:если Оклад<4000, то Премия=1600, иначе Премия=Оклад*0,4

Столбец D:=(Оклад+Премия)*0,15

Столбец E:=Оклад+Премия+Уральские

Столбец F:=Всего*0,13

Столбец G:=Всего-Подох. Налог

Указания

2. Для ввода формулы в ячейку С используется логическая функция Если.

Выделите ячейку С2 и нажмите кнопку Вставка функции слева от строки формул.

Введите выражения в поля "Лог_выражение" и "Значение" согласно приведенному ниже рисунку.

Нажмите кнопку Ок.

Введите остальные формулы.

Задание 20

Переименуйте Лист 1 в 1 квартал, создайте на нем таблицу продаж товаров в 1 квартале и сохраните книгу под именем фамилия20.

Значения в столбцах "Всего за квартал", "Количество", "Месяц" и "Процент от квартальной" рассчитайте по формулам. В столбце "Месяц" должно выводиться название месяца, в котором больше всего продано товара, в столбце "Количество" – количество товара в этом месяце.

Переименуйте Лист 2 в 2 квартал и создайте на нем аналогичную таблицу продаж товаров во 2 квартале.

Создайте новую книгу с таблицей "Продажи за 1 полугодие" и сохраните ее под именем 1 полугодие.

Значения в столбцах "1 кв." и "2 кв." рассчитайте по формулам. Текст в ячейках D2-D6 – результат использования формул с текстом.

Указания

1. Значения в столбце Gрассчитайте по формуле МАКС, которая находит максимальное значение из нескольких ячеек.

Для этого выделите ячейку G3, нажмите кнопку Вставка функций, в категории "Статистические" выберите функцию МАКС и нажмите кнопку Ок.

После открытия окна "Аргументы функции" введите диапазон ячеек, из которых надо выбрать максимальное значение (B3-D3), для чего выделите мышью эти ячейки (их адреса появятся в поле "Число1").

Нажмите кнопку Ок.

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

Если значение Количество=значению в январе, то значение январь, иначе, если Количество=значению в феврале, то значение февраль, иначе значение март.

Для ввода формулы выделите ячейку F3, нажмите кнопку Вставка функций, в категории "Логические" выберите функцию ЕСЛИ и нажмите кнопку Ок.

После открытия окна "Аргументы функции" введите в поля элементы формулы согласно рисунку.

Установите курсор в поле Значение_если_ложь и снова вызовите функцию ЕСЛИ, нажав на кнопку с надписью "ЕСЛИ" слева под панелью инструментов.

Введите в поля вложенной функции элементы согласно рисунку, после чего нажмите кнопку Ок.

3. В ячейку H3 введите формулу =G3/E3. Установите для столбца Hпроцентный формат, нажав кнопку Процентный формат на панели инструментов.

4. Для создания таблицы продаж товаров во 2 квартале скопируйте таблицу с листа 1 квартал на лист 2 квартал и в ней измените названия месяцев и числовые данные.

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

Для этого выделите ячейку В2, введите знак"=", перейдите в книгу "фамилия19", щелкните по ячейке Е3 и нажмите клавишу Enter. Формула в ячейке В2 будет иметь вид: ='[иванов19.XLS]1 квартал'!$E$3.

Введите аналогичные формулы в столбец С.

6. Формула в ячейке D3 должна иметь текст.

Текст в формуле пишется в кавычках, а разделяются элементы формулы знаком &.

Соответственно формула в ячейке D3 должна иметь следующий вид: =A2&" - "&B2+C2&" шт.".

Задание 21

Определите для каких наборов значений переменных х и у Р будет истинно?

Р = х+у > х-у или х+2у > 0

х	-1	-1
у			-1

Указания

Введите в ячейки А1:Е1 приведенную таблицу.

Выделите ячейку В3 и вызовите функцию ИЛИ. Введите в поля выражения согласно рисунку и нажмите кнопку Ок.

Аналогично решите задачу для других наборов значений х и у (формулы в ячейки С3:Е3 можно ввести заполнением).

Результат решения показан рисунке: при всех наборах х и у Р истинно.

Задание 22

Защитите от изменения ячейки А1 и А2 таблицы задания 21.

Указания

Выделите весь лист и выполните на выделении команду контекстного меню Формат ячеек. Перейдите на вкладку Защита, снимите флажок "Защищаемая ячейка" и нажмите кнопку Ок.

Выделите ячейки А1 и А2и выполните на выделении команду контекстного меню Формат ячеек. На вкладке Защита установите флажок "Защищаемая ячейка" и нажмите кнопку Ок.

Выполните команду меню Сервис-Защита-Защитить лист. При необходимости введите пароль и нажмите кнопку Ок.

Проверьте работу защиты, изменив содержимое ячейки А1.

Для снятия защиты выполните команду меню Сервис-Защита-Снять защиту листа.

Задание 23

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

Указания

Необходимо создать четыре макроса, каждый из которых вставляет в выделенную ячейку текст: "отлично", "хорошо", "удовлетворительно" и "неудовлетворительно". Макрос – это записанная последовательность команд, которая может быть воспроизведена.

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

Наберите на клавиатуре текст "отлично", нажмите клавишу Enter и выполните команду меню Сервис-Макрос-Остановить макрос.

Аналогично создайте другие макросы.

Измените надпись на кнопке, скорректируйте ее размеры и перетащите мышью в нужное место листа.

Проверьте, работает ли кнопка с макросом.

Аналогично создайте другие кнопки.

Задание 24

Создайте таблицу по приведенному образцу и сохраните под именем фамилия24.

1. Вставьте со связыванием таблицу в документ Word.

2. Скопируйте таблицу на лист 2 и на лист 3.

3. На листе 1, используя автофильтр, выведите список работников, имеющих зарплату в январе больше 5000 руб.

4. На листе 2, используя расширенный фильтр, выведите список работников, старше 40 лет или имеющих зарплату за квартал менее
18000 руб.

5. На листе 3 используя команду Итоги, рассчитайте зарплату по отделам, то есть выведите общую зарплату 1-го отдела , 2-го отдела и 3-го отдела по месяцам.

Указания

1. Выполните команду Копировать для созданной таблицы Excel.

Откройте редактор Word, установите курсор в том месте документа, куда должна быть вставлена таблица и выполните команду меню Правка-Специальная вставка.

В открывшемся окне в поле "Как" выделите "Лист Microsoft Excel" и установите переключатель в положение "Связать".

Нажмите кнопку Ок.

2. Скопируйте таблицу, перейдите на Лист 2 и на ячейке А1 выполните команду контекстного меню Вставить.

3. Выделите любую ячейку в таблице и выполните команду меню Данные-Фильтр-Автофильтр.

Для отмены автофильтра выполните повторно команду меню Данные-Фильтр-Автофильтр.

4. Добавьте два столбца "Квартал" и "Возраст". Введите в столбец "Квартал" формулы суммирования зарплат =СУММ(D2:F2), в столбец "Возраст" – формулы расчета возраста работника =(ТДАТА()-B2)/365.

Вставьте копию заголовков таблицы в строку 11. Теперь в строках 12 и 13 могут задаваться критерии фильтра.

В ячейку G12 введите: <18000, а ячейку Н13 введите: >40.

Если условия фильтра записываются в разных строках (как в данном примере), то это соответствует функции ИЛИ. Если условия фильтра записываются в одной строке, то это соответствует функции И.

Для отмены фильтра выполните команду меню Данные-Фильтр-Отобразить все.

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

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

Задание 25

Найдите корень уравнения Cos(b) - 3,63b - 5,5 = 0.

Указания.

Пусть значение b будет в ячейке А1.

В ячейку В1 введите формулу: = COS(A1) - 3,63*A1 - 5,5

После выполнения подбора параметра будет получен результат, приведенный на рисунке, то есть корень уравнения в = -1,494.

Задание 26

Найти все корни уравнения х² + 2*х – 5 = 0

Указания

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

Для приближенного нахождения корней можно построить график функции у = х² + 2*х – 5 и по графику определить приближенные значения х, при которых у близко нулю.

По данным таблицы постройте точечную диаграмму и определите приближенные значения х, при которых у ≈ 0.

В данном примере значения х равны -3 и 1.

Проведите подбор параметра при этих значениях х.

Для этого можете выделить ячейку В4 и для нее провести подбор параметра. Затем то же самое выполнить для ячейки В8.

Значения корней уравнения будут находиться в ячейках А4и А8.

Задание 27

Создайте таблицу графика работы персонала и сохраните ее под именем фамилия24.

Значения в строке 9 и в ячейке В14 рассчитайте по формулам.

В ячейке В9 - общее число занятых =СУММ(B2:B8);

В ячейке С9 – сумма произведений соответствующих элементов столбцов В и С =СУММПРОИЗВ($B$2:$B$8;C2:C8); в ячейкеD9 – соответственно столбцов В и D и так далее.

В ячейке В14 – произведение ячеек В9 и В13.

В строке 9 приведено минимальное количество работающих в каждый день недели.

Подберите график работы персонала, минимизирующий общую зарплату (значение в ячейке В14) при условии, что работает каждый сотрудник 5 дней в неделю с двумя подряд идущими выходными при следующих ограничениях:

число занятых должно быть целое;

число занятых должно быть больше или равно нулю;

всего занятых по дням недели должно быть больше или равно числу требующихся по дням недели.

Указания

Выделите ячейку В14 и выполните команду меню Сервис-Поиск решения.

В открывшемся окне Поиск решения должна быть установлена целевая ячейка В14.

Установите группу переключателей в положение "минимальному значению".

В поле "Изменяя ячейки" установите интервал ячеек В2:В8.

Для ввода ограничений в поле "Ограничения" нажмите кнопку Добавить и в окне Добавление ограничения введите первое ограничение в соответствии с рисунком. Затем снова нажмите кнопку Добавить и введите второе ограничение, а затем – третье. Нажмите кнопку Ок.

В окне Поиск решения нажмите кнопку Выполнить.

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

Задание 28

Используя команду Поиск решения, найдите значения Х1, Х2, Х3 и Х4, удовлетворяющие следующим условиям:

Х1, Х2, Х3, Х4 ≥ 0;

Х1 + Х2 + Х3 = 0;

2(Х1 - Х2)+3(Х3 + Х4) = 9;

Х1 - Х2 + 2Х3 - Х4 = -3;

-3Х1 + Х2+ 3Х3 + 5Х4 → min;

Указания

Пусть значения Х1, Х2, Х3, Х4 будут в ячейках А1, А2, А3, А4,соответственно.

В ячейку В1 введите формулу =А1+А2+А3;

В ячейку В2 введите формулу =2*(А1-А2)+3*(А3+А4);

В ячейку В3 введите формулу =А1-А2+2*А3-А4;

В ячейку В4 введите формулу =-3*А1+А2+3*А3+5*А4.

Выделите ячейку В4и выполните команду меню Сервис-Поиск решения.

В открывшемся окне Поиск решения в группе переключателей установите значение "минимальному значению".

В поле "Изменяя ячейки" введите адреса А1:А4.

В поле "Ограничения" введите ограничения согласно приведенному рисунку, для чего нажмите кнопку Добавить и введите ограничения.

Нажмите кнопку Выполнить.

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

Задание 29

Создайте таблицу и сохраните ее под именем фамилия29.

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

Результат может быть получен с помощью сводных таблиц.

Выделите любую ячейку таблицы, выполните команду меню Данные-Сводная таблица и выполните инструкции мастера сводных таблиц.

В созданном листе перетащите мышью имена полей из списка полей сводной таблицы в саму таблицу согласно рисунку: "Вид обуви" перетащите в поля столбцов; "Модель" – в поля строк; "Сумма" – в поле данных.

После этого сводная таблица приобретет следующий вид.

Задание 30

Найдите произведение матриц А*В, где .

Указания

Введите в ячейки А1:D4 матрицу А, а в ячейки F1:F4 – матрицу В.

Выделите ячейки Н1:Н4 и вызовите функцию МУМНОЖ (произведение матриц).

В поле "Массив1" введите адреса ячеек А1:D4, а поле "Массив2" –адреса ячеек F1:F4 согласно приведенному рисунку.

Нажмите комбинацию клавиш Ctrl+Shift+Enter.

После выполнения умножения результат должен выглядеть в соответствии с приведенным рисунком.

Задание 31

Найдите обратную матрицу для матрицы .

Указания

Введите в ячейки А1:D4 матрицу А.

Выделите ячейки F1:I4 и вызовите функцию МОБР (обратная матрица).

В поле "Массив" введите адреса ячеек А1:D4 согласно приведенному рисунку.

Нажмите комбинацию клавиш Ctrl+Shift+Enter.

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

СПИСОК ЛИТЕРАТУРЫ

1. Долженков В. А., Колесников Ю. В. Самоучитель Microsoft Excel 2003 / В. А. Долженков, – СПб.: БХВ – Петербург, 2004. – 432 с.

2. Информатика. Базовый курс. 2-е издание / Под ред. С. В. Симоновича. – СПб.: Питер, 2005. – 640 с.

3. Миллхоллон М., Мюррей К. Эффективная работа: Microsoft Office Word 2003 – СПб: Питер, 2005. – 971 с.

4. Немнюгин С. А. Turbo-Pascal. Программирование на языке высокого уровня. Учебник для вузов. – СПб.: Питер, 2003. – 544 с.

5. Харитонова И. А. Самоучитель: Office Access 2003. – СПб.: Питер, 2004. – 464 с.

[1] дошкольное образование и начальные классы

[2] колледжи и университеты