Табличный процессор MS Excel Шевченко И Ю

Табличный процессор MS Excel © Шевченко И. Ю. 2010 г.

Работа в MS Excel 2003, 2007, 2010 (план лекций) 1. Основы. 2. Расчеты в MS Excel. Мастер функций. Математические функции. 3. Статистические функции. 4. Диаграммы. 5. Оптимизация и планирование. 6. Моделирование и прогнозирование. 2

3 MS Excel 2003, 2007, 2010 Тема 2. Расчеты в MS Excel. Мастер функций.

Расчет удельного веса Удельный вес – доля одного показателя в сумме всех (отношение одного показателя к сумме всех), выраженная в %. Расчет удельного веса можно выразить в числовом и %-ном формате. При этом меняется вид формулы, а в ячейках обязательно нужно установить соответствующий формат данных. Обозначим через Пi – значение одного i-того показателя, i – количество показателей. Расчет уд. веса в числовом формате выполняется по формуле: Уд. вес (Пi) = (Пi *100) / Σ Пi = (Пi / Σ Пi )*100. Расчет уд. веса в % формате выполняется по формуле: Уд. вес % (Пi) = Пi / Σ Пi. Т. к. в % формате число умножается на 100 и рядом выводится знак %, то в формуле отсутствует (*100). 4

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

Чтобы проверить правильность расчета удельного веса, необходимо сложить рассчитанные удельные веса всех показателей с помощью Автосуммирования. Сумма всех удельных весов должна быть равной 100! Нахождение % от числа: чтобы найти % от числа, необходимо число умножить на сам % (или сотую долю процента). РК (районный коэффициент)= Оклад*15% РК = Оклад*0, 15

7 Функции. Мастер функций. ввод в строке редактирования изменение диапазона ввод в ячейке диапазон мастер функций ячейка ! Можно мышкой!

Мастер функций позволяет быстро и удобно выполнять всевозможные расчеты с использованием встроенных функций. Мастер функций (МФ) содержит более (или около) 400 встроенных функций, условно разделенных на несколько категорий: • • Математические (тригонометрические); Статистические; Финансовые; Логические; Дата и время; Ссылки и массивы; Работа с базой данных; Текстовые и др. . 8

Все функции имеют одинаковый формат записи, который включает: • имя функции и • находящийся в круглых скобках перечень аргументов. СРЗНАЧ (А 2 : А 15) СТЕПЕНЬ (В 4; 3) Функция представляет собой программу с уникальным именем, для которой пользователь должен задать конкретные значения аргументов. Выбирая функцию, мы обращаемся к подпрограмме с этим именем, где записан алгоритм расчета по этой функции! Имя подпрограмм пишется заглавными буквами! 9

• • • Аргументами функции могут быть: Числа; Ссылки на ячейки и диапазоны ячеек; Имена ячеек; Текст; Логические выражения; Другие функции МФ. Функции, которые используются в качестве аргументов в других функциях, называются вложенными: КОРЕНЬ (SIN(В 2)). Функции могут вставляться в отдельную ячейку либо в расчетную формулу. Для расчета Y по формуле нужно использовать функцию COS: Y = COS(x)3+5*x 10

11 Открыть Мастер функций можно через : 1. 2. Меню Вставка Функция; Значок fx в строке формул (или на панели инструментов). 3. В Excel 20072010 Мастер функций состоит из двух шагов: • На 1 -м шаге - списки категорий и функций (выбирается категория, затем нужная функция из списка). • На 2 -м шаге – устанавливаются аргументы выбранной функции.

Окно Мастера функций 1 Шаг Мастера функций 12

2 Шаг Мастера функций 13 Получить информацию о функции и её аргументах можно на любом шаге МФ в Справке об этой функции!

Категория Математические COS (0, 5); SIN (A 1); TAN ($A$1) – tg (x); ATAN (x) – arctg (x); ABS (x) – модуль числа; EXP (x) – ex ; LOG 10 (x) – lg (x) ; LN (x) – ln (x); ПИ() – число π СУММ (А 1: А 10) – сумма значений ячеек диапазона А 1: А 10; СУММ (А 1; А 5) – сумма значений ячеек А 1 и А 5; КОРЕНЬ (В 2) – корень числа; СТЕПЕНЬ (число; степень) – возведение чисел в любую степень; СТЕПЕНЬ (В 4; 1/3) – (В 4)1/3 ; СТЕПЕНЬ (С 4; -0, 25) – (С 4)-0, 25 14

СУММЕСЛИ (диапазон; условие) – суммирует значения ячеек в диапазоне, отвечающих определенному условию или критерию. СУММЕСЛИ (D 2: D 6; “>=1000”) D 1 Доплата, р 2 3 4 5 6 7 2000 1000 500 900 1500 4500

Функции даты и времени • СЕГОДНЯ () – устанавливает текущую дату, не имеет аргументов; • ДНЕЙ 360 (начальная дата; конечная дата) – определяет количество дней между двумя датами, т. е. разницу в днях, исходя из 360 дней в году. Например, можно посчитать стаж сотрудника, если найти разницу между датой приема на работу и текущей, разделив на количество дней в году: ДНЕЙ 360 (01. 90; 25. 01. 11)/360 Возраст человека, если найти разницу между датой рождения и текущей, разделив на количество дней в году, округлив результат до целых: ДНЕЙ 360 (01. 70; СЕГОДНЯ () )/360

Логические функции ЕСЛИ (условие; выражение 1; выражение 2) – выбор из двух вариантов: если условие ИСТИНО, то используется выражение 1, а иначе – выражение 2. Результат=ЕСЛИ (А 2>=70; “сдал”; “не сдал”). Оценка =ЕСЛИ (В 2= “сдал”; ЕСЛИ (А 2>80; 5; 4); “-”). Сдача экзаменов. 17

И (условие 1; условие 2; условие 3); – одновременное выполнение всех условий 18 ЕСЛИ (И (условие 1; условие 2); выражение 1; выражение 2) – если условие 1 и условие 2 ИСТИНО, то используется выражение 1, а иначе - выражение 2. Принят=ЕСЛИ (И (B 2>1994; C 4>175); принят; -). Набор детей в спортивную школу Премия=ЕСЛИ ( И (A 5>3000; A 5<5000); А 5*2; А 5).

ИЛИ (условие 1; условие 2; условие 3) – выполнение 19 хотя бы одного из условий: или 1 -го, или 2 -го, или 3 -го ЕСЛИ (ИЛИ (условие 1; условие 2); выражение 1; выражение 2) - если ИСТИНО хотя бы одно из условий (условие 1 или условие 2), то используется выражение 1, а иначе - выражение 2. Принят=ЕСЛИ ( ИЛИ (В 2=100; С 2=100; В 2+С 2>=180); да; -). Поступление абитуриентов в вуз

B 2>=10 НЕ – обратное условие, НЕ (B 2<10) ? Выражением в логических функциях могут быть: • Числа, • Ссылки на ячейки, • Арифметические выражения, • Текст, • Другие функции Мастера функций.

Статистические функции СРЗНАЧ – среднее арифметическое для диапазона или нескольких ячеек; МИН – минимальное значение в диапазоне ячеек; МАКС – максимальное значение в диапазоне ячеек; СЧЕТЕСЛИ (диапазон; условие) – счет количества ячеек в диапазоне, отвечающих определенному условию. (Смотри пример для функции СУММЕСЛИ) СЧЕТЕСЛИ (D 2: D 6; “>=1000”). Результат – 3 ячейки. 21

Финансовые функции. Финансовые расчеты. По типу решаемых задач все финансовые функции можно разделить на следующие условные группы: • для анализа потоков платежей и инвестиционных проектов; • для анализа ценных бумаг; • для расчета амортизационных платежей; • вспомогательные функции. Финансовые функции каждой группы имеют набор обязательных и необязательных аргументов (которые можно не устанавливать или они принимают значения по умолчанию). Информацию по функции (её аргументам, расчетной формуле) можно получить в справке на 1 -м или 2 -м Шаге Мастера функций. 22

Финансовые функции для расчетов по кредитам и займам. При проведении кредитно-депозитных операций, долгосрочной аренде и т. п. возникают потоки платежей (обыкновенный аннуитет), при которых выплаты (поступления) денежных средств осуществляются равными суммами через одинаковые интервалы времени. Количественный анализ таких операций сводится с исчислению основных характеристик, например, с помощью функций: • будущей величины платежа (БС или БЗ); • текущей величины платежа (ПС или НЗ); • величины отдельного платежа (ПЛТ или ППЛАТ); • нормы доходности в виде процентной ставки (СТАВКА или НОРМА); • числа периодов проведения операции (КПЕР). 23

БС (ставка; кпер; плт; пс; тип) - предназначена для расчёта будущей суммы вклада или займа на основе постоянной процентной ставки и периодических постоянных платежей. ТИП – константа, может принимать значение 0 или 1, и обозначающая, когда должна производиться выплата: 0 – в конце периода, 1 – в начале периода. По умолчанию ТИП= 0. Например: нужно вложить 1000 руб. под 7% годовых, ежемесячно будет вкладываться по 500 руб. в начале месяца в течение 5 лет. Определить накопленную сумму вклада. БС (7% /12 ; 12*5; -500; -1000; 1). БС = 37423 руб. 7% /12 - т. к. вклад пополняется ежемесячно, то получаем % ставку на месяц; СТАВКА - процентная ставка на один расчётный период; 12*5 - (12 мес. *5 лет) число периодов проведения операции; КПЕР может быть выражено в месяцах, годах, кварталах, полугодиях. 24

25 БС (ставка; кпер; плт; пс; тип) БС (7% /12 ; 12*5; -500; -1000; 1) ПЛТ и ПС со знаком «-» , который показывает, что эти суммы отдаются, т. е. вкладываются в банк.

26 Работа в Excel 2007, 2010 Тема 3. Мастер диаграмм. Построение Диаграмм

Общий подход Excel позволяет строить диаграммы различных типов на основе: данных в ячейках ЭТ и результатам расчетов. Диаграмма - представление данных в графическом виде. Мастер диаграмм состоит из 4 -х шагов или этапов. На каждом шаге (этапе) устанавливаются соответствующие параметры диаграмм. Сначала выделить все нужные данные, для выделения несвязанных диапазонов используем +Ctrl. А потом: 27

Порядок построения диаграмм: 1. необходимо правильно выделить данные из таблиц: по столбцам и по строкам; 2. вызвать Мастер диаграмм на панели инструментов или ч/з меню Вставка; 3. построение диаграммы по шагам; 4. редактирование диаграммы по элементам. Построение диаграммы по шагам Мастера диаграмм: а) тип диаграммы б) исходные данные в) параметры диаграммы г) размещение диаграммы

Основные типы диаграмм Гистограмма (столбчатая диаграмма): сравнение значений одного или нескольких рядов данных График: показывает изменение процесса во времени (равномерные отсчеты) 29 Круговая: доли в сумме Точечная: связь между парами значений (график функции)

30 Элементы диаграмм название диаграммы сетка подписи данных легенда ряды данных ось названия осей

Настройка диаграммы и ее элементов Конструктор: общие свойства Макет: настройка свойств отдельных элементов Формат: оформление отдельных элементов 31

32 Графики функций Задача: построить график функции для . Таблица значений функции: шаг 0, 5 ЛКМ ! ЛКМ Что зависит от шага? Точность построения графика функции!

Графики функций Вставка диаграммы «Точечная» : выделить данные результат: 33

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

Третий шаг. Позволяет установить параметры диаграммы в зависимости от типа диаграммы, количество вкладочек – разное. Для типа Гистограмма - 6 вкладочек, для Круговой – 3. Заголовки позволяют написать заголовки диаграммы и название оси. Линии сетки – оси линии по всем осям. Легенда – это прямоугольная область, в которой представлены обозначения и названия рядов. Можно убрать и добавить легенду по диаграммам, изменить ее размещение. Подписи данных на диаграмме. - значения – числовые данные на диаграмме, - категории – название категорий.

5) Таблицы данных – данные, которые были выделены для построения диаграмм. На четвертом шаге выбирается вариант размещения готовой диаграммы: - на имеющемся (Листе) рядом с таблицей; - на отдельном (Листе). И нажать - Готово Готовую диаграмму необходимо редактировать по элементам.