- •В.П.Обоскалов
- •Предисловие
- •Excel. Теоретические аспекты
- •Основные понятия
- •Основные термины
- •Работа с объектами
- •Функциональные клавиши
- •Указатель мыши
- •Формулы. Имена ячеек. Функции
- •Диаграммы
- •Функции списка
- •Формы данных
- •Фильтры
- •Автофильтр
- •Расширенный фильтр
- •Промежуточные итоги
- •Сводные таблицы
- •Консолидация данных
- •Нелинейные уравнения. Оптимизационные задачи
- •Поиск решения
- •Подбор параметра
- •Проектирование вычислений с помощью таблицы подстановки
- •Внешняя среда
- •Экспорт и импорт данных
- •Связь с базами данных
- •Вызов программы ms Query
- •Мастер запросов
- •Макросы
- •Запись макроса
- •Относительные ссылки
- •Выполнение макроса
- •Подключение макроса к объектам Назначение сочетания клавиш для запуска записанного ранее макроса
- •Назначение макроса кнопке или графическому объекту
- •Текст макроса
- •Excel. Лабораторный практикум
- •Начальное знакомство
- •Выделение блоков
- •Прозрачность ячеек. Перемещение данных. Копирование
- •Относительная и абсолютная адресация
- •Заполнение таблицы. Формулы. Относительная адресация. Суммирование
- •Абсолютная адресация. Имена ячеек
- •Форматирование таблицы
- •Вставка и удаление строк и столбцов
- •Форматирование ячеек
- •Форматирование столбцов и строк
- •Сетка. Показ формул
- •Автозаполнение. Списки
- •Автозаполнение
- •Диаграммы
- •Диаграмма. Общий случай
- •Гистограмма и круговая диаграмма
- •Графики функций
- •Работа с массивами данных
- •Подбор параметра. Поиск решения
- •Подбор параметра
- •Поиск Решения
- •Транспортная задача
- •Функции списка
- •Сортировка
- •Формы данных
- •Вычисление итогов
- •Консолидация данных
- •Сводная таблица
- •Внешняя среда
- •Экспорт и импорт данных. Связь документов
- •Процедуры пользователя
- •Диалоговые окна
- •UserForm
- •Решение инженерных задач наExcel
- •Проектирование эт
- •Основные функции
- •Суммирование
- •Примеры
- •Функции даты
- •Формульные расчеты
- •Тип оборудования
- •Самостоятельная работа
- •Самостоятельная работа
- •Коэффициенты, характеризующие график нагрузки
- •Проектирование таблицы.
- •Проектирование таблицы
- •Самостоятельная работа
- •Температурный режим трансформатора
- •Математический метод
- •Самостоятельная работа
- •Ремонтная ведомость Самостоятельная работа
- •Работа с матрицами
- •Расчет сети постоянного тока
- •Самостоятельная работа
- •Расчет токов трехфазного короткого замыкания
- •Метод z-матрицы
- •Эквивалентирование сети
- •Расчет токов кз по модели сети постоянного тока
- •Оптимизационные задачи
- •Решение систем нелинейных уравнений
- •Самостоятельная работа
- •Оптимальное распределение мощности нагрузки между параллельно работающими агрегатами
- •Линейное программирование
- •Самостоятельная работа
- •Двойственная задача линейного программирования
- •Самостоятельная работа
- •Работа с комплексными числами
- •Самостоятельная работа
- •Прогнозирование нагрузок
- •Метод наименьших квадратов
- •Самостоятельная работа
- •Метод скользящего среднего
- •Линейный тренд
- •Самостоятельная работа
- •Вероятность и статистика
- •Функции расчета вероятностных параметров
- •Математическое ожидание
- •Дисперсия
- •Корреляционный момент
- •Коэффициент корреляции
- •Гистограммы
- •Нормальное распределение
- •Расчет вероятностного режима электрической сети
- •Самостоятельная работа
- •Самостоятельная работа
- •Гистограмма случайной величины
- •Самостоятельная работа
- •Регрессия
- •Дополнительная регрессионая статистика
- •Простая линейная регрессия
- •Самостоятельная работа
- •Использование f-статистики
- •Вычисление t-статистики
- •Объектное программирование. Visual Basic
- •Основные понятия
- •Основные методы
- •МетодыRange, Cells, Offset
- •Самостоятельная работа
- •Методы и свойства активности объекта
- •Методы активности
- •Свойства, характеризующие активность
- •Макрорекордер
- •Самостоятельная работа
- •Свойства, характеризующие содержимое
- •Самостоятельная работа
- •Основные элементы языка
- •Общие сведения и структура программных модулей
- •Самостоятельная работа
- •Типы данных и переменные
- •Оператор Dim
- •Массивы
- •Пользовательские типы данных. Структуры
- •Динамическое перераспределение памяти
- •Константы
- •Видимость переменных и констант
- •Операторы присваивания
- •Математические и строковые операции
- •Процедуры
- •Вызов процедуры
- •Самостоятельная работа
- •Использование необязательных аргументов
- •Самостоятельная работа
- •Функции
- •Передача массива
- •Ссылки на внешние библиотеки
- •1. Скалярное произведение векторов (столбцы Excel) разной размерности
- •2. Поэлементное произведение массивов
- •Функции Application
- •Операторы принятия решения
- •ОператорIf
- •Блочная структура if
- •Многоблочная структура if
- •Select Case
- •Безусловный переход
- •Циклы объектного типа
- •Пример двойного цикла
- •Передача массива данных в таблицу
- •Коррекция блока ячеек таблицы
- •Ввод и вывод данных
- •Чтение и запись последовательных файлов
- •Диалоговые функции
- •MsgBox()
- •Самостоятельная работа
- •Функция InputBox()
- •Ввод названия файла
- •Самостоятельная работа
- •Диалоговые окна Excel
- •Самостоятельная работа
- •Диалоговые окна пользователя
- •Элементы управления
- •Командная кнопка (CommandButton)
- •Самостоятельная работа
- •Надпись (Label)
- •Самостоятельная работа
- •Окно редактирования (TextBox)
- •Окно списка (ListBox)
- •Самостоятельная работа
- •Выпадающее окно (ComboBox)
- •Групповое окно (Frame)
- •Контрольный индикатор (CheckBox)
- •Кнопка выбора (OptionButton)
- •Заполнение массива случайными числами
- •Линейная интерполяция
- •Самостоятельная работа
- •Оптимальное распределение нагрузки
- •Самостоятельная работа
- •Функции комплексных чисел
- •Самостоятельная работа
- •Обращение комплексной матрицы
- •Самостоятельная работа
- •Решение систем дифференциальных уравнений
- •' Модуль "Ввод исходных данных из таблицы Excel"
- •Перенумерация узлов
- •Самостоятельная работа
- •Матрица инциденций по узлам
- •Индивидуальные задания
- •Библиографический список
- •620002, Екатеринбург, ул. Мира,19
- •620002, Екатеринбург, ул. Мира,19 в.П.Обоскалов
Формы данных
Работа со списками, особенно с большим числом разнородных полей, существенно упрощается при использовании такого способа представления информации, как формы данных. Здесь на экране присутствует только одна запись, что позволяет видеть запись, как правило, целиком. Однако необходимо, чтобы таблица удовлетворяла требованиям, предъявляемым к спискам.
Фильтры
Фильтрация позволяет оставить видимыми только самые необходимые записи, которые формируются заданием критериев. Существуют два способа фильтрации – через режимы «автофильтр» и «расширенный фильтр».
Автофильтр
С помощью команды Автофильтр фильтрация выполняется непосредственно в таблице. В отличие от сортировки, записи при фильтрации не переупорядочиваются, а только показываются те из них, которые удовлетворяют критериям фильтрации. В первой строке области списка должны находиться метки столбцов. Каждое поле можно использовать в качестве критерия. По умолчанию в таблице выводятся все записи.
Задание критерия. После выполнения последовательности меню Данные/Фильтр/Автофильтр на всех полях списка появляются кнопки, позволяющие фильтровать содержимое данного поля по всем записям (рис. 1.3). Выбор некоторого значения в поле фамилий, например “Волков”, позволяет вывести на экран только те записи, которые относятся к данной фамилии.
Рис.
1.3.
Автофильтр
Рис.
1.4.
Пользовательский автофильтр
Здесь возможно построение более сложных логических конструкций. При этом используются операции отношения: = (равно), > (больше), < (меньше), <> (не равно), <= (меньше или равно), >= (больше или равно), "начинается с", "не начинается с", "заканчивается на", "не заканчивается", "содержит", " не содержит" (рис. 1.5). На рис. 1.4 второе условие означает вывод всех фамилий на буквы М-Я (операция больше относится к кодам букв, возрастающим по алфавиту).
Критерии фильтрации можно расширять подключением других полей. Это будут логические операции типа "И" (одновременное выполнение условий), все более и более сокращающие выборку.
С помощью команды Данные/Фильтр/Отобразить всё пользователь может восстановить отображение всех элементов списка. Однако эта команда не удалит кнопки, установленные на всех полях списка. Для того чтобы удалить эти кнопки, необходимо повторно выполнить Данные/Фильтр/Автофильтр.
Рис. 1.5. Выбор условий и значений пользовательского автофильтра
Расширенный фильтр
При использовании "Расширенного фильтра" критерии фильтрования можно задавать непосредственно в рабочем листе, что расширит возможности пользователя. В этом случае в диалоговом окне указывается область, содержащая критерии. Преимущество этого способа состоит в том, что пользователь всегда знает о применяемых критериях и может при надобности быстро изменить их.
Для вызова "Расширенного Фильтра" исполняется команда Данные/Фильтр/Расширенный фильтр. На экране появляется диалоговое окно (рис. 1.6), где указываются область данных, область критериев и позиция, с которой должен записываться (если это требуется) модифицированный список.
Рис. 1.6. Диалоговое окно расширенного фильтра
Можно ввести несколько условий (называемых в дальнейшем множественными критериями) для того, чтобы выбрать записи, отвечающие либо всем условиям одновременно, либо одному из них.
Область критериев может быть в любом месте рабочего листа (желательно над или под таблицей). В приведённом на рис. 1.7 примере критерии заданы в строках 15 и 16 таблицы. Для соединения критериев с помощью оператора "И" критерии записываются в одной и той же строке. При этом новое условие записывается в новой клетке. Если второй критерий относится к тому же полю, то название поля повторяется. Для соединения критериев с помощью оператора "ИЛИ" критерии записываются в разных строках. Записанный в ячейку критерий содержит оператор сравнения. Знак равенства при задании критериев не используется, поскольку он будет интерпретироваться как вычисляемое выражение.
Рис.
1.7.
Результат работы расширенного фильтра
Показанные в примере на рис. 1.7 критерии для расширенного фильтра настраивают фильтр таким образом, что он скрывает от пользователя те записи таблицы, которые не удовлетворяют указанному критерию. Иными словами, на экране останутся фамилии только тех студентов, которые получили повышенные оценки по математике и пониженные по физике.
Рассмотренный пример показывает условия, заданные по типу "И". Для того чтобы задать условия типа "ИЛИ", критерии записываются в разных строках, как это показано на Рис. 1 .8.
|
Cтудент |
Экзамены | ||||||
№ |
Фамилия |
Имя |
Отчество |
Математика |
Физика |
Сопромат |
Химия |
Ин.Яз |
5 |
Хомяков |
Михаил |
Евгеньевич |
3 |
4 |
4 |
4 |
4 |
1 |
Лисицын |
Павел |
Иванович |
4 |
2 |
4 |
5 |
3 |
4 |
Зайцев |
Игорь |
Николаевич |
5 |
5 |
5 |
5 |
5 |
3 |
Волков |
Александр |
Сергеевич |
4 |
4 |
4 |
4 |
5 |
2 |
Волков |
Пётр |
Сергеевич |
5 |
3 |
4 |
4 |
5 |
|
|
|
|
|
|
|
|
|
|
|
|
|
Математика |
Физика |
|
|
|
|
|
|
|
|
>3 |
|
|
|
|
|
|
|
>3 |
|
|
|
|
Рис.
1.8.
Фильтр с условиями типа "ИЛИ
Табличный способ задания логических условий является удачной находкой, которая применяется и в других программных продуктах MS Office (Access).