41808

Статистические функции MS Excel 2010. Построение рядов данных

Лабораторная работа

Информатика, кибернетика и программирование

Формат записи функции: МИН число1; число2; Количество допустимых аргументов среди которых находится минимальное значение равно 255. Формат записи функции: МАКС число1; число2; Количество допустимых аргументов среди которых находится максимальное значение равно 255. Формат записи функции: СРЗНАЧчисло1; число2; Количество допустимых аргументов среди которых находится среднее значение равно 255. Формат записи функции: СЧЕТчисло1; число2; Количество допустимых аргументов среди которых находится среднее значение равно 255.

Русский

2013-10-25

495.52 KB

57 чел.

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

«Статистические функции MS Excel 2010. Построение рядов данных»

Цели работы:

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

Задание:

  1.  изучите п.1 «Учебный материал»;
  2.  выполните задания, приведенные в п.2;
  3.  ответьте на контрольные вопросы (п.3).

  1.  Учебный материал

  1.  Функции категории Статистические

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

Рис.17. Окно Мастера функций с выбранное категорией функций Статистические

Рассмотрим ряд статистических функций, встречающихся в простейших вычислениях.

  1.  Нахождение минимального значения (среди числовых значений) в списке аргументов с помощью функции МИН. Формат записи функции:

МИН (число1; число2;…)

Количество допустимых аргументов, среди которых находится минимальное значение, равно 255.

Пример. Найти наименьшее значение цены на книгу «Гарри Поттер и дары смерти» среди магазинов города. Пусть в ячейках B3:B7 внесены значения цены (рис.18). Установите курсор в ячейку В9, вызовите Мастер функций и из категории Статистические выберите функцию МИН. Укажите исходные значения и нажмите OK.

  1.  Нахождение максимального значения (среди числовых значений) в списке аргументов с помощью функции МАКС. Формат записи функции:

МАКС (число1; число2;…)

Количество допустимых аргументов, среди которых находится максимальное значение, равно 255.

Пример. Найти максимальное значение цены на книгу «Гарри Поттер и дары смерти» среди магазинов города. Пусть в ячейках B3:B7 внесены значения цены (рис.19). Установите курсор в ячейку В9, вызовите Мастер функций и из категории Статистические выберите функцию МАКС. Укажите исходные значения и нажмите OK.

Рис. 18.  Нахождение минимального значения среди аргументов

Рис. 19.  Нахождение максимального значения среди аргументов

  1.  Нахождение среднего арифметического значения с помощью функции СРЗНАЧ. Формат записи функции:

СРЗНАЧ(число1; число2;…)

Количество допустимых аргументов, среди которых находится среднее значение, равно 255.

Пример. Найти среднее значение цены на книгу «Гарри Поттер и дары смерти» в магазинах города. Пусть в ячейках B3:B7 внесены значения цены (рис. 20). Установите курсор в ячейку В9, вызовите Мастер функций и из категории Статистические выберите функцию СРЗНАЧ. Укажите исходные данные и нажмите OK.

Рис. 20.  Нахождение среднего значения среди аргументов

  1.  Подсчет количества значений в списке аргументов осуществляется с помощью функции СЧЕТ. Формат записи функции:

СЧЕТ(число1; число2;…)

Количество допустимых аргументов, среди которых находится среднее значение, равно 255.

Пример. Найти количество студентов, получающих стипендию. Пусть в ячейках B3:B7 внесены сведения о стипендии студентов группы (рис.21). Установите курсор в ячейку В9, вызовите Мастер функций и из категории Статистические выберите функцию СЧЕТ. Укажите исходные данные и нажмите OK. В ячейке В9 будет найдено искомое значение.

Рис. 21.  Нахождение количества числовых значений среди аргументов

  1.  Нахождение количества значений, удовлетворяющих заданному условию, выполняется с помощью функции СЧЕТЕСЛИ. Формат записи функции:

СЧЕТЕСЛИ(диапазон; критерий),

где диапазон – диапазон, в котором подсчитывается количество непустых ячеек;

     критерий – проверяемое условие в заданном интервале (в форме числа, выражения, текста).

Примеры записи функции:

  1.  =СЧЕТЕСЛИ(А1:А9; 85) – подсчитывает, сколько раз число 85 встречается в интервале А1:А9;
  2.  =СЧЕТЕСЛИ(А1:А9; “>85”) – подсчитывает, сколько раз в интервале А1:А9 встречаются числа, большие 85;
  3.  =СЧЕТЕСЛИ(А1:А9; “высший”) – подсчитывает, сколько раз в интервале А1:А9 встречается слово «высший»;
  4.  =СЧЕТЕСЛИ(А1:А9; “в*”) – подсчитывает, сколько раз в интервале А1:А9 встречаются слова, начинающиеся на букву «в».

Обратите внимание на то, что если в качестве критерия указываются не числовые значения, а текст или символы, то они заключаются в кавычки.

  1.  Определение ранга (номера позиции) числа в списке других чисел (т.е. порядкового номера относительно других чисел списка) выполняется с помощью функции РАНГ. РВ. Формат записи функции:

РАНГ.РВ(число; ссылка; порядок),

где числочисло, для которого определяется ранг (порядковый номер);

     ссылкамассив или ссылка на список чисел, с которым сравнивается число;

     порядокчисло (0 либо отличное от 0), определяющее способ ранжирования (в порядке убывания или возрастания).

Пример. Используем функцию РАНГ.РВ, которая присвоит номер места каждой марке автомобиля в зависимости от определенного параметра.  Пусть в ячейки В3:В8 занесены значения расхода топлива на 100 км пробега (рис.22). Наилучшим будем считать автомобиль, имеющий минимальный расход. В ячейку С3 занесем формулу

=РАНГ.РВ(B3;$B$3:$B$8;1)

и скопируем ее в оставшиеся ячейки С4:С8. Аргументы в этой формуле означают следующее: В3 – адрес ячейки, которой присваиваем в ячейке С3 номер искомого места;  $B$3:$B$8 – блок ячеек, в который занесены все известные значения расхода топлива и среди которых мы выясняем ранг. Здесь используем абсолютную адресацию ($) для того, чтобы при копировании формулы из ячейки С3 адрес участвующих в вычислении ячеек В3:В8 не изменялся. Последний аргумент функции 1 указывает на то, что сравнение результатов происходит в порядке возрастания, т.е. наилучшим результатом считаем наименьший. Если поставим 0, то лучшим результатом будет наибольший, как, например, в случае с объемом двигателя (рис. 23).

Рис. 22.  Нахождение ранга числа в порядке возрастания значений

Рис. 23.  Нахождение ранга числа в порядке убывания значений

  1.  Построение рядов данных

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

  1.  Использование маркера заполнения и перетаскивания ячеек.

Пусть необходимо построить ряд чисел от 1 до 5,5 с шагом 0,5, т.е. получить арифметическую прогрессию. Для этого:

  1.  в окне открытого листа введите данные в первую ячейку диапазона (рис. 24);
  2.  наведите курсор мыши на правый нижний угол ячейки (там располагается маркер заполнения) и, когда курсор станет тонким черным крестом, при нажатой левой кнопке мыши протащите маркер заполнения вниз по столбцу;
  3.  в конце нужного диапазона отпустите левую кнопку мыши. Оцените результат.

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

Маркер заполнения

   

Рис. 24. Использование маркера заполнения для получения арифметической прогрессии

Если после ввода первых двух значений потянуть за маркер заполнения при нажатой клавише <Ctrl>, то будет реализован принцип автозаполнения, а не получение арифметической прогрессии, и во всех ячейках получится чередование чисел 1 и 1,5 (рис.25).

 

 

Рис.25. Использование маркера заполнения для автозаполнения ячеек

  1.  Использование команды Прогрессия.
  2.  Занесите в ячейку А1 число 1;
  3.  выберите команду Прогрессия…, находящуюся в группе Редактирование вкладки Главная (рис. 26), которая позволяет заполнить ряд соответствующим образом;

Рис. 26. Команда Прогрессия для заполнения рядов данных

  1.  в появившемся диалоговом окне установите параметры, как показано на рис.27.

Рис.27. Диалоговое окно для построения рядов данных

  1.  нажмите ОК. Оцените результат.

3) Использование формул.

  1.  Занесите в ячейку А1 число 1;
  2.  по условию задачи, каждое следующее число отличается от предыдущего на 0,5, поэтому для построения ряда чисел воспользуемся формулой: =А1+0,5, которую внесем в ячейку А2 и с помощью маркера скопируем по столбцу вниз (рис. 28).

Рис.28. Построение ряда чисел с использованием формулы

4) Использование параметров автозаполнения.

  1.  Введите данные в первую ячейку диапазона;
  2.  наведите курсор мыши на правый нижний угол ячейки и, когда курсор станет тонким черным крестом, при нажатой правой кнопке мыши протащите маркер заполнения, верх или вниз по столбцу либо вправо или влево по строке;
  3.  в конце нужного диапазона отпустите правую кнопку мыши;
  4.  в контекстном меню выберите соответствующий пункт:
  5.  «Копировать ячейки» – будут копироваться и значения, и форматы исходной ячейки;
  6.  «Заполнить только форматы» – будет копироваться только формат исходной ячейки;
  7.  «Заполнить только значения» – будет копироваться только значение исходной ячейки.

  1.  Задание к лабораторной работе

Задание 1.  Для выполнения лабораторной работы введите исходные данные в соответствии с выданным вариантом. Отформатируйте таблицу по приведенным ниже параметрам.

  1.  Заголовок таблицы Применение статистических функций сделайте жирным шрифтом, размер шрифта – 12 пт. Для центрирования заголовка таблицы необходимо выделить ячейки A1:G2 и нажать на кнопку Объединить и поместить в центре , расположенную в группе Выравнивание вкладки Главная. Затем, не убирая курсора с объединенных ячеек, в контекстном меню выберите команду Формат ячеек и в открывшемся диалоговом окне выберите: вкладка Выравнивание→область Выравнивание→по горизонтали – по центру; по вертикали - по центру;
  2.  Заголовки столбцов таблицы (№ п/п; ФИО студента; Рост (см); Вес (кг)… и т.д. в зависимости от варианта задания) – по центру, полужирным шрифтом, размер шрифта – 10 пт). Возможность отображать текст внутри ячейки таблицы в несколько строк добивается следующим образом:
  3.  выделить ячейки A3:G3, формат которых требуется изменить;
  4.  в контекстном меню выберите команду Формат ячеек и в открывшемся диалоговом окне выберите: вкладка Выравнивание→область Выравнивание→по горизонтали – по центру; по вертикали - по центру;
  5.  в области Отображение установить флажок переносить по словам.
  6.  Ячейки А4:А13 заполните значениями от 1 до 10 одним из способов, описанных в п.1.2 «Построение рядов данных».
  7.  К тексту ячеек B15:B22 примените начертание курсив и сделайте перенос по словам.
  8.  Выделите ячейки A1:G13 таблицы. С помощью кнопки ГраницыВсе границы  группы Шрифт измените границы таблицы.
  9.  Символ, соответствующий степени 2 числа, можно вставить с помощью команды Символ группы Символы вкладки Вставка. Другой способ указания символа степени: написать степень числа, выделить его, нажать кнопку группы Шрифт и в появившемся диалоговом окне во вкладке Шрифт в области Видоизменение установить флажок надстрочный.

Вариант 1

Задание. Для данной группы студентов определить (искомые значения разместить в соответствующих выделенных ячейках, как показано на рис. 29. На вашем рабочем листе цвет ячеек изменять не нужно):

  1.  минимальное значение роста, веса и бега на 100 м;
  2.  максимальное значение роста, веса и бега на 100 м;
  3.  среднее значение роста, веса и бега на 100 м;
  4.  количество студентов, имеющих рост < 180 см;
  5.  количество студентов, имеющих рост > 185 см;
  6.  количество студентов, имеющих вес < 80 кг;
  7.  количество студентов, имеющих вес > 85 кг;
  8.  количество студентов, участвовавших в соревновании;
  9.  ранг студентов (порядковый номер относительно друг друга) в беге на 100 м.

Рис. 29.  Исходные данные для выполнения лабораторной работы

Вариант 2

Задание. Для данной группы продуктов определить (искомые значения разместить в соответствующих выделенных ячейках, как показано на рис.30. На вашем рабочем листе цвет ячеек изменять не нужно):

  1.  минимальное значение цен в магазинах;
  2.  максимальное значение цен в магазинах;
  3.  среднее значение цен в магазинах;
  4.  количество продуктов, название которых начинается на букву «м»;
  5.  количество продуктов, название которых начинается на букву «к»;
  6.  количество продуктов дороже 25 руб.;
  7.  количество продуктов дешевле 25 руб.;
  8.  количество продуктов, ассортимент которых обновлялся;
  9.  ранг продуктов магазина «Рублик» (порядковый номер относительно стоимости друг друга).

 

Рис. 30.  Исходные данные для выполнения лабораторной работы

Вариант 3

Задание. Для данной группы услуг определить (искомые значения разместить в соответствующих выделенных ячейках, как показано на рис.31. На вашем рабочем листе цвет ячеек изменять не нужно):

  1.  минимальное значение цен в парикмахерских;
  2.  максимальное значение цен в парикмахерских;
  3.  среднее значение цен на услуги парикмахерских;
  4.  количество услуг со стоимостью < 200 руб.;
  5.  количество услуг со стоимостью ≥200 руб.;
  6.  среднее значение стоимости стрижек в парикмахерской «Люкс»;
  7.  средняя стоимость других услуг (отличных от стрижек) парикмахерской «Люкс»;
  8.  количество скидок;
  9.  ранг стоимости услуг парикмахерской «Аванта» (порядковый номер стоимости относительно друг друга).

Рис. 31.  Исходные данные для выполнения лабораторной работы

Вариант 4

Задание. Для данной группы услуг определить (искомые значения разместить в соответствующих выделенных ячейках, как показано на рис.32. На вашем рабочем листе цвет ячеек изменять не нужно):

  1.  минимальное значение цен на услуги компаний сотовой связи;
  2.  максимальное значение цен на услуги компаний сотовой связи;
  3.  среднее значение цен на услуги компаний сотовой связи;
  4.  количество услуг со стоимостью < 2 руб.;
  5.  количество услуг со стоимостью ≥2 руб.;
  6.  среднее значение стоимости звонков оператора «МТС»;
  7.  средняя стоимость других услуг (отличных от звонков) оператора «МТС»;
  8.  количество скидок именинникам;
  9.  ранг стоимости услуг оператора «МТС» (порядковый номер стоимости относительно друг друга).

Рис. 32.  Исходные данные для выполнения лабораторной работы

Вариант 5

Задание. Для данной группы услуг определить (искомые значения разместить в соответствующих выделенных ячейках, как показано на рис.33. На вашем рабочем листе цвет ячеек изменять не нужно):

  1.  минимальное значение населения, площади территории страны, количества городов-миллионеров;
  2.  максимальное значение населения, площади территории страны, количества городов-миллионеров;
  3.  среднее значение населения, площади территории страны, количества городов-миллионеров;
  4.  количество стран с населением < 100 млн. чел.;
  5.  количество стран с населением ≥ 100 млн. чел.;
  6.  количество стран площадью территории >5 млн. км2;
  7.  количество стран площадью территории <1 млн. км2;
  8.  количество стран, берега которых омываются океанами;
  9.  ранг стран по площади территории (порядковый номер страны относительно значений площадей).

Рис. 33.  Исходные данные для выполнения лабораторной работы

Вариант 6

Задание. Для данной группы услуг определить (искомые значения разместить в соответствующих выделенных ячейках, как показано на рис.34. На вашем рабочем листе цвет ячеек изменять не нужно):

  1.  минимальное значение населения, площади территории страны, количества городов-миллионеров;
  2.  максимальное значение населения, площади территории страны, количества городов-миллионеров;
  3.  среднее значение населения, площади территории страны, количества городов-миллионеров;
  4.  количество стран с населением < 100 млн. чел.;
  5.  количество стран с населением ≥ 100 млн. чел.;
  6.  количество стран площадью территории >10 млн. км2;
  7.  количество стран площадью территории <1 млн. км2;
  8.  количество стран с океанами;
  9.  ранг стран по населению (порядковый номер страны относительно значений количества человек).

Рис. 34.  Исходные данные для выполнения лабораторной работы

Вариант 7

Задание. Для данной группы услуг определить (искомые значения разместить в соответствующих выделенных ячейках, как показано на рис.35. На вашем рабочем листе цвет ячеек изменять не нужно):

  1.  минимальное значение длины реки, площади бассейна реки и количества крупных городов, где эти реки протекают;
  2.  максимальное значение длины реки, площади бассейна реки и количества крупных городов;
  3.  среднее значение длины рек, площади бассейна и количества крупных городов;
  4.  количество рек длиной < 5000 км;
  5.  количество рек длиной 5000 км;
  6.  количество рек с площадью бассейна >1000 тыс. км²;
  7.  количество рек с площадью бассейна ≤ 1000 тыс. км²;
  8.  количество стран с указанными реками;
  9.  ранг рек по длине (порядковый номер реки относительно значений длины).

Рис. 35.  Исходные данные для выполнения лабораторной работы

  1.  Контрольные вопросы

  1.  Назовите известные вам функции из категорий Статистические и их аргументы.
  2.  Сколько аргументов могут иметь функции МИН и МАКС?
  3.  Каковы отличия функций СЧЕТ и СЧЕТЕСЛИ. Назовите аргументы этих функций.
  4.  С какой целью в функции РАНГ.РВ используется абсолютная адресация ячеек?
  5.  Самостоятельно выясните назначение и работу функций НАИМЕНЬШИЙ, НАИБОЛЬШИЙ, ТЕНДЕНЦИЯ категории Статистические, используя справку по каждой из них. Приведите примеры.


 

А также другие работы, которые могут Вас заинтересовать

64242. Формы мышления животных 27 KB
  Примером этой формы мышления может служить выбор обезьяной предметов пригодных для употребления в качестве орудия с учётом их величины плотности формы и так далее.
64243. Биологическая ограниченность интеллекта животных 30 KB
  Очень редко обезьяны пользуются на воле и орудиями. Но как и другие человекообразные обезьяны шимпанзе вполне обходятся в повседневной жизни без орудий. Причины ограниченности интеллекта животных: Ладыгина-Котс на основе экспериментальных данных пришла к выводу...
64244. Общая характеристика сравнительной психологии, ее связь с другими науками 36.5 KB
  Сравнительная психология исследует закономерности становления человеческого сознания изучает общее и различное в психической деятельности человека и животных анализирует и сравнивает социальные и биологические факторы определяющие поведение человека.
64245. Сравнительный анализ теорий, описывающих поведение животных и человека 69.5 KB
  Сущность всех таких теорий сводится к признанию у животных внетелесной детерминированной программы поведения. Наличие у животных качеств превосходящих человеческие объясняется развитием или редукцией определенных органов.
64246. Эволюция гаптических и сенсорных функций высших млекопитающих 32.5 KB
  В процессе эволюционного развития человеческая рука как орган и продукт труда: развилась из руки обезьяны и достигла высокой степени совершенства. Развитие и качественные преобразования руки занимают центральное место в антропогенезе как в физическом так и в психическом отношении.
64247. Предметная и предтрудовая деятельность ископаемых обезьян, как фактор зарождения трудовой деятельности человека 36 KB
  В ходе манипулирования предметами кроме конструктивной деятельности Ладыгина-Котс выделяла: ориентировочно-обследующую ознакомительную обрабатывающую двигательно-игровую сохранение отвергание предмета и орудийную деятельности.
64248. Качественные отличия орудий животных от орудий труда человека 31.5 KB
  В отличие от этого орудие труда непременно должно специально изготавливаться для определенных трудовых операций и предполагает знание о будущем его применении. Кроме того орудия труда изготовляются впрок то есть еще до того как возникнет возможность или необходимость их применения.
64249. Групповое поведение обезьян и проблема зарождения человеческого общества 30.5 KB
  Можно предположить что труд с самого начала был общественным так как люди с момента своего появления на земле всегда жили коллективами а обезьяны предки человека более или менее крупными стадами или семьями. Так как человеческое общество не является просто продолжением или усложнением сообщества...
64250. Язык современных обезьян и его качественные отличия от членораздельной речи 38 KB
  Истоки человеческого языка скорее всего навсегда останутся загадкой так как различие лингвистических возможностей человека и современных животных чрезвычайно велики и сравнительные исследования этих возможностей у ныне живущих видов животных дают чрезвычайно мало данных.