О сновы работы с табличным редактором

О СНОВЫ РАБОТЫ С табличным РЕДАКТОРОМ

M ICROSOFT EXEL 2010

 

 

Орлов, 2017

ЛАБОРАТОРНАЯ РАБОТА № 1

СОЗДАНИЕ, ЗАПОЛНЕНИЕ, РЕДАКТИРОВАНИЕ И ФОРМАТИРОВАНИЕ ТАБЛИЦ В ПРОГРАММЕ MS EXCEL

Задание 1. Запуск программы

ü Для запуска программы можно использовать команду главного меню Windows Пуск – Программы – Microsoft Excel или ярлык на рабочем столе.

 

Задание 2. Вид экрана

ü Ознакомьтесь с внешним видом программы

Вид экрана программы представлен на рис. 1. Основную часть окна (см. рис. 1) занимает пустая таблица, при этом на экране высвечивается только небольшой ее фрагмент. Реальный размер таблицы – 256 столбцов и 16384 строки. Для перемещения по таблице справа и снизу располагаются линейки прокрутки. Строки пронумерованы целыми числами от 1 до 65536, а столбцы обозначены буквами латинского алфавита A, B, …, Z, AA, AB, …. На пересечении столбца и строки располагается основной структурный элемент таблицы – ячейка.

Последняя строка окна – называется строкой состояния.

 Слева она содержит кнопки для перемещения по рабочим листам и “корешки” рабочих листов, которые по умолчанию содержат названия листов: Лист1, Лист2 и т.д.

 

Задание 3. Выделение фрагментов таблицы

ü Потренируйтесь в выделении каких-либо областей рабочего листа. Это действие необходимо перед форматированием ячеек.

 

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

ü Чтобы выделить одну строку, помещаем указатель мыши на номер строки на координатном столбце.

ü Для выделения нескольких строк перемещаемся по координатному столбцу, не отпуская левую клавишу.

ü Чтобы выделить один столбец, помещаем указатель мыши на букву на координатной строке.

 

ü Для выделения нескольких столбцов перемещаемся по координатной строке, не отпуская левую клавишу.

ü Для выделения нескольких ячеек перемещаемся по таблице при нажатой левой клавише.

Выделение снимается щелчком в любом месте экрана.

 

Задание 4. Изменение размеров ячеек

ü Потренируйтесь в изменении размеров строк и столбцов.

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

Если необходимо изменить размеры сразу нескольких ячеек, их необходимо сначала выделить.

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

 

Задание 5. Форматирование содержимого ячеек

Команда контекстного меню – Формат ячеек предназначена для выполнения основных действий с ячейками. Действие будет выполнено с активной ячейкой или с группой выделенных ячеек. Команда содержит следующие закладки:

 

ЧИСЛО – позволяет явно определить тип данных в ячейке и форму представления этого типа. Например, для числового или денежного формата можно определить количество знаков после запятой.

ВЫРАВНИВАНИЕ – определяет способ расположения данных относительно границ ячейки. Если включен режим “ПЕРЕНОСИТЬ ПО СЛОВАМ”, то текст в ячейке разбивается на несколько строк. Режим позволяет расположить текст в ячейке вертикально или даже под выбранным углом.

ШРИФТ – определяет параметры шрифта в ячейке (наименование, размер, стиль написания).

ГРАНИЦА – обрамляет выделенные ячейки, при этом можно определить толщину линии, ее цвет и местоположение.

ВИД – закрашивает фон ячеек с помощью выделенного цвета или узора.

ЗАЩИТА – устанавливается защита на внесение изменений.

Команда применяется к выделенной или активной в настоящий момент ячейке.

 

 

Л АБОРАТОРНАЯ РАБОТА № 2

Задание 1. Копирование содержимого ячеек

ü Выделяем исходную ячейку, помещаем указатель мыши на край рамки и при нажатой клавише <Ctrl> и левой клавише мыши перемещаем рамочку в новое место. При этом копируется содержимое ячейки, в том числе и формула.

 

Задание 2. Автозаполнение ячеек

ü Выделяем исходную ячейку, в нижнем правом углу находится маркеравтозаполнения, помещаем курсор мыши на него, он примет вид +; при нажатой левой клавише растягиваем границу рамки на группу ячеек. При этом все выделенные ячейки заполняются содержимым первой ячейки.

1. 2.

 

Задание 3. Автозаполнение числового ряда.

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

1. 2.

 

Задание 4. Автозаполнение дней недели, названий месяцев.

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

 

 

Ошибки в формулах

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

Excel может распознать далеко не все ошибки, но те, которые обнаружены, надо уметь исправить.

Ошибка #### появляется, когда вводимое число не умещается в ячейке. В этом случае следует увеличить ширину столбца.

Ошибка #ДЕЛ/0! появляется, когда в формуле делается попытка деления на нуль. Чаще всего это случается, когда в качестве делителя используется ссылка на ячейку, содержащую нулевое или пустое значение.

Ошибка #Н/Д! является сокращением термина "неопределенные данные". Эта ошибка указывает на использование в формуле ссылки на пустую ячейку.

Ошибка #ИМЯ? появляется, когда имя, используемое в формуле, было удалено или не было ранее определено. Для исправления определите или исправьте имя области данных, имя функции и др.

Ошибка #ПУСТО! появляется, когда задано пересечение двух областей, которые в действительности не имеют общих ячеек. Чаще всего ошибка указывает, что допущена ошибка при вводе ссылок на диапазоны ячеек.

Ошибка #ЧИСЛО! появляется, когда в функции с числовым аргументом используется неверный формат или значение аргумента.

Ошибка #ССЫЛКА! появляется, когда в формуле используется недопустимая ссылка на ячейку. Например, если ячейки были удалены или в эти ячейки было помещено содержимое других ячеек.

Ошибка #ЗНАЧ! появляется, когда в формуле используется недопустимый тип аргумента или операнда. Например, вместо числового или логического значения для оператора или функции введен текст.

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

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

Задание 5.

ü Создайте таблицу по образцу, используя прием автозаполнения ячеек

 

Задание 5.

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

Л АБОРАТОРНАЯ РАБОТА № 3

РАСЧЕТЫ В MS EXCEL

Задани е 1.

ü Оформить таблицу

ü Отформатировать данные по образцу табл.1. При этом в первую строку листа внести название таблицы и разместить посредине с использованием команды «Объединить и поместить в центре».

ü Средствами Excel рассчитать размер премии для каждого сотрудника (графа «Премия, руб.»), а также сумму выплаты: а) по каждой строке; б) по столбцу «Итого».

Задание 2.

ü Ввести данные согласно заданию на второй лист

ü Средствами Excel рассчитать сумму аренды помещения (исходя из курса доллара 30 руб. за 1USD).

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

ü Ввести данные согласно заданию (см. табл.3) на лист №3.

Задание 3.

ü Создайте таблицу следующего вида:

ü Произведите заполнение строки с номером 2 последовательностью месяцев: сентябрь, октябрь, ноябрь, декабрь, используя режим автозаполнения.

ü Заполните таблицу произвольными цифровыми значениями.

ü Подсчитайте значения в графе “ Итого ” за каждый месяц

ü Вставьте пустую строку с номером 7. В клетку А7 введите текст: “ Сумма доходов ”.

ü Подсчитайте значения в графе “ Сумма доходов ” за каждый месяц.

ü Удалите текст “ Итого ” в ячейке по адресу А16. Вместо него введите текст “ Сумма расходов ”.

ü Подсчитайте значение в графе “ Баланс ”, введя следующую формулу (для клетки В17 - за сентябрь): =В7 - В16 Т.е. сумма доходов минус сумма расходов. Пробелы в формуле не допустимы.

ü Аналогично подсчитайте значения в графе “ Баланс ” за остальные месяцы.

ЛАБОРАТОРНАЯ РАБОТА № 4

Задание 1.

ü Составьте таблицу объема продаж видеомагнитофонов.

 

Задание 2.

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

Последовательность построения:

ü Построить таблицу с исходными данными (см. выше).

ü Выделить блок клеток А2:В6.

ü Вызвать Мастера диаграмм.

ü Выбрать тип диаграммы - круговая и вид.

ü Подписи данных - Добавить

ü Поместить диаграмму на имеющемся листе, готово.

ü Щелчком выделить область диаграммы.

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

Задание 3.

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

 

Последовательность построения:

ü Построить таблицу с исходными данными (см. выше).

ü Выделить блок клеток А3:В9

ü Вызвать Мастера диаграмм

ü Выбрать тип диаграммы - гистограмма,

ü Убрать легенду, линии сетки

ü Вставить заголовки

ü Подписи данных - есть

ü Поместить диаграмму на имеющемся листе, готово

ü Щелчком выделить область диаграммы, вызвать контекстное меню и очистить

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

Задание 4.

ü 3 подростка: Вася, Петя и Саша в течение недели продавали газеты. Построить диаграмму продажи газет, если известно количество проданных каждым подростком газет. Затем с теми же данными построить график продаж и диаграмму с областями.(Самостоятельно по приведенным данным получить приведенные диаграммы).

 

Задание 5.

ü В Excel составьте таблицу и постройте гистограмму по образцу

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

Задание 6.

рис.4

Далее вызовите команду: Вставка – Гистограмма и выберите объемную гистограмму с группировкой. У вас на экране появится предварительная гистограмма, которая нуждается в редактировании! (рис.5)

рис.5

В данном макете еще нет подписей оси Х и У, отсутствует легенда и заголовок.

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

рис.6

Введите название диаграммы и подписи оси Х и У с клавиатуры.

На диаграмме нажмите правую кнопку мыши и вызовите команду: «Выбрать данные». В появившемся окне «Выбор источника данных в разделе «Элементы легенды (ряды) нажмите «Изменить».

 рис. 7

В поле «Имя ряда» для Ряда 1 левой кнопкой мыши укажите год (соответствующую ячейку – 2013).

Повторите аналогичную операцию для Ряда 2 (выберите соответствующую ячейку - 2014)

рис.8

В окне «Выбор источника данных» в разделе «Подписи горизонтальной оси (категорий)» укажите названия фондов.

рис 9.

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

После проделанных операций гистограмма примет внешний вид, как представлено на рис.10.

рис.10

 

Задание 2.

 

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

К ценам и прибыли добавьте денежный формат: Формат ячеек – закладка «Число» - «Денежный» (ед. изм. руб.)

Столбец «Стоимость» рассчитывается по формуле: = Цена закупки*Количество

Столбец «Цена реализации»: = Цена закупки*25% (соответствующая ячейка Е2) + Цена закупки

Столбец «Планируемая прибыль» = (Цена реализации – Цена закупки)*Количество

Строка «Итого» суммирует значения столбцов «Стоимость» и «Планируемая прибыль» (используйте функцию СУММ и выделите соответствующие ячейки: D3:D11 и F3:F11)

 

После расчета построить объемную гистограмму по планируемой прибыли. Гистограмма должна иметь заголовок, легенду (внизу), подписи на рядах данных (размером 8 пунктов), подписи оси Х и У.

 

Задание 3.

Подготовьте таблицу для вычисления значений функции y=x²+sinx. Для этого необходимо в ячейку В3 внести формулу (использовать мастер функций). Распространить формулы на диапазон ячеек С3:I3, используя маркер автозаполнения.

 

 

 

Составьте график функции.

 

Л АБОРАТОРНАЯ РАБОТА № 5

Выполнение.

В ячейки A3, В3 и С3 введем значения коэффициентов квадратного уравнения и обозначим эти ячейки именами a, b и с_. Ячейку А4, где будет размещаться значение дискриминанта, обозначим именем D. Для вычисления дискриминанта в ячейку А4 введем формулу =b^2-4*a*c_, затем для вычисления корней в ячейки А5 и А6 введем функцию ЕСЛИ с соответствующими условиями для a, b, c, и d и формулами для корней (-b+КОРЕНЬ(D))/(2*a) и (-b-КОРЕНЬ(D))/(2*a).

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

 

Задание 2. Дана таблица с итогами экзаменационной сессии.

Итоги экзаменационной сессии

№ п/п	Ф. И.О.	Математика	Эконом. Теория	Информатика
1.	Макаров С.П.	8	7	6
2.	...	...
3.

 

Составить электронную таблицу, определяющую стипендию по следующему правилу:

По рассчитанному среднему баллу за экзаменационную сессию (s) вычисляется повышающий коэффициент (k), на который затем умножается минимальная стипендия (m).

Повышающий коэффициент вычисляется по правилу:

если 4 £ s < 8, то k=1.5,

если 8 £ s < 10, то k=1.8,

если s= 10, то k=2.0

Если же s<4 или s>10, то стипендия не назначается и поэтому нужно в этом случае коэффициент k вычислять специальным образом, например, присвоить k текст «Неправильные данные»

Выполнение.

1. Составить исходную таблицу:

Итоги экзаменационной сессии

№ п/п	Ф. И.О.	Математика	Эконом. Теория	Информатика
1.	Макаров С.П.	8	7	6
2.	...	...
3.
…
Средний балл

2. Составить электронную таблицу для выплаты стипендий.

 

№ п/п	Ф.И.О.	Средний балл	Коэффициент	Стипендия
1.	Макаров С.П.	7
2	...	...
3

 

· Построить таблицу по образцу

· Графу Ф.И.О. скопировать с исходной таблицы.

· Графы средний балл и стипендия рассчитать по соответствующим формулам с использованием логических функций ЕСЛИ, И, ИЛИ, НЕ.

 

Задание 3.

По результатам сдачи сессии группой студентов (таблица Итоги экзаменационной сессии), определить

- количество сдавших сессию на "отлично" (9 и 10 баллов);

-  на "хорошо" и "отлично" (6-10 баллов);

-  количество неуспевающих (имеющих 2 балла);

- самый "сложный" предмет;

- фамилию студента, с наивысшим средним баллом.

 

Задание 4.

Пусть в ячейках A1, A2, A3 записаны три числа, задающих длины сторон треугольника.

Написать формулу:

- определения типа треугольника (равносторонний, равнобедренный, разносторонний),

- определения типа треугольника (прямоугольный, остроугольный, тупоугольный),

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

Л АБОРАТОРНАЯ РАБОТА № 6

Задание 3.

Задание 2 выполните при помощи функций ПРОСМОТР, ИНДЕКС и/или ВЫБОР.

 

Задания для самостоятельной работы.

Задание 1С.

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

Задание 2С.

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

Задание 3С.

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

Задание 4С.

Подсчитать число повторений символа ‘a’ в строке символов из ячейки A3.

Задание 5С.

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

Л АБОРАТОРНАЯ РАБОТА № 7

ПРИНЯТИЕ РЕШЕНИЙ

Задание 1. Задача о оптимальном ассортименте

Предприятие выпускает 2 вида продукции. Цена единицы 1 вида продукции – 25 000, 2 вида продукции – 50 000. Для изготовления продукции используются три вида сырья, запасы которого 37, 57,6 и 7 условных единиц. Нормы затрат каждого сырья на единицу продукции представлены в следующей таблице.

Продукция