Практическая работа 11. 1. MS Excel. Назначение и интерфейс

Выполнив задания этой темы, вы научитесь:

1. Запускать электронные таблицы;

2. Основные понятия: ячейка, строка, столбец, адрес ячейки;

3. Ввод данных в ячейку;

4. Строка формул;

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

Задание: Познакомиться практически с основными элементами окна MS Excel 2003. выполнить отчет в рабочей тетради по поставленным вопросам.

1. Чтобы достичь быстро конца таблицы по горизонтали или вертикали, необходимо нажать комбинации клавиш: Ctrl+→ - конец столбцов или Ctrl+↓ - конец строк. Быстрый возврат в начало таблицы - Ctrl+Home.

2. Выделите всю таблицу. Воспользуйтесь пустой кнопкой.

3. Определите количество листов в Книге1. Вставьте через контекстное меню Добавить–Лист два дополнительных листа. Для этого встаньте на ярлык листа Лист 3 и щелкните по нему правой кнопкой, откроется контекстное меню выберите опцию Добавить и выберите в окне Вставка Лист. Добавлен Лист 4. Аналогично добавьте Лист 5. Внимание! Обратите внимание на названия новых листов и место их размещения. Измените порядок следования листов в книге. Щелкните по Лист 4 и, удерживая левую кнопку, переместите лист в нужное место.

4. Установите количество рабочих листов в новой книге по умолчанию равное 3. Для этого выполните команду Сервис–Параметры–Общие.

5. Закройте документ, щелкнув по кнопке Закрыть (внутренний крестик)на вопрос о сохранении документа, выберите НЕТ. Выйдите из окна приложения: Файл – Выход.

Задание: Выполните в таблице ввод необходимых данных и простейшие расчеты по данным.

Технология выполнения задания:

1. В ячейку А1 Листа 2 введите текст: "Год основания КазГЮУ". В ячейку C1 введите число – номер текущего года.

Внимание! Обратите внимание на то, что в текстовые данные выравниваются по левому краю, а числа и даты – по правому краю.

2. Выделите ячейку D1, введите с клавиатуры формулу для вычисления возраста университета: = C1- B1

Внимание! Ввод формул всегда начинается со знака равенства «=». Адреса ячеек нужно вводить латинскими буквами без пробелов. Адреса ячеек можно вводить в формулы без использования клавиатуры, а просто щелкая мышкой по соответствующим ячейкам.

3. В ячейке D1 установите знак «=», далее щелкните мышкой по ячейке C1, обратите внимание адрес этой ячейки появился в D1, поставьте знак «–» и щелкните по ячейке B1, нажмите {Enter}.

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

5. Для изменения ширины столбца используют также команды меню Формат – Столбец – Ширина – Автоподбор ширины или Стандартная ширина.

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

7. Верните первоначальную высоту строки (12,75 пт).

8. Перейдите в строку 5. С помощью команд меню Формат – Строка – Высота строки измените высоту строки 5 до 40 пт.

9. Верните высоту строки, используя команду Автоподбор высоты.

10. В ячейку А2 введите текст "Мой возраст".

11. В ячейку B2 введите свой год рождения.

12. В ячейку С2 введите текущий год.

13. Введите в ячейку D2 формулу для вычисления Вашего возраста в текущем году (= C2- B2).

14. Выделите ячейку С2. Введите номер следующего года. Обратите внимание, перерасчет в ячейке D2 произошел автоматически.

15. Определите свой возраст в 2025 году. Для этого замените год в ячейке С2 на 2025.

16. Очистите содержимое любой ячейки командой Правка – Очистить – Все.

Самостоятельная работа

Упражнение: Посчитайте, используя ЭТ, хватит ли вам 130 тенге, чтоб купить все продукты, которые вам заказала мама, и хватит ли купить чипсы за 25 тенге?

№	Наименование	Цена в тенге	Количество	Стоимость
	Хлеб	9,6		=С2*D2
	Кофе	2,5		=С3*D3
	Молоко	13,8		=С4*D4
	Пельмени	51,3		=С5*D5
	Чипсы			=С6*D6

Практическая работа 11.2. MS Excel 2003. Ввод и редактирование данных. Адресация ячеек

Выполнив задания этой темы, вы научитесь:

· Изучить основные возможности форматирования таблиц на примере простого бланка-счета за ремонт телевизора.

Задание.

1. Изучим основные возможности форматирования таблиц на примере простого бланка-счета за ремонт телевизора.

• Введите в ячейку А1 строку «Счет за ремонт телевизора». Введите в ячейку A3 символ «№», в А4 - 1, в А5 - 2. Введите в ячейку ВЗ строку «Наименование работ», в В4 - Замена кинескопа, в В5 - Ремонт антенны, в В6 - Итого:, в В7 - НДС:, в В8 - Спецналог, в В9 - К оплате:. Введите в ячейку СЗ строку «Стоимость работ», в С4 - 200, в С5 -15.

2. Выбор ширины и выравнивание текста.

• Щелкните мышью по ячейке А1 и выберите шрифт Arial и размер шрифта 14.

• Щелкните мышью на заголовке третьей строки. Выделится вся строка. Назначьте выделенной строке шрифт Times New Roman размером 12 пунктов. При этом строки в ячейках ВЗ и СЗ выйдут за пределы столбцов. Подберите ширину столбцов В и C.

• Отцентрируйте текст в ячейках строки 3, нажав кнопку По центру.

• Выделите диапазон ячеек А1:С1 и нажмите кнопку - Объединить и поместить в центре на панели инструментов Форматирование. Текст будет выровнен по центру выделенного диапазона.

• Выделите диапазон ячеек В6:В9 и выровняйте текст в ячейках по правому краю, нажав кнопку По правому краю. Начертание шрифта Полужирное.

• Подсчитайте, сколько будет стоить замена кинескопа и ремонт антенны, и результат занесите в ячейку Итого:. Для этого выделите диапазон ячеек С4:С6 и щелкните по кнопке Автосумма на панели Стандартная. В ячейке С6 появится результат вычислений «215».

• Введите в ячейку С7 формулу для вычисления НДС «=С6*0,2».

• Введите в ячейку С8 формулу для вычислений спецналога «=С6*0,015». Выделите диапазон ячеек С6:С9 и щелкните мышью по кнопке Автосумма. В ячейку С9 будет вставлен результат вычислений «261,225»

3. Форматирование чисел.

• Выделите диапазон ячеек С4:С9. Щелкните по выделенному фрагменту правой кнопкой мыши и выберите из контекстного меню команду Формат ячеек.

• В диалоговом окне щелкните мышью на ярлычке с надписью Число, в списке Числовые форматы выберите формат Денежный. Выделенные суммы отобразятся с разделением тысяч и символом «р.» в конце.

4. Обрамление и фон ячеек.

• Выделите диапазон ячеек А1:С9. Выберите команду меню Формат - Ячейки. В диалоговом окне выберите вкладку Граница.

• Щелкните мышью на изображении двойной линии в поле Тип линии и затем в поле Все щелкните по кнопке Внешние. В поле Отдельные появится образец обрамления двойной линией. - ОК.

• Выделите диапазон ячеек АЗ:СЗ. Щелкните мышью на панели Форматирование по пиктограмме Границы и выберите там шаблон с толстой черной линией снизу. Выделенный диапазон ячеек будет подчеркнут толстой черной линией.

• Выделите диапазон ячеек ВЗ:В9 и создайте тонкую черную черту справа с помощью пиктограммы Границы. Выделите диапазон ячеек А9:С9.

• Откройте цветовую палитру (на панели Форматирование пиктограмма Цвет заливки) для выбора цвета фона. Щелкните мышью на квадратике желтого цвета в палитре. Фон выделенного диапазона ячеек станет желтым. Черные цифры на желтом фоне хорошо видны на экране, а при печати на черно-белом принтере желтый цвет будет выглядеть как светло-серый. На этом создание нашего бланка закончено.

5. Изменение информации в бланке.

Для этого выделите ячейку С4 и введите новую стоимость 250- Enter.

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

• Выберите команду меню Файл - Параметры страницы. Появится диалоговое окно.

• Выберите вкладку Страница. Установите опцию Книжная в поле Ориентация.

• Откройте список Размер бумаги и выберите строку А4 210*297 мм.

• Выберите вкладку Поля. Установите желаемые отступы от краев листа бумаги с помощью полей Верхнее, Нижнее, Левое, Правое или оставьте прежние.

• В поле Центрировать на странице установите флажок Горизонтально и сбросьте флажок Вертикально. Теперь наш бланк будет размещен в верхней части листа и выровнен по центру ширины листа.

• Выберите вкладку Колонтитулы. Колонтитулы - это первая и последняя строки документа, которые используются для размещения служебной информации: номеров страниц, названия документа, даты его создания и т. д. Для нашего бланка ничего этого не нужно, поэтому просто уберем колонтитулы. Для этого откройте список Верхний колонтитул и выберите строку (нет). То же самое сделайте со списком Нижний колонтитул.

• Выберите вкладку Лист и в группе Печать сбросьте флажок Сетка (если он не сброшен), чтобы на печать не выводилась сетка, разделяющая ячейки таблицы.

• Нажмите кнопку Просмотр. На экране появится изображение листа бумаги с нашим бланком. Это режим предварительного просмотра. Для выхода из просмотра - кнопка Закрыть. Если вы сделали все правильно, то должны получить бланк следующего вида.

7. Выведем бланк-счет на печать, нажав на панели Стандартная кнопку Печать.

Задание 1.

Технология выполнения задания:

1. Выделите ячейку А1 и введите слово Информатика. Скопируйте ячейку А1в буфер обмена и вставьте ее в ячейку А5. (Правка - Буфер обмена, Правка - Копировать). Обратите внимание, в ячейке буфера обмена находится слово Информатика. В ячейку С1 введите слово Математика. Скопируйте его в буфер обмена. Обратите внимание, вам сейчас доступны два слова: Информатика и Математика.

2. Вставьте слово Информатика в ячейку А7 из буфера обмена, вставьте слово Математика из буфера обмена в ячейку D5. Нажмите Enter.

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

4. Режимом копирования и вставки можно управлять с помощью клавиатуры. Скопируйте с помощью клавиатуры (нажав клавиши Ctrl+C) содержимое ячейки А7 в ячейку А9 (нажав клавиши Ctrl+V).

5. С помощью команд меню Правка – Вырезат ь, а затем Правка – Вставить переместите содержимое ячейки А5 в ячейку А11.

6. Выделите ячейку А11 и заполните ее содержимым пять рядов вниз, с помощью маркера автозаполнения - квадрат в правом нижнем углу.

7. В ячейку F1 введите любое число, в ячейку G1 - любое слово, а в ячейку H1 введите 2008.

8. Выделите блок F1:H1 и протащите его маркер заполнения на 10 строк вниз. Проанализируйте результат.

9. Выделите столбец F и удалите его с помощью команды меню Правка - Удалить. Обратите внимание на смещение столбцов.

10. Удалите столбец F, используя клавишу Delеte (с сохранением “пустого” места). Очистите столбец G, используя контекстное меню.

11. Выделите блок A5:H14 и очистите его, используя команды меню Правка – Очистить – Все.

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

Технология выполнения задания:

1. Создайте таблицу:

№ пп	Наименование затрат	Цена (тенге)	Кол-во	Стоимость	В % от общего кол-ва затрат
1.	Стол
2.	Стул
3.	Компьютер	14 976
4.	Доска школьная
5.	Дискеты
6.	Кресло	2 500
7.	Проектор	12 000
	Общее кол-во затрат

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

3. Вычислите Общее количество затрат в столбце Стоимость, используя кнопку Автосумма.

4. Для каждого наименования затрат вычислите долю затрат (в %) от общего количества затрат.

Внимание! При вычислении долей (в %) используйте формулу, содержащую в качестве делителя абсолютный адрес ячейки с числом, обозначающим суммарное количество затрат (например, =E2/$E$9).

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

6. С помощью команды Формат - Строка - Скрыть скройте (временно удалите из таблицы) последнюю строку.

7. Выделите созданную таблицу вместе с находящейся под ней пустой строкой и скопируйте ее ниже на этом же листе.

8. В исходном экземпляре таблицы выделите две строки, находящиеся выше и ниже удаленных строк, и восстановите скрытые строки с помощью команды Формат–Строка–Отобразить.

Отформатируйте исходный экземпляр таблицы, используя команды Формат – Ячейки:
шрифт – Courier New Cyr; начертание – полужирное; размер – 14 пт; выравнивание – По центру.

9. С помощью вкладки Вид выберите светло-серую заливку.

10. Отформатируем заголовок таблицы, предварительно выделив его. Формат – Ячейки – Выравнивание – По горизонтали: по центру – По вертикали: по центру – Отображение – Переносить по словам - Ок

11. Отформатируйте второй экземпляр таблицы с помощью команд меню Формат– Автоформат, выбрав одно из стандартных оформлений.

12. На листе Лист1 рабочей книги выделите строки с 1-й по 10-ю (исходный экземпляр таблицы).

лабораторная работа 11. MS Excel. Фильтрация (выборка) данных из списка

Задание.

Создайте таблицу в соответствие с образцом, приведенным на рисунке.

Технология выполнения задания:

1. Установите курсор-рамку внутри таблицы данных.

2. Выполните команду меню Данные - Сортировка.

3. Выберите первый ключ сортировки: в раскрывающемся списке "сортировать" выберите "Отдел" и установите переключатель в положение "По возрастанию" (Все отделы в таблице расположатся по алфавиту).

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

5. Установите курсор-рамку внутри таблицы данных.

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

7. Снимите выделение в таблицы.

 

8. У каждой ячейки заголовка таблицы появилась кнопка "Стрелка вниз", она не выводится на печать, позволяющая задать критерий фильтра. Мы хотим оставить все записи с ненулевым остатком.

9. Щелкните по кнопке со стрелкой, появившейся в столбце Количество остатка. Раскроется список, по которому будет производиться выборка. Выберите строку Условие. Задайте условие: > 0. Нажмите ОК. Данные в таблице будут отфильтрованы.

 

10. Вместо полного списка товаров, мы получим список проданных на сегодняшний день товаров.

11. Фильтр можно усилить. Если дополнительно выбрать какой-нибудь отдел, то можно получить список неподанных товаров по отделу.

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

13. Можно временно скрыть остальные столбцы, для этого, выделите столбец "№", и в контекстном меню выберите Скрыть. Таким же образом скройте остальные столбцы, связанные с приходом, расходом и суммой остатка. Вместо команды контекстного меню можно воспользоваться командой Формат-Столбец - Скрыть.

14. Чтобы не запутаться в своих отчетах, вставьте дату, которая будет автоматически меняться в соответствии с системным временем компьютера Вставка - Функция - Дата и время - Сегодня.

15. Как вернуть скрытые столбцы? Проще всего выделить таблицу всю целиком, щелкнув по пустой кнопке и выполнить команду Формат - Столбец - Показать.

16. Восстановите исходный вариант таблицы и отмените режим фильтрации. Для этого щелкните по кнопке со стрелкой и в раскрывшемся списке выберите строку Все, либо выполните команду Данные - Фильтр - Отобразить все.

Самостоятельная работа

1. Дана электронная таблица:

В ячейку D1 введена формула, вычисляющая выражение по формуле =(A2+B1-C1).

	A	B	C	D

1) 1 2) 2 3) 3 4) 4