Microsoft Excel. От основ к задачам обработки и представления данных (стр. 10 )

Выбор одного из значений приводит к отображению только тех строк, которые в данном столбце содержат выбранное значение.

8.3.10. Составление программы

В программе используется типовой алгоритм поиска максимума, минимума. Блок-схема алгоритма поиска максимума имеет вид (рис. 86).

Отличием программы от этой блок-схемы является то, что цикл начинается с первого элемента (с первой ячейки), так как цикл предназначен для вычисления еще нескольких параметров. Программа достаточно простая и Вы с ней легко разберетесь. В главном цикле перебираются все ячейки с данными и накапливаются суммы температур (для вычисления среднего) за каждый день (первая колонка StDay(i, 1)). Кроме того, вычисляются максимальные (вторая колонка StDay(i, 2)) и минимальные (третья колонка StDay(i, 3)) значения за каждый день. В цикле использован интересный прием. Как узнать какой день обрабатывается? Для этого необходим счетчик – переменная Count, которая содержит номер дня, к которому относится текущее значение переменной Obj (текущая ячейка). Как только день обработан, переменная Count наращивает значение на единицу, а если это был последний день (т. е. Count стала равна 11 после добавления единицы), то переменную Count снова делают равной 1.

Рассмотрим участок, где формируется список дней.

For i = 1 To 10

' Для получения среднего необходимо разделить сумму на

' число лет наблюдения

' Заносим данные в список

ListBox1.AddItem "День " & i & ": " ' Первая колонка с названием дня

Для добавления новой строки в список используется метод AddItem (он позволяет занести данные в первую колонку списка, если их несколько). Объект ListBox1 имеет свойство ColumnCount равное четырем, а свойство ColumnWidth равное 49.95 пт; 35 пт; 35 пт; 35 пт (при задании свойства можно просто перечислять цифры через точку с запятой без букв "пт" – пункт). Первое свойство задает число колонок, а второе свойство их ширину (поэтому чисел четыре). Далее с помощью свойства List задаются значения еще трех колонок первой строки.

' Вторая колонка со средним значением

ListBox1.List(i – 1, 1) = Format(StDay(i, 1) / 30, "0.00")

Обратите внимание, что счет строк в объекте класса «Список» начинается с нуля, поэтому указывается номер строки как i –1. Следующий параметр отражает номер колонки – 1 (счет тоже начинается с нуля).

Sum = Sum + StDay(i, 1) / 30 ' накапливаем сумму средних значений

' Третья колонка с максимальным значением

ListBox1.List(i – 1, 2) = StDay(i, 2)

' Ищем максимальное значение

If Max < StDay(i, 2) Then

Max = StDay(i, 2)

End If

' Четвертая колонка с минимальным значением

ListBox1.List(i – 1, 3) = StDay(i, 3)

' Ищем минимальное значение

If Min > StDay(i, 3) Then

Min = StDay(i, 3)

End If

Next i

8.4. Задача на самостоятельное решение

Имеются данные о продажах определенных типов автомобилей, производимых разными странами, за первое полугодие по месяцам.

Требуется:

·  Рассчитать показатели, характеризующие продажу автомобилей (количество проданных автомобилей каждого типа за 1, 2 кварталы и за полугодие в целом, а также сумму продаж за каждый месяц, квартал и полугодие) (см. таблицу, подлежащие заполнению клетки выделены желтым фоном, итоги – в последней строке таблицы). Отформатируйте клетки с объемом выручки в числовой формат с 2-мя знаками после запятой.

·  Выделите контрастным форматированием (условное форматирование) автомашины производства России.

·  Установите автофильтр для выделения сведений об автомашинах отдельных марок.

·  Ниже таблицы разместите результаты расчета статистики. Количество проданных автомобилей по странам-производителям, наиболее популярные автомобили (первые пять) – их тип и число.

·  Постройте диаграмму, отражающую продажи автомобилей по странам.

·  Определите процент автомобилей "Жигули" (всех моделей) в общем количестве проданных автомобилей за первый квартал, и в количестве проданных автомобилей по России.

·  Составить программу отображения информации о продаже автомобилей по странам (количество и объем продаж в рублях), информации о наиболее продаваемых автомобилях (первые пять) и выбора типа автомобиля в зависимости от имеющейся суммы. Пользователю предоставить возможность изменения суммы (с помощью объекта класса «Счетчик») и выводить список автомобилей, которые можно купить на эту сумму (название и цена). Подсказка: метод Clear очищает объект класса «Список» (удаляет из него все строчки).

8.5. Самоконтроль

В результате изучения главы 8 Вы должны освоить:

1.  Принципы и методы обработки информации (сортировку данных, поиск данных, вычисление среднего значения, максимального значения, минимального значения, вычисление процентов, выделение категорий данных);

2.  Работу с функциями Excel позволяющими осуществлять обработку информации;

3.  Работу с массивами в Excel;

4.  Построение графиков отображающих информацию в наиболее наглядном виде;

5.  Создание простейших программ по выводу статистической информации.

Вы должны знать:

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

2.  Процедуру создания графиков, их типы и назначение;

3.  Процесс создания программ, отображающих информацию, используемые интерфейсные объекты, методы, свойства, события.

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

1.  Что такое примечание?

2.  Как создать примечание?

3.  Как изменить примечание?

4.  Как удалить примечание?

5.  Можно ли использовать функции форматирования (жирный шрифт, размер, цвет) по отношению к примечанию?

6.  Как округлить число с помощью функции Excel?

7.  Как установить процентный формат ячеек в Excel?

8.  Как вычислить среднее значение с помощью функции Excel?

9.  Можно ли вычислить среднее значение, не используя функцию СРЗНАЧ?

10.  Что находит функция СЧЁТЕСЛИ? Приведите пример применения функции.

11.  Как подсчитать число строк и столбцов в диапазоне?

12.  Как выделяют категорию данных? Опишите процесс выделения необходимых данных.

13.  Приведите несколько примеров применения функции ЕСЛИ?

14.  Может ли функция ЕСЛИ быть вложенной в другую функцию?

15.  Как определить стипендию студента?

16.  Что такое абсолютная ссылка?

17.  Что такое относительная ссылка?

18.  Что такое диапазон ячеек?

19.  Что вычисляет функция СУММЕСЛИ? Приведите пример.

20.  Как вычисляются проценты?

21.  Всегда ли сумма процентов отдельных категорий будет равна 100%? Как можно избежать этого?

22.  Может ли сумма процентов при округлении начальных значений быть больше 100%.

23.  Как найти максимальное значение в диапазоне ячеек?

24.  Что вычисляет функция ПОИСКПОЗ?

25.  Какие типы сопоставления есть у функции ПОИСКПОЗ?

26.  Что выполняет функция ТЕКСТ?

27.  Объясните, как получить название предмета с максимальным средним баллом.

28.  Как выполнить условное форматирование?

29.  Что такое условное форматирование?

30.  Чем отличается критерий в условном форматировании: «по значению» от критерия «по формуле»?

31.  Как выделить категорию двоечников с помощью условного форматирования?

32.  Как выделить категорию хорошистов с помощью условного форматирования?

33.  Какие классы диаграмм Вы знаете?

34.  Какие типы стандартных диаграмм Вам известны?

35.  Перечислите несколько нестандартных типов диаграмм (не менее пяти).

36.  Опишите шаги мастера по созданию диаграмм.

37.  Опишите назначение элемента управления «Кнопка» и его свойства.

38.  Опишите назначение объекта класса «Рамка» и его свойства.

39.  Опишите назначение объекта класса «Счетчик» и его свойства.

40.  Опишите назначение объекта класса UserForm и его свойства.

41.  Что выполняет метод Show? Для чего он используется в программе?

42.  Что выполняет метод AddItem? Для чего он используется в программе?

43.  Когда происходит событие «Click»? У какого объекта оно может возникать? Как событие обрабатывается в программе?

44.  Когда происходит событие «Initialize»? У какого объекта оно может возникать? Как событие обрабатывается в программе?

45.  Что вычисляет функция Format? Для чего она применяется в программе?

46.  Что вычисляет функция Str? Для чего она применяется в программе?

47.  Что вычисляет функция Trim? Для чего она применяется в программе?

48.  Расскажите алгоритм подсчета числа двоечников.

49.  Расскажите алгоритм подсчета числа троечников.

50.  Расскажите алгоритм подсчета числа хорошистов.

51.  Расскажите алгоритм подсчета числа отличников.

52.  Расскажите алгоритм подсчета числа двоек.

53.  Расскажите алгоритм подсчета числа троек.

54.  Расскажите алгоритм подсчета числа четверок.

55.  Расскажите алгоритм подсчета числа пятерок.

56.  Расскажите алгоритм создания списка студентов, получающих стипендию.

57.  Как произвести сортировку данных?

58.  Что такое порядок сортировки? Какие Вам известны стандартные порядки сортировки.

59.  Что такое пользовательский порядок сортировки? Как его создать?

60.  Для чего используется пользовательский порядок сортировки?

61.  Что определяет понятие массива в Excel?

62.  Что такое формула массива? Для чего она используется? Приведите пример.

63.  Что вычисляет функция НАИБОЛЬШИЙ? Какие у нее есть параметры? Приведите пример.

64.  Что вычисляет функция НАИМЕНЬШИЙ? Какие у нее есть параметры? Приведите пример.

65.  Расскажите процедуру автозаполнения.

66.  Вспомните, как и когда можно использовать заполнение ячеек с помощью команд Excel.

67.  Как работает функция ДВССЫЛ? Что она вычисляет? Приведите пример.

68.  Что такое автофильтр? Объясните его назначение.

69.  Расскажите и объясните алгоритм поиска минимума.

70.  Расскажите и объясните алгоритм поиска максимума.

71.  Расскажите и объясните алгоритм поиска среднего значения.

72.  Можно ли найти максимальный и минимальный элементы в одном цикле и одном условии. Обоснуйте свой ответ.

73.  Может ли максимальный элемент равняться минимальному элементу. Обоснуйте свой ответ.

74.  Объясните, что делает метод Clear.

75.  Что возвращает функция ЛЕВСИМВ. Зачем она нужна, приведите пример.

76.  Что возвращает функция ИНДЕКС. Зачем она нужна, приведите пример.

77.  Какие подзадачи решают при обработке информации?

Приложение

Таблица «Сессия»

Файл-заготовка содержит таблицу Excel c формулами (рис. 87).

Рис. 87. Первая часть таблицы «Сессия».

В ячейках AA18-AM317 находятся простые числа – оценки по предметам (от 2 до 5), расположенные случайным образом. В ячейках AB17-AM17 находятся краткие названия предметов («П1», …, «П12») с примечаниями – полными названиями. В ячейках AK11 и AT11 хранятся размеры стипендий для отличников и успевающих студентов соответственно.

Формулы в ячейках (скопировать в столбце до 317 строки):

AN18:=ОКРУГЛ(СРЗНАЧ(AB18:AM18),2)

AO18:=СЧЁТЕСЛИ(AB18:AM18,2)

AP18:=СЧЁТЕСЛИ(AB18:AM18,3)

AQ18:=СЧЁТЕСЛИ(AB18:AM18,4)

AR18:=СЧЁТЕСЛИ(AB18:AM18,5)

AS18:=ЕСЛИ(AO18<>0,"двоечник",ЕСЛИ(AR18=12,"отличник",""))

AT18:=ЕСЛИ(AS18="двоечник","",ЕСЛИ(AS18="отличник",$AK$11, $AT$11))

Вторая часть таблицы содержит результаты обработки таблицы по студентам (рис. 88).

Рис. 88. Вторая часть таблицы «Сессия».

Формулы в ячейках:

AW17:=ЧИСЛСТОЛБ(AB18:AM18)*ЧСТРОК(AB18:AB317)

AW18:=СЧЁТЕСЛИ($AB$18:$AM$317,5)

AW19:=СЧЁТЕСЛИ($AB$18:$AM$317,4)

AW20:=СЧЁТЕСЛИ($AB$18:$AM$317,3)

AW21:=СЧЁТЕСЛИ($AB$18:$AM$317,2)

AW23:=ЧСТРОК(AM18:AM317)

AW25:=СЧЁТЕСЛИ($AS$18:$AS$317,"отличник")

AW26:=СЧЁТЕСЛИ($AS$18:$AS$317,"двоечник")

AW28:=AW25/$AW$23

AW29:=AW26/$AW$23

AW31:=СУММ(AT18:AT317)

AW33:=СУММЕСЛИ($AT$18:$AT$317,AT11)

AW34:=СУММЕСЛИ($AT$18:$AT$317,AK11)

Третья часть таблицы содержит результаты обработки таблицы по предметам (рис. 89).

Рис. 89. Третья часть таблицы «Сессия».

Формулы в ячейках:

AW38:=СУММ(AB$17:AB$318)/ЧСТРОК($AB$18:$AB$317) – скопировать в ячейки AW39:AW49

AY38:=МАКС(AW38:AW49)

AY40:="П"&ТЕКСТ(ПОИСКПОЗ(МАКС(AW38:AW49),AW38:AW49,0), "0")

Таблица «Температура»

Файл-заготовка содержит таблицу Excel c формулами (рис. 90).

Рис. 90. Первая часть таблицы «Температура»

В ячейках AA21, AA31, AA41, …, AA311 находятся года (числа) 1960, 1961, 1962, …, 1989. В ячейках AB21, …, AB320 периодически повторяются дни 1, 2, …, 10. В ячейках AC21-AC320 значения температуры (дробные числа) от 10 до 40. В ячейках AD21-AD320 отсортированные по возрастанию, а в AE21-AE320 по убыванию значения температуры.

Формулы в ячейках:

AH20:=ЧСТРОК(AC21:AC320)

AH21:=МАКС(AC21:AC320)

AH22:=МИН(AC21:AC320)

AH23:=ОКРУГЛ(СРЗНАЧ(AC21:AC320),2)

AH27:=ОКРУГЛ(СРЗНАЧ(ЕСЛИ($AB$21:$AB$320=AG27,$AC$21: $AC$320)),2) – скопировать в столбец AH28-AH36

AK21:=НАИБОЛЬШИЙ($AC$21:$AC$320,AJ21) – скопировать в AK22-AK25

AK28:=НАИМЕНЬШИЙ($AC$21:$AC$320,AJ28) – скопировать в AK29-AK32

Результаты обработки значений температуры показаны на рис. 91.

Рис. 91. Вторая часть таблицы «Температура».

Формулы в ячейках:

AH39:=СЧЁТЕСЛИ($AC$21:$AC$320,AG39) – скопировать в ячейки AH40-AH44

AH47:=AH39

AH48:=AH40-AH39

AH49:=AH41-AH40

AH50:=AH42-AH41

AH51:=AH43-AH42

AH52:=AH44

AM54:=СРЗНАЧ(AM23:AM52)

AM55:=МАКС(AM24:AM52)

AM56:=МИН(AM23:AM52)

AO54:=СРЗНАЧ(AO23:AO52)

AO55:=МАКС(AO24:AO52)

AO56:=МИН(AO23:AO52)

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

AM23:=ДВССЫЛ(AN23)

Эта формула скопирована в ячейки AM24-AM52. Данные для этой формулы берутся из соседнего столбца (AN23-AN52), в который они заносятся вручную. Там находятся названия ячеек ac21, ac31, …, ac311 в виде текста.

Для других дней формулы идентичны.

Таблица «Автомашины»

Вид таблицы показан на рис. 92, 93, 94.

Рис. 92. Первая часть таблицы «Автомашины».

Рис. 93. Вторая часть таблицы «Автомашины».

Рис. 94. Третья часть таблицы «Автомашины».

Примечания в ячейках:

AC45: Воспользуйтесь формулой массива {=сумм(если…)} для подсчета объемов продаж

AM51: Используйте функцию НАИБОЛЬШИЙ( )

AN51: Используйте функции ПОИСКПОЗ( ) и ИНДЕКС(диапазон; индекс), вторая функция возвращает значение ячейки из указанного диапазона ячеек, по индексу (находится с помощью первой функции).

AT45: Для выделения "Жигулей" воспользуйтесь функцией ЛЕВСИМВ(<адрес>). Эта функция возвращает левый символ строки, содержащийся в ячейке по указанному адресу (аналогично функции Left(S$,1)). Применяйте формулы массива. Вычисление процента ведите относительно ячейки AN40.

AU45: Введите формулу подсчета числа российских машин проданных за 1-ое полугодие.

Формула в ячейке AD57:=СУММ(AC45:AC52).

Cписок использованной литературы

1.  Информатика. Базовый курс / и др. – СПб: Питер, 2000. – 640 с.

2.  Якубайтис сети и системы. Справочная книга. – М.: Финансы и статистика, 1996. – 368 с.

3.  Додж М., Эффективная работа с Microsoft Excel 2000. Пер. с англ. – СПб: Питер, 2000. – 1056 с.

4.  Язык компьютера. / Под ред. . Пер. с англ. – М.: Мир, 1989. – 240 с.

5.  и др. Вычислительная техника и программирование: Курсовая работа / , , ; Под ред. . – М.: Высш. шк., 1992. – 192 с.

Содержание

Введение. 3

1. Общие сведения о табличных процессорах.. 5

1.1. Появление табличных процессоров. 5

1.2. Характеристики табличного процессора. 7

2. Основные навыки работы с Microsoft Excel. 10

2.1. Основные понятия табличного процессора. 10

2.1.1. Именованный стиль. 10

2.1.2. Нумерованный стиль. 11

2.1.3. Объекты таблицы. 11

2.2. Вид табличного процессора Excel 12

2.3. Перемещение по таблице и выделение ячеек. 12

2.4. Ввод данных в ячейки. 15

2.4.1. Ввод чисел. 16

2.4.2. Ввод текста. 17

2.4.3. Ввод дат и времени суток. 17

2.5. Форматирование ячеек. 18

2.6. Операции копирования, удаления, перемещения, очистки и заполнения ячеек. 26

2.7. Операции изменения размеров строк и столбцов. 31

2.8. Операции с файлами. 33

2.9. Самоконтроль. 34

2.9.1. Контрольные вопросы к главе 1 и 2. 35

2.9.2. Упражнения на самостоятельную работу. 36

3. Вычисления в Microsoft Excel. 37

3.1. Синтаксис формулы. 37

3.1.1. Операторы в формулах. 38

3.1.2. Приоритет операторов. 39

3.2. Ссылки на ячейку. 40

3.2.1. Относительные и абсолютные ссылки. 41

3.3. Использование функций для вычисления значений. 44

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

3.3.2. Ввод формулы с функцией. 46

3.3.3. Мастер функций. 47

3.4. Группы функций. 50

3.5. Самоконтроль. 52

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

3.5.2. Упражнения на самостоятельную работу. 53

4. Работа с листами и окнами.. 55

4.1. Выбор листов в книге. 56

4.2. Изменение количества отображаемых ярлычков листов. 56

4.3. Контекстно-зависимое меню для ярлычков листов. 57

4.4. Управление видимостью листов. 59

4.5. Несколько окон для одного рабочего листа. 59

4.6. Смена, сокрытие, свертывание окон. 60

4.7. Изменение масштаба изображения. 61

4.8. Самоконтроль. 61

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

4.8.2. Упражнения на самостоятельную работу. 62

5. Создание макросов. 63

5.1. Запись макроса. 64

5.2. Выполнение макроса. 65

5.3. Назначение области графического объекта для запуска макроса. 66

5.4. Назначение кнопки панели инструментов для запуска макроса. 66

5.5. Удаление макроса. 68

5.6. Самоконтроль. 69

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

5.6.2. Упражнения на самостоятельную работу. 70

6. Построение диаграмм.. 71

6.1. Шаг 1. Выбор типа диаграммы. 71

6.2. Шаг 2. Источник данных диаграммы. 80

6.3. Шаг 3. Параметры диаграммы. 81

6.4. Шаг 4. Размещение диаграммы. 82

6.5. Самоконтроль. 82

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

6.5.2. Упражнения на самостоятельную работу. 83

7. Введение в Visual Basic for Application.. 85

7.1. Изучение свойств интерфейсного объекта UserForm.. 86

7.2. Окно проекта. 89

7.3. Интерфейсные объекты. 90

7.3.1. Надпись. 91

7.3.2. Поле. 92

7.3.3. Кнопка. 94

7.3.4. Выключатель. 94

7.3.5. Флажок. 95

7.3.6. Переключатель. 95

7.3.7. Список. 95

7.3.8. Поле со списком. 96

7.3.9. Полоса прокрутки. 97

7.3.10. Счетчик. 97

7.3.11. Рамка. 97

7.3.12. Рисунок. 98

7.4. Самоконтроль. 99

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

7.4.2. Упражнения на самостоятельную работу. 102

8. Задачи обработки информации.. 103

8.1. Постановка задачи. 104

8.2. Решение задачи. 105

8.2.1. Работа с примечаниями. 105

8.2.2. Определение среднего балла. 106

8.2.3. Определение количества оценок каждого вида. 107

8.2.4. Определение категории студента. 108

8.2.5. Определение стипендии студента. 110

8.2.6. Определение стипендиального фонда. 111

8.2.7. Вычисление процентных отношений. 112

8.2.8. Вычисление среднего балла по предметам и определение максимального из них 112

8.2.9. Выделение категории студентов с помощью условного форматирования. 114

8.2.10. Построение диаграммы. 116

8.2.11. Создание программного кода. 117

8.3. Применение полученных знаний для решения однотипных задач. 128

8.3.1. Задача обработки информации об измерениях температуры. 128

8.3.2. Некоторые особенности решения задачи обработки температур. 129

8.3.3. Нахождение максимального, минимального и среднего значения. 129

8.3.4. Упорядочивание данных. 130

8.3.5. Расчет средних значений для каждого дня. 131

8.3.6. Расчет первых пяти наибольших и последних пяти наименьших значений. 133

8.3.7. Расчет числа дней, в которых температура была в одном из диапазонов. 133

8.3.8. Составление таблицы температур. 134

8.3.9. Условное форматирование и автофильтр. 135

8.3.10. Составление программы. 135

8.4. Задача на самостоятельное решение. 137

8.5. Самоконтроль. 137

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

Приложение. 141

Список использованной литературы

Содержание …………………………………………………………………………………...148