Файл: Методические рекомендации по выполнению лабораторно практических работ по теме Обработка табличной информации.pdf

- 24 -
С помощью функции Если можно вычислять следующие математические выражения:
???? = {
????
2
, ???? > 0
−????
2
, ???? ≤ 0
Запись в Excel:
=ЕСЛИ((x>0); (x^2); ЕСЛИ((x<=0);-(x^2)))
Следует отметить, что последнее условие можно не проверять (x<=0), так как оно является просто ложным для первого.
Тогда запись в Excel:
=ЕСЛИ((x>0); (x^2); -(x^2))
Для более сложных систем результат будет выглядеть аналогично. Например
???? = {
−????
2
, ???? < −1
????, − 1 ≤ ???? < 1
????
2
, ???? ≥ 1
Запись в Excel:
=ЕСЛИ(x<-1); -(x^2); ЕСЛИ((x<1); x; (x^2)))
С помощью функции «ЕСЛИ» можно записать вычисления для условий заданных на естественном языке. Например:
Процентная ставка по депозитам:
На сумму до 2000 – 11,5%
От 2000 до 10000 – 12,5%
От 10000 до 100000 – 12,8%
Свыше 100000 – 13%
Запись в Excel:
=ЕСЛИ((x<2000);(x*11,5%);
((х<10000);(x*12,5%);
((x<100000);(x*12,8%);(x*13%))))
Ход работы.
1. На выполнение работы отводиться 50 минут, отставание на 15 минут снижает оценку на 1 балл
2. Создать таблицу доходности реализатора. Заполнить ее случайными числами от
0 до 30000 (=СЛЧИС()*30000)
x>0?
ДА, x^2
Нет, x<=0?
Да, -х^2
x>0?
ДА, x^2
Нет, -x^2
x<-1?
ДА, -x^2
Нет, x<1?
ДА, x
НЕТ, x^2
x<2000?
ДА, x*11,5%
Нет,
x<10000?
ДА, x*12,5%
НЕТ,
x<100000?
ДА, х*12,8%
Нет, х*13%

- 25 -
ФИО
Январь
Февраль
Март
Апрель
Май
Июнь
Самарская
Семенов
Сидоров
Сковорода
Скуба
Смирнова
Соколов
Соловьев
Стрельников
Ступка
3.
Подсчитать общий доход за каждый месяц и среднее значение реализации по каждому реализатору
4.
Отдельно записать максимальное и минимальное значения реализации по месяцу.
5.
На листе 2 создать аналогичную таблицу по вычислению заработной платы с учетом следующего правила
Реализовано на сумму до 5000 - оплата 1000 от 5000 до 10000
- оплата 1000+1% от реализации от 10000 до 20000 - оплата 1000+1,5% от реализации свыше 20000
- оплата 1000+2% от реализации
6.
Вычислить среднюю зарплату по каждому реализатору
7.
Подсчитать число реализаторов со средней зарплатой от 1200 до 1300 8.
Подсчитать какой процент прибыли потрачен на выплату заработной платы в каждом месяце и всего за полгода.
9.
На листе 3 построить таблицу значений вычисления функции
???? = {
ln(????) , ???? > 1 1
2
(???? − 1)
2
, 0 < ???? ≤ 1
|????|, ???? ≤ 0
На отрезки от -1 до 3 с шагом 0,3
Результат вычисления занести в таблицу:
X
-1
-0,7
-0,4
-0,1 0,2 0,5 0,8 1,1 1,4 1,7 2
2,3 2,6 2,9 3,2
Y
1 0,7 0,4 0,1 0,32 0,125 0,02 0,095 0,336 0,531 0,693 0,833 0,956 1,065 1,163

- 26 -
ЛАБОРАТОРНАЯ РАБОТА №5.
«ПОСТРОЕНИЕ ГРАФИКОВ И ДИАГРАММ»
Цель: Научиться строить и настраивать свойства графиков и диаграмм.
Задачи:
1. Создание гистограммы, круговой диаграммы
2. Создание графиков
3. Создание поверхностей
Теория.
Примечание: самые большие изменения претерпел способ построения и
оформления диаграмм при переходе от MS Excel 2003 к MS Excel 2010.
Все графики и диаграммы строятся по таблице данных. Если заголовки строк или столбцов распознаются как текстовый формат данных, то они автоматически становятся подписями данных в диаграмме. Если подписи не распознаются автоматически, то данным присваиваются подписи Ряд1, Ряд 2, и т.д.
В MS Excel 2003 диаграмма создаётся пошагово. Последовательно вводятся:
1. Выбор типа диаграммы
2. Ряды данных, выбор по строкам или столбцам, порядок данных,
3. Определяется, что будет выводиться в области диаграммы: подписи, заголовок, таблица данных, линии сетки.
4. Выбор, куда выводить диаграмму: на тот же лист или на отдельный.
В случае необходимости на каждом этапе можно нажать готово и дальнейшее заполнение данных произойдет автоматически.
В MS Excel 2007/2010 диаграмма формируется автоматически, по выделенному диапазону данных, в случае необходимости все элементы можно перенастроить, используя линейку инструментов или пункты контекстного меню.

- 27 -
Диалог настройки рядов данных:
2010, вызывается из контекстного меню «Выбрать данные»:
2003(шаг 2):

- 28 -
Область диаграммы
Настройка для MS Excel 2003:
Свойства каждой из областей можно настраивать, выбрав пункт контекстного меню «Формат …» соответствующего элемента диаграммы. В примере точки данных залиты: узор (1-й столбец), градиентная (2-й столбец), текстура (3-й столбец). Область построения заполнено рисунком, область диаграммы – прозрачный. Линии сетки: сплошная вертикальная и пунктирная горизонтальная. Следует отметить, что в MS Excel
Образец
0 10 20 30 40 50 60 70 80 90 1
2 3
4
Номер строки
З
н
а
че
н
и
е
с
то
л
б
ц
е
в
Ряд1
Ряд2
Ряд3
Область диаграммы
Легенда
Названия осей
Оси
Область построения
Точки данных
Название диаграммы
Линии сетки

- 29 -
2010 добавлены новые элементы стилизации такие как: тень, свечение, сглаживание, формат объемной фигуры. Усовершенствовался стиль «Градиентная заливка» теперь стало возможным добавлять плавные переходы с более чем 2-мя цветами с настраиваемой пропорцией (аналогично градиенту в PhotoShop)
Настройка заливки MS Excel:
2003 2010
Настройка школы из формата оси.
2003 2010

- 30 -
Далее будет рассматриваться особенности настройки одних из основных стандартных типов диаграмм.
СОЗДАНИЕ ГИСТОГРАММ, КРУГОВЫХ ДИАГРАММ
2003 2010
Перекрытие и зазор наглядно видны в диалоге MS Excel 2003: прямоугольники данных наложены один на другой (перекрытие=30), зазор между рядами – 50 ряды стали ближе.
Значения по умолчанию отображены в диалоге MS Excel 2010.
Аналогичные настройки для круговой диаграммы: вместо перекрытия – угол поворота, вместо зазора – разделение разреза.
Примеры круговых диаграмм и гистограмм:
Стандартные настройки:
Изменённые настройки:
1 2
3 4
1 2
3 4

- 31 -
СОЗДАНИЕ ГРАФИКОВ
Разница между графиком и поточечной диаграммой заключается в том, что
«График» выбирает в качестве значений по оси Х номера строк, а в «Точечная диаграмма» выбирается в качестве значений по оси Х значения из первого столбца.

- 32 -

- 33 -
Пример точечной диаграммы
СОЗДАНИЕ ПОВЕРХНОСТЕЙ
Пример поверхности
При построении поверхностей для того чтобы изменить цвет каждого слоя поверхности достаточно дважды нажать левую кнопку мыши на подписи данных.
Подписи данных формируются автоматически по видимой области данных, но вы так же можете задать их самостоятельно аналогично тому, как задавались параметры оси.
0,00 20,00 40,00 60,00 80,00 100,00 120,00 0,00 20,00 40,00 60,00 80,00 100,00
Ряд1
Ряд2 1
2 3
4
Р1
Р3 0,00 20,00 40,00 60,00 80,00 100,00 80,00-100,00 60,00-80,00 40,00-60,00 20,00-40,00 0,00-20,00 1
2 3
4
Р1
Р2
Р3 0,00 20,00 40,00 60,00 80,00 100,00 80,00-100,00 60,00-80,00 40,00-60,00 20,00-40,00 0,00-20,00

- 34 -
Для изменения угла обзора в Ms Excel 2003 захватывается граница параллелепипеда, в котором находиться поверхность, и перемещением мыши производится вращение. Другой способ - через контекстное меню над областью диаграммы – «Объемный вид…».
В MS Excel 2010 это можно сделать только через контекстное меню над областью диаграммы – «Поворот объемной фигуры…»

- 35 -
Ход работы.
1. Отобразить все примеры, которые встречаются при объяснении теории (Пример гистограммы и круговой диаграммы, пример графика и поточечной диаграммы, пример построение поверхности)
2. Каждая диаграмма выводиться на отдельный лист.
3. Время выполнения - 40 минут, отставание на 10 минут - минус балл.
Примечание: Все диаграммы строились по одной таблице данных.

- 36 -
ЛАБОРАТОРНАЯ РАБОТА №6.
«ПОДБОР ПАРАМЕТРА, СЦЕНАРИИ»
Цель: Научиться решать задачи с помощью MS Excel
Задачи:
1. Использование средства «Подбор параметра» для поиска решений
2. Использование сценария для сохранения результатов
Теория.
Доступ к средству «Подбор параметра» для 2003 – меню Сервисподбор параметра, для 2010 – ДанныеАнализ «Что если»Подбор параметра
Покажем использования средства подбор параметра для поиска решения квадратного уравнения: y=x
2
+5x-10;
1. Построим график функции для визуального определения приблизительного решения
2. На графике видно, что есть два решения приблизительно равные
-6,3 и 1,8 3. Выбираем 2 ячейки: в первой будет введено значение х, а во второй вычисляется значения y;
4. Вводим первое приблизительное решение в ячейку соответствующую X
5. Используем средство подбор параметра для поиска точного решения
6. Сохранить результат поиска, нажав OK в следующем окне
-20
-15
-10
-5 0
5 10 15 20
-10
-8
-6
-4
-2 0
2 4
Ряд1
Какое значение искать. В примере, «0» так как мы ищем решение уравнения, но в другом случае можно ставить другое значение, например «значение будущей прибыли».

- 37 -
7. Учитывая, что поиск решения идет с точностью до 3-го знака, для более приемлемого чтения результата следует уменьшить число знаков после запятой.
8. Аналогичным образом находиться и второе решение
Когда поставленная задача имеет несколько решений, то удобно каждое из решений записать отдельным сценарием. Для этого существует средство «сценарии» в
MS Excel 2003 находиться в меню сервиссценарии…, в MS Excel 2010 – Вкладка
ДанныеАнализ «Что если»Диспетчер сценариев. Диалог выглядит в обоих офисах аналогично.
Принцип работы:
1. Создание базового сценария. a. Для этого выделите все ячейки, которые не содержат формулы b. Вызвать диспетчер сценариев нажать Добавить Дать имя например
«база». Если все сделано верно, то выделенные ячейки автоматически добавятся в поле «Изменяемые ячейки:» как показано на рисунке ниже

- 38 -
2. Аналогичным образом создаются сценарии решений сразу после вычисления
Результат добавления сценариев.

- 39 -
3. Создание отчета по сценариям. Результат работы всех сценариев удобно записать в таблицу. Для этого: a. Открыть диспетчер сценариев b. Нажать «отчет» и выбрать один из вариантов (рекомендуется
«структура»). Результат показан на рисунке ниже
Ход работы.
1. На первом листе выполнить пример показанный выше
2. На втором листе создать таблицу, где № - это номер компьютера студента
Экономические показатели
Значения ВСЕ ВЫЧИСЛЕНИЯ С № ЗАПИСЫВАЮТСЯ КАК ЧИСЛО А НЕ
КАК ФОРМУЛА. ЭТОТ СТОЛБЕЦ НАБИРАТЬ НЕ НУЖНО 
Товар
Сахар
Далее по тексту приводиться пример значений для №=5
Ед. измерения
Тонна
Цена закупки за ед.
2025 Записывается как 2000+№*5
Кол-во
105 Записывается как 100+№
Цена всей партии
212625 Вычисляется как произведение цены закупки на Количество
Накладные расходы
Транспортные расходы на ед.товара
10 Записывается как 5+№
Транспортные расходы всей партии
1050 Вычисляется как произведения Транспортных расходов за ед. на Количество
Непредвиденные расходы
250 Записывается как №*50
Страхование груза
4252,5 Вычисляется как 2% от Цены всей партии
Итого накладных расходов
5552,5 Вычисляется как сумма всех расходов (предыдущие 3 значения)
Кредит
Проценты по кредиту(а)
12,5% Записывается как (10+№/2)% (знак проценты обязателен)
Срок возврата (b)
8 Просто 8 месяцев
Необходимая сумма кредита (Х)
218177,5
Вычисляется как сумма Цены за партию и Итого накладных
расходов
Возврат кредита
Цена реализации ед. товара
2600 Записывается как 2500+№*20
Выручка от реализации
273000 Вычисляется как произведение Цены реализации на Количество
НДС
32760 Вычисляется как 12% от выручки реализации
Возврат кредита
237035,82 = X* (1+a/12)^b, где X – сумма кредита, a-%, b-срок возврата
Прибыль после выплаты кредита
3 204,18 Вычисляется как выручка от реализации – возврат кредита –
НДС