Технология создания и обработки числовой информации

Тема №1: MS Excel. Использование основных математических, статистических, логических и текстовых функций, функций даты и времени.

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

I.     Использование основных математических функций

1.     Включите компьютер, войдите в систему и запустите табличный процессор Microsoft Excel.

2.     Присвойте первому листу имя «Мат. ф-ции», сразу сохраните рабочую книгу в папке «Мои документы» под именем «пр1» в формате, совместимом с предыдущими версиями Excel (пр1.xls).

-        Для сохранения в формате «xls» выполните команду Файл → Сохранить как и выберите тип файла по образцу:

3.     Выполните задания:

а.     Найдите сумму квадратов первых десяти чисел;

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

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

Оформите задание по образцу:

-        первый ряд чисел получите с помощью автозаполнения;

-        квадрат числа находится по формуле: =A1^2 (для числа в ячейке A1, результат записан в ячейку A2);

-        квадраты остальных чисел получите копированием формулы из ячейки A2;

-        сумму квадратов в ячейке K2 найдите с помощью функции автосуммирования;

-        сумму квадратов в ячейке G4 найдите с помощью функции СУММКВ;

-        для нахождения квадратного корня используйте функцию КОРЕНЬ;

-        для нахождения кубического корня используйте функцию СТЕПЕНЬ.

4.     Присвойте второму листу имя «Триг. ф-ции». На этом листе протабулируйте функцию на промежутке [0°; 360°] с шагом 10°.

Оформите задание по образцу:

-        Поясните (запишите в отчёт), каким образом в формуле можно использовать не адрес ячеек, а буквенное обозначение (x).

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

II.   Использование основных статистических функций

5.     Присвойте третьему листу имя «Стат. ф-ции». На этом листе создайте табель учета рабочего времени работников предприятия (5-7 человек). В ячейках указывайте:

-        число 8, если работник вышел на работу;

-        символ «о», если работник был в отпуске;

-        символ «б», если работник болел;

-        символ «п», если работник прогулял;

-        ячейку не заполняйте, если был выходной.

Для каждого работника определите количество отработанных дней.

Оформите задание по образцу:

-        В ячейке AF5 используйте статистическую функцию СЧЁТ (=СЧЁТ(B5:AE5)), затем скопируйте её вниз.

6.     Добавьте в книгу четвёртый лист, назовите его «Мин-Макс-СрЗн». Заполните случайными числами два массива – одномерный (Массив 1) и двумерный (Массив 2). В массиве 1 должны быть числа в диапазоне от 20 до 150, а в массиве 2 – в диапазоне от 1 до 90. Найдите минимальное, максимальное и среднее значение для этих массивов.

Оформите задание по образцу:

-        Для заполнения массивов используйте функцию СЛУЧМЕЖДУ(), для минимального, максимального и среднего значений – МИН(), МАКС() и СРЗНАЧ().

7.     Создайте таблицу по образцу:

 

8.     Лист переименуйте в «Задача».

9.     При расчете среднего балла (столбец I) используйте функции «СРЗНАЧ». Число 1 - интервал ячеек, содержащих оценки студента, для которого вычисляется средний балл; Число 2 - количество знаков после запятой.

10. При расчете суммы стипендии (столбец K) с помощью Мастера функций используйте формулу:

-        если средний балл (столбец I) = 5.0, то стипендия (столбец K) = 1050;

-        если средний балл >= 4.0, то стипендия = 960;

-        если средний бал > 3.5 и < 4, то стипендия = 750;

-        иначе стипендия = 0.

11. Для получения необходимого результата используем функцию «ЕСЛИ»

Мастер функций ЕСЛИ

12. В ячейке «Примечание» ввести данные (функция «ЕСЛИ»):

-        если средний балл (столбец I) = 5.0, то Примечание (столбец L) = повышенная стипендия на 50%;

-        если средний балл >= 4.0, то Примечание = повышенная стипендия на 25%;

-        если средний бал > 3.5 и < 4, то Примечание = минимальная стипендия.

13. Выполняем аналогично п.3, вместо суммы стипендии указывается столбец Примечание, в результате формула принимает вид:

Формула «Примечания»

14. В столбце «Рейтинг» указывается место учащегося по результатам успеваемости. Данные рассчитываются с помощью Мастера функций «РАНГ». Аргументы: Число – ячейка (I3), содержащая средний балл студента, для которого считаем рейтинг, Ссылка – интервал ячеек (I3:I12): все заполненные ячейки из столбца «средний балл».

Мастер функций РАНГ

Выходные данные

15. Сохранить файл на своем компьютерном носителе.

16. Создать таблицу расчета стипендии по образцу.

Исходные данные

17. Минимальная оценка вычисляется с помощью формулы «МИН»: число 1 – диапазон ячеек, в которых нужно найти минимальное число.

18. Значение столбца Стипендия вычислить по формуле «ЕСЛИ», учитывая минимальную стипендию и возможность получения повышенной стипендии в зависимости от минимальной оценки. Стипендия начисляется следующим образом:

-        при минимальной оценке 2 (столбец Е) – нет стипендии (в ячейке должен быть «0»),

-        при минимальной оценке 3 – минимальная стипендия (ячейка С1),

-        при минимальной оценке 4 – стипендия выше в 1,25 раза (минимальная стипендия * 1,25),

-        при минимальной оценке 5 – стипендия выше в 1,5 раза (минимальная стипендия * 1,5).

Выходные данные

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

Ведомость на выдачу стипендии

20. На отдельном листе подсчитать количество студентов, которые учатся на «4» и «5», имеют оценку «3», имеют оценку «2».

Успеваемость

21. Создать круговую диаграмму «Качество успеваемости», содержащую процентное отношение этих категорий студентов друг к другу.

Успеваемость

III.              Использование основных логических функций

22. Добавьте в книгу пятый лист, назовите его «Лог.ф-ции». Создайте таблицу и решите задачу: «Оптовые покупатели получают скидку в размере 5% от суммы покупки, если сумма покупки больше 1000 грн. Определить сумму, которую заплатит каждый из трех покупателей за свою покупку».

Оформите задание по образцу:

-        Подсказка к заданию:

-        Обратите внимание на формат ячеек: используется «финансовый» формат

23. Используя функцию «Если», найдите значение y при x = -5; -2; 0; 1; 6.5:

-        Проверьте себя: такая ли у вас получилась формула: =ЕСЛИ(B8<0;КОРЕНЬ(B8^2+1); ЕСЛИ(B8=0;1;B8/3+СТЕПЕНЬ(2*B8-1;1/3)))?

IV. Использование основных текстовых функций

С помощью функций обработки текста можно выполнять различные преобразования над строковыми данными. В Microsoft Excel имеется 24 функции, относящихся к данной группе. Рассмотрим использование некоторых из них при решении различных задач, часто возникающих в практической деятельности:

СЦЕПИТЬ (текст1; текст2; …) – объединяет несколько текстовых строк в одну.

СЖПРОБЕЛЫ (текст) – удаляет из текста лишние пробелы (кроме одиночных пробелов между словами).

ЛЕВСИМВ (текст;кол_зн) – возвращает указанное количество знаков с начала строки текста.

НАЙТИ (строка;текст; поз) – возвращает номер позиции первого вхождения строки в текст, начиная с указанной позиции. Нумерация ведется относительно левого символа текста.

ДЛСТР(текст) – возвращает количество знаков в текстовой строке.

ПРАВСИМВ(текст;кол_зн) – возвращает указанное количество знаков с конца строки текста.

ПСТР (текст; поз; кол_зн) – возвращает заданное количество знаков из строки текста, начиная с указанной позиции.

ЗАМЕНИТЬ (текст; поз; кол_зн; строка) – заменяет в тексте начиная с указанной позиции заданное количество символов на другую строку.

Есть список студентов группы в следующем виде:

Нужно представить список несколько в другом виде, объединив фамилию, имя и отчество в одной ячейке:

-        Эта задача легко решается с помощью одной функции СЦЕПИТЬ. Выделив ячейку E 1, с помощью Мастера функций в категории Текстовые выбираем эту функцию и заполняем нужные поля следующим образом

-        (адреса ячеек вводятся автоматически, если щелкнуть мышью по соответствующей клетке в таблице; кавычки набирать не нужно, они также появятся автоматически после ввода пробела и перехода к следующему полю!)

-        Осталось только растянуть формулу на нужное количество строк. В столбце E список получен.

-        Скопируйте этот лист в эту же рабочую книгу:

-        Если удалить столбцы А:С, то список «разрушится», вместо нужных данных, в столбце появятся ошибочные значения #ССЫЛКА! – мы удалили ячейки, на которые были ссылки в формулах. Чтобы такого не произошло, нужно сначала получить новый список в ячейках в виде значений (а не формул). Для этого нужно выделить столбец E и скопировать его (в буфер). Затем выделить нужный столбец, диапазон или верхнюю ячейку диапазона и в меню Правка выбрать пункт Специальная вставка. В открывшемся окне выбрать радиокнопку Значения и нажать ОК. В выбранном диапазоне формулы заменятся на значения, и полученный список может «существовать» совершенно независимо от исходного.

24. .Исходный список представьте в виде списка фамилий с инициалами:

-        Выполним поставленную задачу последовательно. Сначала в ячейку D1 внесём формулу с функцией, которая выделяет первый символ из имени. Для этого с помощью Мастера функций в категории Текстовые выберем функцию ЛЕВСИМВ (рекомендуется в практической работе и дальше все функции вставлять в формулы с использованием Мастера функций).

-        После сохранения формулы в ячейке D1 скопируем её в ячейку E1 для выделения первого символа отчества.

-        А затем с помощью функции СЦЕПИТЬ объединим нужные ячейки, пробел и точки.

-        Перейти на строки 6, 7, и т.д. поможет ползунок на полосе прокрутки;

-        С помощью копирования и специальной вставки сохраним полученный список в виде значений в нужном диапазоне.

V.   Использование основных функций «Дата и время»

25. Составьте таблицу с датой рождения трёх – пяти ваших друзей по образцу:

-        Для заполнения значениями столбца Год примените функцию Год() категории «Дата и время».

-        Столбцы «Месяц» и «День» заполняются соответственно с помощью функций Месяц() и День()

-        Определите значения для столба ДЕНЬ НЕДЕЛИ, для этого:

o   введите в ячейку G2 = и щелкните по ячейке C2, нажмите Enter; затем скопируйте введенную формулу во все нижестоящие ячейки (протягиванием, с помощью маркера автозаполнения);

o   выделите диапазон ячеек, содежащий день недели;

o   выполните Формат → Ячейки → все форматы, в поле «Тип» введите ДДДД, нажмите «ОK».

26. Измените, представление данных в столбце «Месяц» так, чтобы отображалось название месяца.

-        Используйте текстовую функцию Текст(): =ТЕКСТ(C2;"ММММ")

 

СПИСОК ЛИТЕРАТУРЫ

1.         Боброва, Е. И. Инфомрационно-коммуникационные технологии в деятельности библиотеки вуза / Е. И. Боброва. – Москва : Директ-Медиа, 2013. – 156 с.

2.         Каймин, В. А. Информатика : учебник / В. А. Каймин. – Москва : Инфра-М, 2006. – 285 с.

3.         Безручко, В. Т. Компьютерный практикум по курсу «Информатика» : учеб. пособие / В. Т. Безручко. – Москва : Инфра-М, 2008. – 386 с.

4.         Федотова, Е. Л. Информатика : курс лекций : учеб. пособие / Е. Л. Федотова, А. А. Федотов. – Москва : Форум : Инфра-М, 2011. – 480 с.

5.         Каймин, В. А. Информатика : учебник / В. А. Каймин. – Москва : Инфра-М, 2010. – 285 с.

6.         Информатика : учеб. пособие / под ред. Б. Е. Одинцова, А. Н. Романова. – Москва : Вузовский учебник : Инфра-М, 2012. – 410 с.

7.         Сергеева, И. И. Информатика : учебник / И. И. Сергеева, А. А. Музалевская, Н. В. Тарасова. – Москва : Форум : Инфра-М, 2011. – 384 с

8.         Безручко, В. Т. Компьютерный практикум по курсу «Информатика» : учеб. пособие / В. Т. Безручко. – Москва : Инфра-М, 2012. – 386 с.

9.         Каймин, В. А. Информатика : учебник / В. А. Каймин. – Москва : Инфра-М, 2009. – 285 с.

10.      Безручко, В. Т. Информатика (курс лекций) : учеб. пособие / В. Т. Безручко. – Москва : Инфра-М, 2012. – 432 с.

11.      Шапорев, С. Д. Информатика. Теоретический курс и практические занятия : учеб. пособие / С. Д. Шапорев. – Санкт-Петербург : БХВ-Петербург, 2008. – 472 с.

12.      Сергеева, И. И. Информатика : учебник / И. И. Сергеева, А. А. Музалевская, Н. В. Тарасова. – Москва : Форум : Инфра-М, 2013. – 384 с

13.      Яшин, В. Н. Информатика : программные средства персонального компьютера : учеб. пособие / В. Н. Яшин. – Москва : Инфра-М, 2014. – 236 с.

14.      Гуриков, С. Р. Информатика : учебник / С. Р. Гуриков. – Москва : Инфра-М, 2014. – 464 с.

15.      Безручко, В. Т. Информатика (курс лекций) : учеб. пособие / В. Т. Безручко. – Москва : Инфра-М, 2014. – 432 с.

16.      Плотникова, Н. Г. Информатика и информационно-коммуникативные технологии (ИКТ) : учеб. пособие / Н. Г. Плотникова. – Москва : РИОР : Инфра-М, 2014. – 124 с.

17.      Сергеева, И. И. Информатика : учебник / И. И. Сергеева, А. А. Музалевская, Н. В. Тарасова. – Москва : Форум : Инфра-М, 2014. – 384 с.

18.      Жаров, М. В. Основы информатики : учеб. пособие / М. В. Жаров, А. Р. Палтиевич, А. В. Соколов. – Москва : Форум, 2008. – 288 с.

19.      Скачано с www.znanio.ru