Ревчук И.Н. Практикум по MS Excel для экономистов

Подождите немного. Документ загружается.

101 102

3. Построить приведенную ниже таблицу. По таблице по-

строить диаграмму. В построенной диаграмме а) изменить

способ заливки одного из рядов данных; б) изменить коли-

чество рядов данных.

Температура воздуха в городах мира с 12 по 18 декабря

Лондон Рим Париж Гродно

Понедельник 10,0 C 15,0 C 5,0 C 0,0 C

Вторник 12,0 C 13,0 C 4,0 C -3,0 C

Среда 7,0 C 10,0 C 2,0 C 2,0 C

Четверг 8,0 C 14,0 C 1,0 C 4,0 C

Пятница 4,0 C 8,0 C -10,0 C 2,0 C

Суббота -2,0 C 9,0 C -4,0 C -5,0 C

Воскресение 0,1 C 5,0 C -2,0 C 3,0 C

4. Построить приведенную ниже таблицу. По таблице по-

строить диаграмму. В построенной диаграмме а) изменить

способ заливки одного из рядов данных; б) изменить коли-

чество рядов данных.

Планируемые объемы продаж

Наименование

издания

Май

Июнь

Июль

Август

Сентябрь

Октябрь

Гродненская ярмарка 20 860 8856 10445 11734 9474 16210

Гродненская правда 15 657 16100 7868 14586 12892 8050

Погоня 10 450 10330 16860 7889 13645 7243

В свободный вечер 7 680 11610 11503 15987 16154 9460

Молодежный курьер 13 550 12764 15451 16863 7880 8496

5. На основании следующей таблицы требуется графически

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

монитора и объема видеопамяти к цене и их среднее значе-

ние для компьютеров с размером оперативной памяти 8 Мб.

Выберите подходящий тип диаграммы и постройте ее. В по-

строенной диаграмме: а) изменить способ заливки одного из

рядов данных; б) изменить

количество рядов данных.

Компьютеры типа «Notebook»

(по материалам «Компьютерной газеты»

№19 (108) 20 мая 1997 года)

Модель

Процессор

ОЗУ

HDD

Монитор

Видео

Гар.срок

Цена

GoldStar 725 486sx-25 4 170 9,5 512 12 850

Compaq Contura

400CX

486dx2-40 4 260 8,7 12 1100

Austin 486dx2-50 4 340 9,5 12 1390

Arche 486dx2-50 4 260 10,4 512 12 1500

RoverBook 486dx2-66 4 200 10,4 12 800

Toshiba T2130CT 486dx4-75 8 520 10,4 1024 12 2350

Toshiba T2155CDS 486dx4-75 8 520 10,4 12 1980

Texas Instr.

TM4000M

486dx4-75 8 525 10,4 12 2390

Magitronic 486dx4-100 8 520 9,5 12 1760

Twinhead SlimNote

5100T

I486dx4-

100

16 810 10,4 12 2380

CTX EzBook 5x86-100 8 810 10,4 12 1650

Compal 5x86-133 8 540 10,4 1024 36 1350

Toshiba T105CS p-75 8 810 10,4 1024 12 1600

Compal p-100 8 540 11,3 1024 36 1750

Hitachi E-100D p-100 8 810 11,3 1024 12 1850

Compaq Armada 1120 p-100 8 810 10,4 1024 12 1984

Toshiba T110SC p-100 8 810 11,3 1024 12 2000

Toshiba 110SC p-100 8 772 11,3 1024 2100

Toshiba T205CDS p-100 8 810 11,3 2048 24 2200

103 104

Продолжение таблицы

Toshiba T425CDS p-100 8 810 11,3 2048 24 2250

JVC p-100 8 540 11,3 1024 36 2300

Toshiba T425CDT p-100 8 1300 11,3 2048 36 2800

Toshiba 200CDS Sat-

ellite

p-100 8 772 11,3 1024 2820

RoverBook p-100 1870

Leo DesigNote 3510T p-120 8 810 11,3 12 2446

Compaq Armada 1130 p-120 16 1000 10,4 1024 12 2240

Compaq Armada 1510 p-120 16 1080 11,3 2048 36 2320

Compaq Armada

1130T

p-120 16 1000 10,4 1024 12 2380

6. На основе следующей таблицы построить объемную гис-

тограмму.

В построенной диаграмме: а) изменить способ за-

ливки одного из рядов данных; б) изменить количество ря-

дов данных.

Продажи в 2003 году: МП «Сириус»

Наименование товара Кв 1 Кв 2 Кв 3 Кв 4

Шкаф Венеция 250 190 180 230

Компьютерный стол 345 450 270 330

Кресло-кровать 120 150 100 160

Кухня «Вязынка» 90 110 120 150

Комод «Венеция» 350 400 220 375

Спальный гарнитур 450 210 290 380

7. На основе следующей таблицы построить объемную гис-

тограмму. В построенной диаграмме: а) изменить способ за-

ливки одного из рядов данных; б) изменить количество ря-

дов данных.

Периодические издания: продажи за 2004 год

Наименование издания Кв 1 Кв 2 Кв 3 Кв 4

«Мой сад» 3 200 3 939 2 310 3 200

Продолжение таблицы

«Советская Белоруссия» 3 714 4 321 3 012 4 120

«Литературная газета» 4 374 5 012 4 678 5 115

«АиФ» 5 869 6 780 5 500 5 750

«Главный бухгалтер» 3 560 2 900 3 100 3 110

8. На основе следующей таблицы построить объемную гис-

тограмму. В построенной диаграмме а) изменить способ за-

ливки одного из рядов данных; б) изменить количество рядов

данных.

Расходы на путешествие

Город Гостиница Питание Экскурсии Покупки

Варшава 159 207 109 233

Прага 219 123 107 193

Москва 249 155 231 143

Киев 240 222 201 165

Одесса 180 143 243 109

Минск 200 246 160 195

9. В таблице приведена информация о производстве товаров

фирмой и ее реализации в 2002–2003 гг. Рассчитать годовые

итоговые показатели и процент реализации продукции (отноше-

ние «Реализовано» к «Произведено»).

Год Квартал Произведено Реализовано % реализации

2002 Всего

2002 I кв. 134 105

II кв. 129 117

III кв. 144 165

IV кв. 135 126

2003 Всего

2003 I кв. 160 132

II кв. 136 128

105 106

Продолжение таблицы

III кв. 140 160

IV кв. 167 160

На основе построенной таблицы построить объемную гисто-

грамму. В построенной диаграмме а) изменить способ заливки

одного из рядов данных; б) изменить количество рядов данных

10. В таблице приведена информация о предварительных ито-

гах выборов в Государственную Думу РФ в 1999 году.

Блок, партия % голосов

КПРФ

27,2 %

Единство

28,2 %

СПС

12,2 %

Отечество

9,5 %

ЛДПР

7,2 %

Яблоко

6,8 %

НДР

2,4 %

СЖР

1,8 %

Пенсионеры

1,4 %

Кедр

1,2

Прочие

2,1 %

На основе таблицы построить круговую диаграмму. В постро-

енной диаграмме: а) изменить способ заливки одного из рядов

данных; б) изменить количество рядов данных

Управление списками (базами данных)

В Excel присутствуют средства, позволяющие обрабаты-

вать информацию, организованную по принципу баз данных.

Это средства работы со списками. Списки состоят из данных

одинаковой структуры. Чтобы достичь максимальной эффек-

тивности в работе со списками, при формировании списков не-

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

• данные в пределах одного столбца однородны (одного ти-

па);

• столбцам присвоены уникальные имена; каждая строка уни-

кальна;

• список не содержит пустых строк.

• между списком и другими данными листа необходимо оста-

вить, по меньшей мере, одну пустую строку и один пустой

столбец. Это позволяет Excel быстрее обнаружить и выде-

лить список при выполнении сортировки, наложении

фильтра или вставке вычисляемых автоматически итоговых

значений;

• следует избегать размещения важных данных слева или

справа от списка; данные могут быть скрыты при фильтра-

ции списка;

• перед внесением изменений в список необходимо убедиться

в том, что все скрытые строки и столбцы отображены. Если

строки или столбцы списка скрыты, данные могут быть не-

преднамеренно удалены;

• при создании списка не рекомендуется создавать дополни-

тельные строки, отделяющие заголовки столбцов и запол-

ненные символами «тире» или знаками равенства, так как

Excel может рассматривать такие строки как данные.

Строки списка называются записями, а столбцы – полями.

Основными видами обработки баз данных являются поиск и из-

влечение данных, сортировка и анализ полученных

результатов.

Примеры решения задач

Задача 5.1. База данных АВТОВЛАДЕЛЬЦЫ содержит следую-

щие поля: Фамилия, Имя, Отчество, Дата выпуска машины,

Марка машины, Дата регистрации машины, Номерной знак, Се-

рия, Цвет, Цена($). Найти записи, в которых идет речь о маши-

нах марки «Мерседес».

107 108

Решение. Вариант 1. Воспользуемся средством Автофильтр.

Выполним указанные ниже действия:

1) установим курсор в фильтруемом списке (базе данных);

2) выполним команду Данные/Фильтр/Автофильтр (рис. 89);

3) откроем список в поле Марка машины (рис. 90);

4) выберем в списке значение «Мерседес». На рис. 91 изо-

бражен результат применения фильтра к заданной базе дан-

ных.

Рисунок 89

Рисунок 90

Рисунок 91

Вариант 2. Воспользуемся средством «Фильтр по выделенно-

му». Установим курсор в ячейку, содержащую название марки

109 110

«Мерседес» и нажмем кнопку Автофильтр на панели инстру-

ментов Стандартная. Результат приведен на рисунке 92.

Рисунок 92

Вариант 3. Воспользуемся средством Форма. Для этого, уста-

новив курсор в списке, выполним команду Данные/Форма. В

результате получим доступ к окну диалога, приведенному на

рисунке 93. Нажмем кнопку Критерии и в пустом бланке спра-

ва от текста Марка машины введем нужное название (рис. 94).

Нажимая на кнопку Далее, получим все записи списка, удовле

творяющие заданному условию (рис. 95).

Рисунок 93 Рисунок 94

Рисунок 95

Задача 5.2. В базе данных АВТОВЛАДЕЛЬЦЫ найти записи, в

которых идет речь о машинах черного или сиреневого цвета, год

выпуска которых – 1998.

Решение. Воспользуемся средством Автофильтр:

1) устанавливаем курсор в фильтруемом списке (базе данных);

2) выполняем команду Данные/Фильтр/Автофильтр;

3) открываем список в поле Дата выпуска машины;

4) выбираем в списке

значение «Условие». Выбираем оператор

«больше или равно» и устанавливаем дату 1.01.98. Вторым ус-

ловием, связанным с первым логическим И является «меньше

или равно 31.12.98» (рис. 96);

111 112

Рисунок 96

5) в поле «Цвет открываем список и выбираем значение

«Условие» (рис. 97):

Рисунок 97

На рисунке 98 изображен результат применения фильтра к за-

данной базе данных.

Рисунок 98

Задача 5.3. В базе данных АВТОВЛАДЕЛЬЦЫ найти записи, в

которых идет речь о машинах, год выпуска которых 1997 или

2000.

Решение. Так как 1997 и 200 годы относятся к двум диапазонам

от 1.01.97 до 31.12.97 и от 1.01.00 до 31.12.00, то следует вос-

пользоваться средством Расширенный фильтр. Для этого нужно

выполнить такую последовательность действий:

1) скопировать из списка заголовок

фильтруемого столбца и

дважды вставить его на рабочий лист, например так, как показа-

но на рисунке 99;

Рисунок 99

2) ввести в строки под заголовками условий требуемые крите-

рии отбора (рис. 100);

113 114

Рисунок 100

3) установить курсор в списке;

4) выполнить команду Данные/Фильтр/Расширенный фильтр;

5) чтобы показать результат фильтрации, скрыв ненужные

строки, следует установить переключатель Обработка в по-

ложение Фильтровать список на месте. Чтобы скопировать

отфильтрованные строки в другую область листа, следует ус-

тановить переключатель Обработка в положение Скопиро-

вать результаты в

другое место, перейти в поле Поместить

результат в диапазон, а затем указать верхнюю левую ячейку

области вставки;

6) ввести в поле Диапазон критериев ссылку на диапазон ус-

ловий отбора, включающий заголовки столбцов и щелкнуть

по кнопке Ok (рис. 101).

Результат фильтрации приведен на рисунке 102.

Рисунок 101

Рисунок 102

Задача 5.4. В базе данных АВТОВЛАДЕЛЬЦЫ найти записи, в

которых идет речь о машинах, год выпуска которых 1997 или

2000, а цвет черный или розовый.

115 116

Решение. На рисунке 103 приведены диапазоны критериев для

фильтрации списка в этом случае.

Дата выпуска

машины

Дата выпуска

машины

Цвет

>=1.01.97 <=31.12.97 розовый

>=1.01.00 <=31.12.00 розовый

>=1.01.97 <=31.12.97 черный

>=1.01.00 <=31.12.00 черный

Рисунок 103

Задача 5.5. В базе данных АВТОВЛАДЕЛЬЦЫ найти записи,

в которых фамилии автовладельцев заканчиваются на. «ский»,

«ская» или «сков».

Решение. На рисунке 104 приведен диапазон критериев для

фильтрации списка (1 вариант), а на рисунке 105 – второй ва-

риант. В первом варианте используется оператор точного со-

ответствия, который в общем виде выглядит так:

=''=текст''

После

ввода вместо текст ский, ская или сков на экране по-

лучим:

Так получим на экране: Так вводим:

Фамилия Фамилия

=*ский ="=*ский"

=*ская ="=*ская"

=*сков ="=*сков"

Рисунок 104

Во втором варианте использован вычисляемый критерий. По-

этому строка заголовков в области критериев либо не заполня-

ется, либо должна содержать названия, отличные от названий

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

следует включать строку заголовков, даже если она пустая.

Ссылки в формулах указываются для первой записи в базе дан

ных.

Так получим на экране: Так вводим:

ЛОЖЬ =ПРАВСИМВ(A2;4)="ский"

ЛОЖЬ =ПРАВСИМВ(A2;4)="ская"

ЛОЖЬ =ПРАВСИМВ(A2;4)="сков"

Рисунок 105

Задача 5.6. В базе данных АВТОВЛАДЕЛЬЦЫ найти записи, в

которых идет речь о машинах, цена которых отличается от сред-

ней цены по всей базе не более чем на 1000. Найти количество

таких записей, среднюю, максимальную и минимальную цены

таких машин.

Решение. На рисунке 106 приведен диапазон критериев. Здесь

$J$2:$J$30 ― диапазон размещения столбца

с полем Цена($).

Так как среднее значение при переходе от записи к записи не

должно меняться, использованы абсолютные ссылки. Для про-

верки правильности результата можно вычислить среднюю цену

по базе. В данной задаче это значение равно 4388,69. На рисун-

ке 107 приведены отфильтрованные записи.

Так получим на экране: Так вводим:

ИСТИНА =ABS(СРЗНАЧ($J$2:$J$30)–

J2)<=1000

Рисунок 106

117 118

Рисунок 107

Для получения количества таких записей, средней, максималь-

ной и минимальной цены таких машин следует воспользовать-

ся соответствующими функциями обработки баз данных:

=БСЧЁТА(А1:J30;J1;Критерии)

=ДСРЗНАЧ(А1:J30;J1;Критерии)

=ДМАКС(А1:J30;J1;Критерии)

=ДМИН(А1:J30;J1;Критерии).

Имя «Критерии» присвоено диапазону N10:N11, в котором

расположена область критериев. На рисунке 108 приведен ре-

зультат фильтрации (для

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

формулам.

Рисунок 108

Замечание. Для суммирования значений в списке по заданным

условиям может быть использован мастер суммирования. На-

пример, с помощью мастера суммирования можно создать фор-

мулу для вычисления суммарной цены машин по определенной

марке в списке. Мастер суммирования является надстройкой.

Если в подменю Мастер меню Сервис отсутствует команда

Частичная сумма, необходимо установить и загрузить

про-

грамму надстройки.

Для определения суммарной цены всех машин марки «Ауди»,

год выпуска которых находится в диапазоне от 1990 до 2000 го-

дов, следует выполнить следующие действия:

1) установить курсор в списке;

2) выполнить команду Сервис / Мастер /Частичная сумма;

3) проверить, правильно ли указан диапазон размещения списка;

4) нажать кнопку Далее;

5) следуя указаниям Мастера, добавить нужные условия;

6) выбрать на рабочем листе ячейку (ячейки), в которые следу-

ет вывести информацию.

119 120