Электронные таблицы EXCEL
Методические указания
для выполнению лабораторных работ
Часть 1
Санкт-Петербург
Содержание
Содержание. 2
2. Требования к использованию табличного процессора Excel 2
3. Технология работы с базами данных средствами MS Excel 3
3.1. База данных. Составляющие базы данных. 3
3.2. Ввод данных в базу данных. Ввод имен полей.. 5
3.3. Использование формы данных. 5
3.4. Сортировка данных. Сортировка по возрастанию/по убыванию.. 6
3.5. Сортировка в особом порядке. 7
3.6. Сортировка по четырем и более полям.. 8
3.7. Поиск, фильтрация и редактирование в базах данных. Использование формы данных 9
3.8. Автофильтр. 10
3.9. Расширенный фильтр. 11
3.10. Задание условий с использованием логической операции ИЛИ.. 12
3.11. Задание условий с использованием логической операции И.. 12
3.12. Задание условий с одновременным использованием логических операций И и ИЛИ 14
3.13. Использование вычисляемых условий.. 14
3.14. Анализ списка с помощью подведения промежуточных итогов. 15
3.15. Функции для анализа списка. 18
3.16. Функции баз данных. 19
3.17. Проверка вводимых значений.. 20
3.18. Задание типа данных и допустимых значений.. 20
3.19. Сообщение для ввода. 22
3.20. Задание сообщения об ошибке. 23
4. Рекомендуемая методика выполнения РАБОТ. 24
4.1. Варианты индивидуальных заданий по работе со списками в MS Excel 24
4.2. Пример получения индивидуального задания. 32
Рекомендуемая литература. 32
Требования к использованию табличного процессора Excel
Разрабатываемые таблицы должны быть выполнены в соответствии с требованиями по оформлению текстовых документов. Каждую таблицу должен предварять заголовок, раскрывающий ее назначение. Перед заголовком (в его тексте) или выше заголовка должно быть указано слово «Таблица» и ее номер. В ячейках таблицы Excel должны быть явно заданы типы данных. Таблицы должны быть аккуратно и красиво оформлены. Клетки таблицы должны быть обрамлены разделительными линиями. Края и заголовки таблицы должны быть выделены линиями другого вида. Информация в ячейках таблицы должна быть единообразно позиционирована.
Табличный процессор Excel представляет собой компьютерную программу, предназначенную для обработки данных. Обработка может включать в себя:
- выполнение различных вычислений с использованием аппарата функций и формул;
- исследование влияния различных факторов на данные;
- решение задач оптимизации;
- получение выборки данных, удовлетворяющих определенным критериям;
- построение графиков и диаграмм;
- статистический анализ данных.
Технология работы с базами данных средствами MS Excel
База данных. Составляющие базы данных
Запустите MS Excel. Проще всего запустить MS Excel из главного меню (кнопка Пуск). В главном меню необходимо нажать на опцию Программы, после чего в появившемся выпадающем меню выбрать опцию Microsoft Excel.
Книга в Microsoft Excel представляет собой файл, используемый для обработки и хранения данных. Каждая книга может состоять из нескольких листов, поэтому в одном файле можно поместить разнообразные сведения и установить между ними необходимые связи. Начальное количество листов книги задается установками "по умолчанию". Их можно задать (изменить) воспользовавшись пунктом главного меню Сервис\Параметры\Общие\Листов в новой книге. Количество листов в уже созданной книге можно изменить через пункт главного меню Вставка\Лист. Для удаления ненужного листа предусмотрена команда Правка\Удалить лист.
Листы служат для организации и анализа данных. Можно вводить и изменять данные одновременно на нескольких листах, а также выполнять вычисления на основе данных из нескольких листов.
Лист - основной документ, используемый в MS Excel для хранения и обработки данных. Он может также называться электронной таблицей. Кроме электронных таблиц могут существовать также специально организованные листы диаграмм, макросов, диалоговых окон и т.п. Такие листы, как правило, генерируются системой автоматически по мере возникновения потребности. Листы объединяются в книгу. Каждая книга Excel имеет собственное имя и рассматривается операционной системой как самостоятельный файл. Обычный лист MS Excel состоит из ячеек, образующих строки и столбцы. Листы объединены в книгу. В Microsoft Excel в качестве базы данных можно использовать список. Список - набор строк таблицы, содержащей связанные данные, например база данных счетов или набор адресов и телефонов клиентов. Список может использоваться как база данных, в которой строки выступают в качестве записей, а столбцы являются полями. Первая строка списка при этом содержит названия столбцов.
При выполнении обычных операций с данными, например при поиске, сортировке или обработке данных, списки автоматически распознаются как базы данных.
Выделяются следующие элементы списка (рис. 1):
- запись (отдельная строка);
- поле (отдельный столбец);
- строка заголовков (первая строка списка);
- имена полей (имя колонки в первой строке списка).
Рис.1. Элементы списка в MS Excel
Диапазон базы данных - это область, где хранятся данные списка. Связанные друг с другом данные записываются в отдельные строки. Каждому столбцу соответствует свое поле списка с уникальным именем поля. Правила для определения местоположения списка:
· если, какому-либо диапазону ячеек на текущем листе присвоено имя " База_данных ", Excel будет считать этот диапазон списком,
· если перед выбором команды, в меню Данные выделен диапазон ячеек, то он будет воспринят как список.
Пусть некоторая ячейка внутри списка активна. Excel определит базу данных следующим образом. Будет сделана попытка найти самую верхнюю строку, содержащую текст. В случае успеха эта строка станет строкой заголовков, а все соседние непустые строки под заголовками будут рассмотрены как список. Если текстовая строка не найдена, в качестве списка будут выделены все соседние непустые ячейки вокруг активной.
Диапазон критериев - специально организованная область на рабочем листе (на том, на котором находится список), где задаются критерии поиска информации, здесь указываются имена полей и отводится область для записи условий отбора.
Диапазон для извлечения - область, в которую копируют выбранные из списка данные. Диапазон для извлечения может быть ограниченным и неограниченным. Ограниченный диапазон для извлечения - диапазон, для которого указано максимально допустимое количество строк. Чтобы задать такой диапазон, следует в какой-либо строке перечислить необходимые имена полей, а затем выделить эту строку и нужное количество пустых строк под ней. Неограниченный диапазон для извлечения - диапазон, содержащий только строку необходимых имен полей.
