Курносова А.П., Улезько А.В. Практикум по информатике

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

менении данных в полях этого окна изменяется содержимое соответст-

вующих ячеек базы данных.

Для использования форм данных таблица должна иметь имена

столбцов. Имена столбцов становятся именами полей в форме данных. По-

ле соответствует каждому столбцу таблицы. Форма данных автоматически

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

це, до 32 полей за один раз. С помощью полосы прокрутк

и можно прокру-

чивать записи базы данных. Позиция выведенной записи указывается в

верхнем правом углу. Передвигаться по полям формы можно с помощью

мыши и клавиш Tab (вниз), Shift+Tab (вверх). В правой части окна рас-

положены следующие кнопки.

Добавить – очищает поля дл

я ввода новой записи базы данных. Ес-

ли снова щелкнуть кнопку Добавить

, то введенные данные будут добавле-

ны, как новая запись, в конец базы данных.

Удалить – удаляет выведен

ную запись, другие записи базы данных

сдвигаются. Удаленные записи не могут быть восстановлены.

Вернуть – восстанавлив

ает отредактированные поля в выведенной

записи, удаляя сделанные изменения. Чтобы восстановить запись, необхо-

димо сделать это перед нажатием клавиши Enter или перед переходом к

другой записи.

Назад – выводит предыдущую запись в списке. Если был определен

критерий с помощью кнопки Критерии

, то кнопка Назад выведет преды-

дущую запись из тех, которые удовлетворяют заданному критерию.

Далее – выводит следующу

ю запись базы данных.

Критерии – очищает поля перед вво

дом критериев сравнения с опе-

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

Правка – служит для выхода из режима ввода критериев. Доступна

только после нажатия кнопки Критерии

Очистить – удаляет сущест

вующий критерий из окна диалога. Дос-

тупна только после нажатия кнопки Критерии

Закрыть – закрыв

ает форму данных.

Для добавления записи к базе данных необходимо:

- выделить ячейку в таблице, к которой следует добавить запись;

- в меню Данные выбрать команду Форма

;

- щелкнуть кнопку Добавить

;

- заполнить поля новой записи;

- для перемещения к следующему полю нажать клавишу Тab;

- после ввода данных нажать клавишу Enter для добавления запи

си;

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

Закрыть

Новые записи будут добавлены в конец базы данных.

231

Установление диапазона критериев

Критерии бывают двух типов.

Критерии вычисления – это критерии, которые являются результатом

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

=F7>СРЗНАЧ($F$7:$F$21) выводит на экран строки, имеющие в столбце

F значения большие, чем среднее значение величин в ячейках F7:F21. Фор-

мула должна возвращать логическое значение ЛОЖЬ или ИСТИНА. При

фильтрации будут доступны только те строки, значения которых будут

придавать формуле значение ИСТИНА.

Критерии сравнения – это набор условий для поиска, используемый

для извлечения данных при запросах по примеру. Критерий сравнения мо-

жет быть последовательностью символов (константой) или выражением

(например,

Цена > 700).

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

рию, необходимо:

- выделить ячейку в таблице;

- в меню Данные выбрать команду Форма;

- щелкнуть кнопку Критерии;

- в полях редактирования ввести критерии для поиска данных;

- для вывода на экран первой записи, отвечающей критерию, щелк-

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

- для выво

да на экран предыдущей записи, отвечающей критерию,

щелкнуть кнопку Назад;

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

ку Критерии и ввести новые критерии;

- по окончанию щелкнуть кнопку Закрыть.

Чтобы снова получить доступ ко всем записям таблицы, необходимо

щелкнуть кнопку Критерии, а за

тем – кнопку Правка.

Команда Фильтр меню Данные позволяет отыскивать и использо-

вать нужное подмножество данных в списке. В отфильтрованном списке

выводятся на экран только те строки, которые содержат определенное зна-

чение или отвечают определенным критериям, при этом другие строки

скрываются. Для фильтрации данных используются команды Авто-

филь

тр и Расширенный филь

тр пункта Фильтр меню Данные.

Автофильтр

Команда Автофильтр устанавливает кнопки скрытых списков

(кнопки со стрелками) непосредственно в строку с именами столбцов (Рис.

99). С их помощью можно выбирать записи базы данных, которые следует

вывести на экран. После выделения эл

емента в открывшемся списке, стро-

ки, не содержащие данный элемент, будут скрыты. Например, если в скры-

том списке поля Цена выбрать

160 р., то будут выведены только записи, у

которых в поле Цена содержится зн

ачение 160 р.

232

Если в поле списка выбрать пункт Условие … , то появится окно

Пользовательский автофильтр. В верхнем право

м списке следует

выбрать один из операторов (равно, больше, меньше и др.), в поле справа –

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

гой оператор, и в поле по левую сторону – значение. Когда включен пере-

ключатель И, будут выводиться только записи, удовлетворяющие обоим

условиям. При включенном переключателе ИЛИ будут выводить

ся записи,

удовлетворяющие одному из условий.

Рис. 99. Пример установки автофильтра

Для вывода нескольких записей с самым большим или самым малым

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

Первые 10. В диалоговом окне Наложение услови

я по списку в пер-

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

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

Чтобы вывести вс

е данные таблицы, необходимо вызвать команду

Отобразить все или отменить команду Автофильтр мен

ю Данные

подменю Фильтр

Расширенный фильт

Команда Расширенный фильтр позволяет фильтровать данные с

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

творяющих определенным критериям (). При повторной фильтрации будут

просматриваться все строки, и скрытые, и открытые. Значение переключа-

телей и полей окна Расширенный фильтр следующие:

Рис. 100. Диалоговое окно Расширенный фильтр

233

фильтровать список на месте – переключатель, скрывающий

строки, которые не удовлетворяют указанному критерию;

скопировать рез

ультат в другое место – копирует отфильтро-

ванные данные на другой рабочий лист или на другое место на этом же ра-

бочем листе;

Исходный диапазон – поле, определяющее диапазон, который со-

держит сп

исок, подлежащий фильтрации;

Диапазон ус

ловий – поле, определяющее диапазон ячеек на рабо-

чем листе, который содержит необходимые условия;

Поместить рез

ультат в диапазон – поле, определяющее диапа-

зон ячеек, в который копируются строки, удовлетворяющие определенным

условиям; это поле активно только в том случае, если выбран переключа-

тель скопировать рез

ультат в другое место;

Только ун

икальные записи – переключатель, который выводит

только строки, удовлетворяющие критерию и не содержащие неповто-

ряющихся элементов. Если диапазон критериев не определен, то в этом

случае все строки списка, содержащие дубликаты, будут скрыты.

Для установления сложных критериев необходимо:

- вставить несколько строк в верхней части рабочего листа;

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

которым следует отфильтровать таблицу;

- при испол

ьзовании критериев сравнения имена критериев должны

быть идентичны именам столбцов, которые проверяются;

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

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

веряемых столбцов;

- выбрать в меню Данные пункт Фильтр

, затем – Расширенный

фильтр и в диалоговом окне вв

ести условия фильтрации.

Рис. 101. Пример расширенной фильтрации

Для объединения критериев с помощью условного оператора И сле-

дует указать критерии в одной и то

й же строке, а для объединения крите-

риев с помощью условного оператора ИЛИ следует ввест

и критерии в раз-

ных строках. Например, диапазон критериев (Рис. 101) вы

водит на экран

все записи, имеющие в столбце Цена значения больше 150 и мен

ьше

1000.

234

После использования команд Автофильтр или Расширенный

фильтр таблица переходит в режим фильтрац

ии. В этом режиме многие

коман

ие диаграмм

ском ви-

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

число

абочем лист

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

ить ди е имена категорий или рядов,

д Excel вл

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

фильтра для вывода только нужных строк можно скопировать полученное

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

Чтобы снова вывести все записи следует в меню Данные выбрать

пункт Фильтр, зат

ем пункт Отобразить все.

10.10. Построение и оформлен

Диаграмма – это представление данных таблицы в графиче

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

ков, секторов и в другой форме. Группы элементов данных, отражающих

содержимое ячеек одной строки или столбца на рабочем листе, составляют

ряд данных.

Для создания диаграммы необходимо:

- на р

аграмму, включая ячейки, содержащи

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

- выбрать команду Диаграмма

меню Вставка или щелкнуть

кнопку

;

формат и другие

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

параметры диаграммы;

тово

, тогда Мастер диаграмм самостоятельно закончит по-

пку Готово

лк

нуть на ней мышью и перетянуть

вание данных из другого приложения

Microsoft в Excel

1. Перейдите к документу, содержащему данные для копирования.

- для перехода к следующему шагу используется кн

опка Да-

лее>>;

- для построения диаграммы на любом шаге можно щелкнут

кнопку Го

строение диаграммы;

- в послед

нем (4-м) окне, после выбора места размещения диа-

граммы, щелкнуть кно

Диаграмму можно перетянуть мышью в любое место. Дл

я изменения

размера диаграммы необходимо ще

маркеры размера. Для изменения типа и параметров построенной диа-

граммы следует щелкнуть на диаграмме правой клавишей мыши и в кон-

текстном меню выбрать подходящую команду. Для удаления диаграммы

следует щелкнуть на ней мышью, чтобы появились марк

еры размера, и

нажать клавишу Delete.

10.11. Копиро

235

2. Выделите ту информацию, которую нужно скопировать.

Копировать

из меню Правка

в других приложениях

3. Выберите команду .

4. Процесс копирования может быть другим.

мот-

я вставки скопи-

з Word можно вставить в трех различных форматах:

Файл

который указ

ан

а в сп

иске нет,

Для получения подробн

ой информации об этой процедуре следует с

реть документацию к приложению.

5. Перейдите к документу Excel, предназначенному дл

рованных данных.

6. Выделите верхнюю левую ячейку той области, в которую необходи-

мо вставить данные.

7. Из меню Правка

или меню быстрого доступа выберите команду

Специальная вс

тавка

8. Выберите формат, в котором должны быть вставлены данные.

Например, текст и

как объект Microsoft Word, как изображение и как текст.

9. Нажмите кнопку OK

Открытие документа, созданного в другом приложении

1. Выберите кнопку Открыть или команду Открыть в меню .

2. Укажите имя файла.

В окне

Имя Файла перечисляются файлы того типа,

в раскрывающемся списке

Тип Файлов. Если нужного тип

то выберите параметр

Все Файлы или же просто введите полное имя до

кумента в поле

Имя Файла

Наж

мите кнопку OK.

Если отк

рывается текстовый файл, то

Мастер Текста спросит, как

разместить текст по столбцам.

236

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

1. Раскройте понятия «табличный процессор», «электронная таблица»,

«книга Microsoft Excel», «рабочий лист», «ячейка».

2. Сколько строк и столбцов может содержать рабочий лист? Сколько ра-

бочих листов может содержать книга?

3. Какие элементы управления отображаются в окне Excel?

4. Какие команды содержатся в пунктах меню и на панели инструментов

Excel?

5. Какими способами можно выделить несколько ячеек, строк, столбцов,

листов?

6. Как установить проверку на ввод данных в ячей

ки?

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

8. Как закрепить области таблицы для постоянного отображения на экра-

не? Какие способы закрепления бывают разных по размеру таблиц?

9. Как установить и снять объединение нескольких ячеек?

10. Для че

го используется строка формул? Какие основные элементы рас-

полагаются в строке формул и диалогового окна функций?

11. Какие группы функций используются в Excel?

12. Как вставить вложенную функцию?

13. Чем отличаются относительная и абсолютная ссылка на ячейки? Как их

сделать? Какие бывают виды относительных ссылок?

14. С какими параметрами формата ячейки рекомендуется оформлять шап-

ку таблицы?

15. Для чего используется фи

льтр и как его установить в таблице?

16. Каким образом можно отсортировать данные таблицы?

17. Назначение группировки и структуры данных.

18. Содержание и назначение элементов вкладки

Лист в параметрах стра-

ницы.

19. Какие стандартные шаблоны создания книг имеются в Excel? Для чего

предназначены шаблоны? Как пользователю создать свой шаблон?

20. В чем отличие различных способов вставки таблиц Excel в документы

Word?

21. Назначение инструментов Excel: зависимости, подбор параметра, сце-

нарии, форма, макрос, поиск решения и пакет анализа.

22. Как защитить данные от несанкционированного изменения в книге Excel?

23. Для чего использ

уются сводные таблицы? Как создать сводную таблицу?

24. Применение имен ячеек.

25. Виды сообщений об ошибках в ячейках.

237

10.13. Практическое задание № 26

«Создание простейших таблиц и диаграмм»

Цель: освоить приемы создания и оформления таблиц, проведения

простейших расчетов и построения диаграмм в табличном

процессоре Excel.

В табличном процессоре Excel создайте таблицу (табл. 1) и оформи-

те, как приведено, рассчитайте итоги и удельный вес.

Площадь по группам определяется автосуммированием.

Всего посе-

вов

равняется сумме площадей по группам культур. Удельный вес должен

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

(см. с. 218-216), как отношение площади посев

а по культурам (группам) к

общей сумме посевов (

Всего посевов). Сохраните книгу под именем

Площади посевов на рабочий диск в папку Информатика.

Таблица 1. Структура посевных площадей

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

Площадь посева,

га

Удельный вес, %

Зерновые и зернобобовые, всего

в т.ч.: озимая пшеница 640

озимая рожь 120

яровая пшеница 60

ячмень 900

горох 150

Технические, всего

в т.ч.: сахарная свекла 100

подсолнечник 150

Кормовые, всего

в т.ч.: кормовая свекла 120

кукуруза на силос 100

кукуруза на зеленый корм 100

однолетние травы 122

Всего посевов

На основании рассчитанной таблицы постройте две круговые диа-

граммы: первую по площади групп культур (ячейки с площадями культур

Всего посевов не используются) и вторую - по площадям только культур

(ячейки с площадями групп культур и

Всего посевов не используются).

Диаграммы должны быть размещены на отдельных листах и иметь легенду

с названиями соответственно культур или их групп. После построения

диаграмм измените в таблице 1 площадь посева сахарной свеклы, введя в

соответствующую ячейку число -

1000, и просмотрите изменения в сег-

ментах диаграмм.

Сохраните изменения в книге.

238

10.14. Практическое задание № 27

«Расчет взаимосвязанных таблиц»

Цель: освоить приемы создания и оформления системы взаимосвязанных

расчетных таблиц.

Откройте книгу

Площади посевов.xls.

На втором листе создайте и оформите таблицу 2. Наименование

культур не вводить, следует использовать ссылки на данные таблицы 1

(Лист1). Периодически делайте сохранение сделанных изменений в книге.

Таблица 2. Урожайность и цена продукции

Сельскохозяйственные культуры и группы Урожайность, ц/га Цена за 1 тонну, руб.

Зерновые и зернобобовые, всего

в т.ч. озимая пшеница 28 3 000 р

озимая рожь 30 2 500 р

яровая пшеница 25 3 500 р

ячмень 22 2 500 р

горох 18 4 000 р

Технические культуры, всего

в т.ч.: сахарная свекла 350 1 200 р

подсолнечник 19 5 250 р

Кормовые культуры, всего

в т.ч. кормовая свекла 420 600 р

кукуруза на силос 120 120 р

кукуруза на зеленый корм 120 150 р

однолетние травы 15 1 200 р

На третьем листе, используя данные таблицы 1 и таблицы 2, рассчи-

тайте структуру товарной продукции (таблица 3). Формы исходной и рас-

четной таблиц должны быть идентичны таблицам 2 и 3 соответственно.

Каждая таблица должна быть выполнена на отдельном листе. Каждый лист

должен иметь название, соответствующее наличию на нем той или иной

таблицы, например, «S посевов», «Ур-ть и цена» и «Стоимость»

Таблица 3. Объем производства и стоимость продукции

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

группы

Валовой сбор, тонн

Стоимость продук-

ции, руб.

Удельный вес, %

… … … …

Итого Х

При построении и расчете таблицы следует предусмотреть сле-

дующий формат ячеек: для строки шапки таблицы – включен перенос слов

и выравнивание установлено по центру (по горизонтали и вертикали), для

цены и стоимости продукции - денежный формат с разделителями разря-

дов; для отражения удельного веса – процентный, один знак после запятой;

для отражения стоимости продукции - знаки после запят

ой должны отсут-

ствовать. Для удобства закрепите области первого столбца и шапки табли-

цы с помощью опции главного меню Окно Фиксировать по

докна.

Во всех таблицах сгруппируйте строки культур по группам культур

с параметром

Итоги в строках над данными.

239

10.15. Практическое задание № 28

«Написание сложных формул»

Цель: освоить приемы написания универсальных формул с использованием

функций, абсолютных и относительных ссылок на ячейки,

копирования формул и автозаполнения данных.

Создайте в табличном процессоре Excel таблицу умножения Пифа-

гора, оформите ее как приведено ниже (двойная линия обрамления, залив-

ка). Цифры 1-9 вводятся с использованием автозаполнения (1, 2 и перетас-

кивание черного крестика). Все ячейки с белой заливкой должны быть рас-

считаны по одной универсальной для всех яч

еек формуле (формула пи-

шется в одной ячейке и копируется в остальные). Для написания формулы

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

пользуемые в расчете (см. с. 218-216)).

1 2 3 4 5 6 7 8 9

2 4 6 8 10 12 14 16 18

3 6 9 12 15 18 21 24 27

4 8 12 16 20 24 28 32 36

5 10 15 20 25 30 35 40 45

6 12 18 24 30 36 42 48 54

7 14 21 28 35 42 49 56 63

8 16 24 32 40 48 56 64 72

9 18 27 36 45 54 63 72 81

Сохраните книгу с именем Таблица умножения и квадратов на ра-

бочий диск в папку

Информатика.

На втором листе рассчитайте и оформите таблицу квадратов. Ис-

пользуйте следующие функции: СТЕПЕНЬ (для возведения в квадрат),

ЗНАЧЕН (преобразовать текстовое значение в число), СЦЕПИТЬ (для со-

единения десятков с единицами).

Должна получится следующая формула:

СТЕПЕНЬ(ЗНАЧЕН(СЦЕПИТЬ(адрес ячейки с десятками; адрес ячейки с единицами));2)

Переименуйте листы согласно названию таблиц и сохраните книгу.

ТАБЛИЦА КВАДРАТОВ

Единицы

Десятки

0 1 2 3 4 5 6 7 8 9

100 121 144 169 196 225 256 289 324 361

400 441 484 529 576 625 676 729 784 841

900 961 1 024 1 089 1 156 1 225 1 296 1 369 1 444 1 521

1 600 1 681 1 764 1 849 1 936 2 025 2 116 2 209 2 304 2 401

2 500 2 601 2 704 2 809 2 916 3 025 3 136 3 249 3 364 3 481

3 600 3 721 3 844 3 969 4 096 4 225 4 356 4 489 4 624 4 761

4 900 5 041 5 184 5 329 5 476 5 625 5 776 5 929 6 084 6 241

6 400 6 561 6 724 6 889 7 056 7 225 7 396 7 569 7 744 7 921

8 100 8 281 8 464 8 649 8 836 9 025 9 216 9 409 9 604 9 801

240