Информатика. Копирование формул. Линейные алгоритмы в MS Excel. Реализация анализа данных в MS Excel

Информатика Лекция Доцент каф. ВМ и М, к.т.н. Каменских Анна Александровна 108 к. Г, тел 239-15-64 [email protected] Microsoft Excel Рис.4.2. Диапазон ячеек D4:E7 Рис.4.3. Окно Прогрессия Вкладка Выравнивание Границы и заливка ячеек Диалоговое окно Автоформат Выбор категории и типа функции Меню функций кнопки Автосумма Определение аргументов функции ПРОИЗВЕД Определение максимального числа Суммирование с использованием функции СУММ Суммирование с использованием функции СУММЕСЛИ Использование функции ЕСЛИ Копирование формул 1 2 3 4 A 3 2 8 B 5 7 4 C =A1+B1 =A2+B2 =A3+B3 До копирования После копирования После копирования Копирование формулы с относительной ссылкой 1 A Месяц Январь B Товар 5 2 февраль 7 3 Март 4 4 Итого =B2+B3+B4 (16) 5 C Процент по месяцам = B1/B$4 (32%) = B2/B$4 (43%) = B3/B$4 (25%) (100%) D Примечание До копирования формулы После копирования После копирования Копирование формулы с абсолютной ссылкой Относительная ссылка Абсолютная ссылка строк Абсолютная ссылка столбцов Абсолютная ссылка Линейные алгоритмы в MS Excel Формулы – это математические выражения, записанные в ячейки листа MS Excel используя стандартный для него синтаксис в рамках которых выполняется вычисления. В MS Excel формулы начинаются со знака равенства (=). Например, формула вычисления разницы меду 10 и отношением 12 к 3 имеет вид «=10-12/3». Формула может содержать такие элементы, как: функция, ссылка, оператор, контакта. Функция – это стандартная формула, которая возвращает результат выполнения определенных действий над значениями, выступающими в качестве аргументов. Например, функция COS(ЧИСЛО) – возвращает косинус от заданного числа. Использование функции позволяет упростить линейное выражение в ячейках листа, что значительно уменьшает длину формул. Константа – это постоянное (не вычисляемое) значение. Например, число 2 или текст «Сумма» являются константами. Выражение или результаты вычисления заданного выражения константами не являются, контактна может быть прописана в отдельной ячейке листа MS Excel. Линейные алгоритмы в MS Excel В формулах используются простые математические операции: Сложение в MS Excel – «+», пример «=В3+143» (складывает значение ячейки B3 и число 143). Вычитание в MS Excel – «-», пример «=B3-143» (вычитает из значения ячейки B3 и число 143). Умножение в MS Excel – «*», пример «=B3*143» (умножает значение ячейки B3 на число 143). Деление в MS Excel – «/», пример «=B3/143» (делит значение ячейки B3 на число 143). Возведение в степень в MS Excel – «^»,«=B3^143» (возводит значение ячейки B3 в степень 143). Функция ABS(число) ПИ() COS(число) SIN(число) TAN(число) EXP(число) Результат выполнения функции Модуль числа или модуль от результата вычисления выражения, записанного в качестве аргумента функции Возвращает число 3,14159265358979, которое является математической константой π с точностью до 15 цифр. Возвращает косинус заданного числа или косинус от результата вычисления выражения, записанного в качестве аргумента функции. Считается, что аргументом функции является угол в радианах. Возвращает синус заданного числа или синус от результата вычисления выражения, записанного в качестве аргумента функции. Считается, что аргументом функции является угол в радианах. Возвращает тангенс заданного числа или тангенс от результата вычисления выражения, записанного в качестве аргумента функции. Считается, что аргументом функции является угол в радианах. Возвращает значение «e», возведенное в степень, которая записана в аргументе функции как число или выражение. Пример = ABS(-2) функция вернет значение 2 = ABS(10-3*2) функция вернет значение 4 = ПИ() функция вернет значение 3,14159265358979 = COS(0) функция вернет значение 1 = COS(ПИ()) функция вернет значение -1 = SIN(0) функция вернет значение 0 = SIN(ПИ()/2) функция вернет значение 1 = TAN(0) функция вернет значение 0 = TAN(ПИ()/4) функция вернет значение 1 = EXP(0) функция вернет значение 1 = EXP(1) функция вернет значение числа «е» приблизительно 2,71828 LN(число) Возвращает натуральный логарифм числа или натуральный логарифм =LN(EXP(1)) функция вернет значение 1 от результата вычисления выражения, записанного в качестве =LN(3) функция вернет значение аргумента функции. Аргумент функции должен быть положительным натурального логарифма от 3 (1,098612) вещественным числом. КОРЕНЬ(число) Возвращает положительное значение квадратного корня числа или =КОРЕНЬ(4) функция вернет значение 2 выражения, записанного в качестве аргумента функции. Аргумент =КОРЕНЬ(16+ABS(-9)) функция вернет функции должен быть больше 0. значение 5 Реализация условий в MS Excel ЕСЛИ (логическое_выражение; значение_если_истина; значение_если_ложь) – функция используется для проверки значений и проверяет, выполняется ли логическое выражение, если да, то выводит значение если истина, нет – значение если ложь. Лог_выражение – любое равенство или не равенство, значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A2>=10 – является логическим выражением; при этом если значение ячейки А2 больше или равно 10, тогда логическое выражение истинно, иначе ложно. Значение_если_истина – значение, текст или результат вычисления выражения, записанного на позиции второго аргумента функции ЕСЛИ, которое возвращается, если аргумент «лог_выражение» имеет значение ИСТИНА. Например, если в ячейке A2 записано число «2», а в ячейке B2 функция «=ЕСЛИ(A2=2;A2^2;A2-1)», тогда «лог_выражение» истинно и в ячейке B2 будет результат выражения «А2^2», которое равно 4. Значение_если_ложь – значение, текст или результат вычисления выражения, записанного на позиции третьего аргумента функции ЕСЛИ, которое возвращается, если аргумент «лог_выражение» имеет значение ЛОЖЬ. Например, если в ячейке A2 записано число «1», а в ячейке B2 функция «=ЕСЛИ(A2=2;A2^2;A2-1)», тогда «лог_выражение» ложно и в ячейке B2 будет результат выражения «А2-1», которое равно 0. Примечания: при реализации более сложного алгоритма с условиями в качестве значений аргументов «значение_если_истина» и «значение_если_ложь» может быть использовано до 64 вложенных друг в друга функций ЕСЛИ. Реализация условий в MS Excel Функция И(логическое_значение1; логическое_значение2; ...) Результат выполнения функции Возвращает значение ИСТИНА, если все логические значения истины, возвращает значение ЛОЖЬ, если хотя бы одно из логических значений не является истинным. ИЛИ(логическое_значение1; Возвращает значение логическое_значение2; ...) ИСТИНА, если хотя бы одно логическое значение истинно, Возвращает значение ЛОЖЬ, если все логические значения ложны. Примеры =И(2>5;3>2) функция вернет значение ЛОЖЬ, так как 1-е условие не выполняется; =И(2<5;3>2) функция вернет значение ИСТИНА, так как все условия истины =ИЛИ(2>5;3>2) функция вернет значение ИСТИНА, так как 2-е условие истинно; =ИЛИ(2=5;3=2) функция вернет значение ЛОЖЬ, так как все условия ЛОЖНЫ Реализация условий в MS Excel ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь) – проверяет, выполняется ли логическое выражение, если да, то выводит значение если истина, нет – значение если ложь; A=10, B=-3 ЕСЛИ(А>B; B*3; A-B) → выполнится В*3 ЕСЛИ(А+B=4; B*3; A-B) → выполнится A-B И(логич_знач1;логич_знач2;…) – возвращает значение ИСТИНА, если все логические значения являются истинными, в противном случае возвращает значение ЛОЖЬ; И(А>15; B15; B4*A) → ложь Реализация анализа данных в MS Excel СЧЁТЕСЛИ (диапазон; критерий) – подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию. Диапазон – это одна или несколько ячеек, проверяемых на выполнение заданного критерия. Ячейки могут содержать числа, имена, массивы, ссылки. Критерий – условие в форме числа, выражения, текста или ссылки на ячейку, который определяет, какие ячейки нужно подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки" или B4. Примечание: синтаксис критерия должен совпадать с синтаксисом ячеек Диапазона. СУММЕСЛИ (диапазон; критерий; диапазон_суммирования) – суммирует ячейки из диапазона суммирования, при выполнении заданныого условия, соответствующей ячейки первого диапазона. Диапазон – диапазон ячеек, который оценивается относительно выполнения заданного критерия. Ячейки могут содержать числа, имена, массивы, ссылки. Критерий – условие в форме числа, выражения или текста, определяющий, какие ячейки должны суммироваться. Например, аргумент «условие» может быть выражен как 32, "32", ">32" или "яблоки". Диапазон_суммирования – фактические ячейки, которые необходимо просуммировать, если соответствующие им ячейки в первом диапазоне отвечают заданному условию. Диаграмма в MS Excel Название диаграммы Область диаграммы Линии сетки Область диаграммы Названия осей Ряд данных Легенда Вкладка работа с диаграммами содержит три вкладки Конструктор Макет Формат Гистограмма Excel стандартная Таблица данных в Excel Гистограмма Excel отформатированная Вы бор вариантов заданий Задание №1 Рассчитать по формулам величину значений функций f1  x  и f 2  f1  , построить на двух отдельных диаграммах зависимость f 1 от x , f 2 от x . Тип диаграммы – точечная. Выполнить форматирование диаграммы. f1  sin  x   e cos 2  x   sin  x  , f 2 3  f13  f12  2 f1  , x    3;3 F1) =(SIN(A2)+EXP(1))/(COS(A2)^2-SIN(A2)) F2) =3*(B2^3+B2^2-2*B2) Задание №2 Рассчитать по формулам величину значений функций f от переменной x , используя функцию MS Excel ЕСЛИ. Построить диаграмм зависимость f от x . Тип диаграммы – точечная. Выполнить форматирование диаграммы. sin   x   cos  x  , при x 0 , x    2; 4 с шагом 0,5 f  3x  , в противном F) =ЕСЛИ(A2>=0;SIN(A2)^ПИ()-COS(A2);3*A2-ПИ()) Задание №3 При помощи средств MS Excel создать таблицы с данными, необходимыми для построения графика математической функции Построить трехлепестковую розу: x 2sin(3 ) cos( ) , y 2sin(3 ) sin( ) ,  принадлежит диапазону от 0 до 3,2 шагом 0,1. Задание №4 При помощи средств MS Excel создать таблицы с данными согласно вашему варианту. Необходимо построить диаграмму по приведенным данным. Вид диаграммы должен полностью совпасть с видом диаграммы на соответствующем варианту рисунке. № отдел а 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 Средняя заработная плата Количество сотрудников 23056,3 28003,6 15263,2 18410,1 25210,3 26345 29510,3 15697,3 18256,05 24369,1 27899,3 20005,3 22130,3 24589,6 14520,3 10 5 8 12 15 8 9 20 15 16 20 14 8 10 11 Задание №5 При помощи средств MS Excel создать таблицы с данными согласно вашему варианту. Выполнить операции с данными в соответствии с вариантом, построить диаграмму по приведенным данным. Вид смешанной диаграммы должен полностью совпасть с видом диаграммы на соответствующем варианту рисунке. Пусть даны сведения по новорожденным в Нытвенском районе Пермского края за май месяц 2016 г. Необходимо подсчитать количество мальчиков и девочек, рожденных в отчетный период, а также найти количество детей с ростом от 53 до 54 см. A 1 2 3 4 5 6 7 8 … 121 ФИО Макова Д.Д. Анисимов А.С. Пузко А.Д. Ас В.А. Харин А.В. Зюзин М.И. Илюков И.И. … Закова О.А. B Пол ребенка Жен. Муж. Жен. Жен. Муж. Муж. Муж. … Жен. C D Вес, кг 2,952 3,693 Рост, см 52,0 53,5 3,206 2,562 2,569 3,012 3,120 … 2,896 54,8 52,5 54,7 52,9 53,3 … 54,4