- •В.П.Обоскалов
- •Предисловие
- •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 в.П.Обоскалов
Функции списка
Excel распознает списки автоматически по однородности данных в строках или столбцах. Пример списков – смета на оборудование. Ограничителями списков являются пустые строки. Если функция списка применяется только для определенной части таблицы, то эту часть необходимо выделить. Функции списка нельзя использовать для несмежных выделений. Рассмотрим использование функций списка на примере сметы.
Перейдите на лист «Смета». С целью более эффективной иллюстрации работы со списками, а также для закрепления усвоенного между полями «Наименование» и «Количество» введите дополнительное поле (столбец) «Напряжение, кВ». Удалите строку 7 -«Всего» (суммарные результаты будут получены через функции списка). Увеличьте число строк (записей) таблицы вдвое путем копирования строк 3-6 (в первой строке у вас название таблицы, а во второй – наименование полей). Приведите в порядок номера позиций. Старой группе оборудования назначьте напряжение 110 кВ, а новой 220. Цену нового оборудования увеличьте вдвое (используйте числа, а не формулы).
Для работы со списками желательно, чтобы первая строка отражала название полей записей, поэтому между первой и второй строками вставьте пустую строку, а затем ее скройте.
Сортировка
Сортировка является одной из основных и более часто используемых функций списка.
Установите АК внутри списка и выполните сортировку таблицы по наименованию оборудования в алфавитном порядке (команда Данные/Сортировка). При этом внимательно ознакомьтесь с диалоговой панелью «Сортировка диапазона». Выполните сортировку списка по цене, по затратам и цене, по номерам позиций (в возрастающем порядке).
Сортировку по одному полю более быстро позволяют выполнять клавиши ИМ . Для работы с ними достаточно просто установить АК внутри поля, по которому реализуется сортировка, и нажать соответствующую клавишу.
Выполните предыдущие работы с использованием клавиш «Сортировка».
Формы данных
Формы являются прекрасным средством заполнения таблиц типа «Списки» (см. п. 1.6). Все данные, относящиеся к одной записи, представляются на одной панели, что повышает их визуализацию.
Установите АК внутри таблицы. Выполните: Данные/Форма. Появилась диалоговая информационная панель (рис. 2.17), где можно дополнять и корректировать записи. Обратите внимание, что здесь присутствуют и вычисляемые поля (Затраты). Пролистайте записи от начала до конца. Измените количество. Как изменились затраты? Создайте новую запись, например, для 5 выключателей 35 кВ по цене 100 тыс.р.. Закончите работу с «Формой». Как изменилась таблица? Если вы не записали в «Форме» номер, введите его.
В результате дополнительного ввода данных заполняются строки в нижней части таблицы. В результате может оказаться, как это имеет место в нашем случае, что будут заполнены все пустые строки между таблицей и расположенными ниже данными. Отсюда рекомендация: все исходные данные - в начало листа. Расширяющиеся таблицы должны иметь достаточное пространство для расширения.
Перенесите курс доллара в начало листа.
Рис.
2.42.
Форма
Введите через форму дополнительную запись о четырех выключателях 110 кВ. В результате таблица приобретает вид рис.2.18
Рис.
2.43.
Дополнительные записи
Вызовите «Форму» и нажмите кнопку “Критерий”(см. рис. 2.17). В поле “Наименование” введите “Выключатель”. Перейдите в режим формы (кнопки "Далее" или «Назад» в зависимости от направления просмотра записей). Пролистайте список. Просмотрите все выключатели напряжением меньше 220 кВ. (в критериях можно определять простейшие условия (<, >); знак равенства можно не ставить).
Фильтр
Представленные в предыдущем разделе функции фильтрации в «Формах» примитивны и не покрывают всех наших потребностей. Excel предоставляет два режима фильтрации данных: «Автофильтр» и «Расширенный фильтр». Прочитайте в теоретической части соответствующий раздел (п.1.6.2).
Автофильтр. Фильтрация осуществляется непосредственно в таблице и позволяет оставить видимыми только те записи, которые формируются заданием встроенных критериев (команда Данные/Фильтр/Автофильтр).
Вызовите Автофильтр. Нажмите кнопку фильтра в поле "Наименование". Оставьте в таблице только выключатели. Выберите функцию "Условие". Составьте условие и выполните фильтрацию, чтобы в таблице остались все записи, кроме выключателей. Восстановите список. Оставьте в таблице оборудование напряжением 35U220. Оставьте в таблице оборудование с наименованием, начинающимся на букву Р, на букву Р и дальше по алфавиту. Восстановите таблицу в полном объеме. Отключите режим фильтрации (Данные/Фильтр/Автофильтр).
Расширенный фильтр. Основным достоинством расширенного, или пользовательского, фильтра является достаточно хороший обзор условий фильтрации. Пусть нам требуется вывести данные по выключателям напряжением меньше 220 кВ.
Ниже таблицы скопируйте строку с названиями полей. В поле "Наименование" укажите «Выключатель», а в поле «Напряжение» - «<220». Установите АК внутри таблицы-списка и выполните: Данные/Фильтр/Расширенный фильтр. В диалоге укажите мышкой область критериев. Придумайте, запишите и реализуйте свой критерий фильтрации.