Конспект лекций по Excell. 2011 печ. 184Л Электронные таблицы.Кон.лек- 26.12. Конспект лекций по дисциплине компьютернаятехникаипрограммировани е для студентов 1го курса дневной и заочной форм обучения образовательно
 Скачать 3.95 Mb.
|
|
0 МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ, МОЛОДЕЖИ И СПОРТА УКРАИНЫ ХАРЬКОВСКАЯ НАЦИОНАЛЬНАЯ АКАДЕМИЯ ГОРОДСКОГО ХОЗЯЙСТВА М. В. Булаенко Б. И. Погребняк Э Л ЕК ТР О Н Н Ы Е Т А БЛ И Ц Ы Конспект лекций Харьков – ХНАГХ – 2011 1 2 МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ, МОЛОДЕЖИ И СПОРТА УКРАИНЫ ХАРЬКОВСКАЯ НАЦИОНАЛЬНАЯ АКАДЕМИЯ ГОРОДСКОГО ХОЗЯЙСТВА До М.П.Пан М. В. Булаенко Б. И. Погребняк Э Л Е К Т Р О Н Н Ы Е Т А Б Л И Ц Ы Конспект лекций по дисциплине К О М П Ь Ю Т Е Р Н А Я Т Е Х Н И К А И П Р О Г Р А М М И Р О В А Н И Е (для студентов 1-го курса дневной и заочной форм обучения образовательно- квалификационного уровня бакалавр, направления подготовки 6.070101–Транспортные технологии (по видам транспорта)) Харьков – ХНАГХ – 2011 3 Булаенко М. В. Электронные таблицы. Конспект лекций по дисципли- не “Компьютерная техника и программирование» (для студентов 1-го курса дневной и заочной форм обучения образовательно-квалификационного уровня бакалавр, направления подготовки 6.070101–Транспортные технологии (по видам транспорта)) / М. В. Булаенко, Б. И. Погребняк; Харьк. нац. акад. город. хоз-ва.− Х.: ХНАГХ, 2011.– 240 с. Авторы: к.т.н., доцент М. В. Булаенко, к.т.н., доцент Б. И. Погребняк. Конспект лекций построен в соответствии с требованиями кредитно- модульной системы организации учебного процесса и согласован с ориенти- ровочной структурой содержания этой учебной дисциплины, рекомендован- ной Европейской Кредитно-Трансферной Системой (ECTS). Рецензент: доцент А. Б. Костенко Утверждено на заседании кафедры Прикладной математики и информа- ционных технологий, протокол № 1 от 30 августа 2010 г. © М. В. Булаенко, Б. И. Погребняк, ХНАГХ, 2011 4 С О Д Е Р Ж А Н И Е ЛЕКЦИЯ № 1. ЭЛЕКТРОННАЯ ТАБЛИЦА MICROSOFT EXCEL НАЗНАЧЕНИЕ И ВОЗМОЖНОСТИ .............................................................. ...6 1.1. Введение ....................................................................................................... 6 1.2. История создания электронных таблиц .................................................... 6 1.3. Организация данных в Microsoft Excel ..................................................... 8 1.4. Адресация ячеек ........................................................................................ 12 1.6. Особенности интерфейса.......................................................................... 18 1.7. Режимы работы.......................................................................................... 21 1.8. Завершение работы.................................................................................... 22 ЛЕКЦИЯ № 2. СОЗДАНИЕ ЭЛЕКТРОННЫХ ТАБЛИЦ........................ 23 2.1. Навигация (перемещение) по электронным таблицам .......................... 23 2.2. Заполнение рабочих листов...................................................................... 27 2.3. Автоматизация заполнения таблиц ......................................................... 32 ЛЕКЦИЯ № 3. ВЫЧИСЛЕНИЯ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ......... 39 3.1. Формулы ..................................................................................................... 39 3.2. Ввод формул............................................................................................... 42 3.3. Функции...................................................................................................... 45 3.4. Замена формулы ее результатом.............................................................. 55 3.5. Обработка ошибок..................................................................................... 57 3.6. Зависимости ячеек ..................................................................................... 63 3.7. Пересчет формул ....................................................................................... 70 3.8. Функции рабочего листа........................................................................... 71 ЛЕКЦИЯ № 4. РЕДАКТИРОВАНИЕ И ФОРМАТИРОВАНИЕ ЭЛЕКТРОННЫХ ТАБЛИЦ................................................................................ 72 4.1. Выделение объектов.................................................................................. 72 4.2. Редактирование содержимого ячеек и рабочих листов......................... 76 4.3. Очистка и удаление ячеек......................................................................... 84 4.4. Форматирование и защита информации ................................................. 86 4.5. Форматирование строк и столбцов........................................................ 110 ЛЕКЦИЯ № 5. ДИАГРАММЫ....................................................................... 113 5.1. Построение диаграмм ............................................................................. 113 5.2. Настройка диаграмм................................................................................ 124 5.3. Добавление и удаление исходных данных ........................................... 126 5.4. Формула ряда данных ............................................................................. 128 5.5. Пользовательские типы диаграммы ...................................................... 129 5.6. Установка типа диаграммы по умолчанию .......................................... 130 5 5.8. Размещение на диаграмме текста и рисунков ...................................... 131 ЛЕКЦИЯ № 6. БАЗЫ ДАННЫХ ................................................................... 132 6.1. Добавление, удаление и редактирование записей ............................... 135 6.2. Поиск и фильтрация ................................................................................ 141 ЛЕКЦИЯ № 7. ОБОБЩЕНИЕ ДАННЫХ ................................................... 150 7.1. Структуризация и итоги.......................................................................... 150 7.2. Консолидация данных............................................................................. 158 7.3. Связывание консолидированных данных с исходными...................... 164 ЛЕКЦИЯ № 8. СВОДНЫЕ ТАБЛИЦЫ ....................................................... 165 8.1. Создание ................................................................................................... 167 8.2. Формирование сводной таблицы вручную непосредственно на рабочем листе .......................................................................................... 170 8.3. Формирование сводной таблицы с помощью специального окно диалога Мастер сводных таблиц и диаграмм – макет......................... 171 8.4. Настройка ................................................................................................. 174 8.5. Сортировка и изменение порядка заголовков полей в сводных таблицах ................................................................................................... 181 8.6. Группировка элементов сводных таблиц.............................................. 186 8.7. Общие и промежуточные итоги в сводных таблицах.......................... 193 8.8. Изменение итоговой функции................................................................ 194 ЛЕКЦИЯ № 9. СРЕДСТВА ЧИСЛЕННОГО АНАЛИЗА ДАННЫХ ..... 195 9.1. Надстройка Поиск решения.................................................................... 195 9.2. Сохранение сценариев и создание отчетов........................................... 204 9.3. Неоднозначность решений ..................................................................... 206 9.4. Решение систем нелинейных уравнений .............................................. 208 9.5. Решения систем линейных алгебраических уравнений ...................... 210 9.6. Регрессия .................................................................................................. 211 9.7. Использование надстройки Поиск решения......................................... 212 9.8. Использование диаграмм........................................................................ 216 9.9. Использование надстройки Пакет анализа данных ............................. 218 ЛЕКЦИЯ № 10. ИНТЕГРАЦИЯ ПРИЛОЖЕНИЙ .....................................222 10.1. Буфер обмена ........................................................................................... 222 10.2. Динамический обмен данными (DDE) .................................................. 225 10.3. Связывание и внедрение объектов (OLE) ............................................. 228 10.4. Полиморфизм........................................................................................... 236 10.5. Управление связями ................................................................................ 237 СПИСОК ИСТОЧНИКОВ ................................................................................ 240 6 ЛЕКЦИЯ № 1. ЭЛЕКТРОННАЯ ТАБЛИЦА MICROSOFT EXCEL НАЗНАЧЕНИЕ И ВОЗМОЖНОСТИ 1.1. Введение Электронные таблицы (табличные процессоры) – это программы, ко- торые предназначены для обработки информации, представленной в табличном виде, т.е. в виде строчек и столбцов, на пересечении которых на- ходятся ячейки. Основным компонентом электронной таблицы является ячейка, которая обозначается неким адресом (именем, ссылкой, координатами). Часть ячеек таблицы содержит некоторые исходные данные, а другая – формулы, операндами которых являются адреса ячеек. В результате этого всякое изменение содержимого какой-либо ячейки исходных данных приво- дит к немедленному изменению содержимого ячеек с формулами – т.е. содержимое электронной таблицы обновляется (пересчитывается) ав- томатически, и всегда остается актуальным. Столь простая, и в тоже время, абсолютно гениальная идея послужила толчком к созданию огромного количества программ этого класса. Наиболее широкое распространение они получили в области выполнения различного рода экономических расчетов. Однажды заполнив пустую электронную таб- лицу исходными данными и формулами в дальнейшем ее можно только «экс- плуатировать» – заносить исходные данные, а результаты будут получаться автоматически. Однако область применения электронных таблиц не ограничи- вается только экономическими расчетами. Благодаря наличию в них большого количества самых разнообразных функций, а также средств наглядного пред- ставления информации в виде различного рода графиков и диаграмм, они на- шли самое широкое применение и в других областях выполнения расчетов, таких, например, как инженерные, научно-технические, статистические и т.д. При этом электронная таблица может оперировать не только константами, но и переменными – содержанием ячеек. 1.2. История создания электронных таблиц Самая первая электронная таблица – VisiCalc – была создана в 1979 году Дэниелом Бриклином и Робертом Фрэнкстоном и принесла своим создателям только в течение первого года продаж около двух миллионов долларов чистой прибыли. А началось все с того, что опыт работы в компьютерной индустрии выпускника Массачусетского технологического института (МТИ) 7 Дэниела Бриклина навел его на мысль, что, не плохо разбираясь в компьюте- рах, он при этом совершенно ни чего не понимает в бизнесе. Это и побудило его пойти на курсы в Гарвардскую школу бизнеса. Во время занятий на этих курсах ему приходилось выполнять много однообразных вычислений, основ- ная специфика которых заключалась в том, что при изменении какой-либо од- ной цифры необходимо было пересчитывать все, зависящие от нее, величины. Малейшая ошибка при этом могла испортить всю работу, которая записыва- лась на большие листы тщательно разлинованной бумаги – Spreadsheets – раз- вернутые листы, или по-русски – «простыни» «пустографки». При этом про- граммистский опыт Бриклина натолкнул его на мысль, что все эти однообраз- ные и нудные «жонглирования» цифрами неплохо было бы «поручить» ком- пьютеру. Своими соображениями он поделился с Робертом Фрэнкстоном, ко- торого эта идея тоже вдохновила, и они вместе приступили к ее реализации. В результате к весне 1979 года идея была воплощена в законченную программу, которую ее создатели назвали VisiCalc (Visible Calculator – визуальный каль- кулятор). Изначально она была написана для компьютера Apple-2 и в течение первого года после начала продаж (с августа 1979 года) разошлась тиражом около 100 тыс. экземпляров по цене 200 долл. за штуку. Первая же электронная таблица для компьютеров типа IBM PC была соз- дана Митчелом Кэпором спустя три года – в конце 1982 году – и называлась Lotus 1-2-3. При этом на ее рекламу было истрачено 1 млн. долл. Но эти рас- ходы окупились очень скоро – уже через полгода было продано 60 тыс. копий по цене 495 долл. за штуку. Одним из наиболее распространенных табличных процессоров в предшествующие десятилетия на территории бывшего Совет- ского Союза был SuperCalc фирмы Computer Associates – «облегченный», по сравнению с Lotus 1-2-3, вариант электронных таблиц для операционной сис- темы MS-DOS. В настоящее время, ведущее положение на мировом рынке программ- ных продуктов этого класса занимает электронная таблица Excel корпорации Microsoft, первая версия которой была выпущена в 1985 году. В 1993 году появилась 5-я версия Excel, ставшая первым приложением знаменитого пакета Microsoft Office. В дополнение к традиционным табличным вычислениям, в основном, в области экономики и финансов, Microsoft Excel позволяет строить различные диаграммы, графики и рисунки, проводить сложную статистиче- скую и математическую обработку данных, обмениваться информацией с наи- более распространенными базами данных, создавать Web-страницы и т.д. Средствами табличного процессора Microsoft Excel можно производить пол- 8 ный цикл обработки информации – от сбора, регистрации и, собственно, обра- ботки до отображения ее в наиболее презентабельном виде, достигая при этом поистине ошеломляющих результатов. Электронная таблица Microsoft Excel 2000, являясь одним из важнейших компонентов пакета Microsoft Office 2000, вышла на рынок в то время, когда предприятиями и компаниями все чаще используется Интернет и интрасети для коллективной работы с информацией. Поэтому пользователям нужны бо- лее удобные средства доступа к ней. Например, создание банков данных от- крывает перед всеми сотрудниками возможность доступа к корпоративной информации через сеть. Отделы информационных технологий ищут средства, которые избавили бы их от подготовки отчетов, а пользователи все чаще при- меняют Microsoft Excel в качестве клиентского интерфейса для работы с са- мыми разнообразными данными. С учетом этих тенденций в версии Microsoft Excel 2000 получили разви- тие три главных направления: 1. Сотрудничество и возможность совместного использования информации в Интернете. 2. Богатый аналитический инструментарий для принятия оптимальных реше- ний. 3. Простота использования программного обеспечения. 1.3. Организация данных в Microsoft Excel Любая программа, и Microsoft Excel в том числе, может обрабатывать данные только вполне определенного вида (формата, структуры). Основными объектами данных, с которыми может работать Microsoft Excel, являются следующие: 1) рабочая книга, 5) ячейка, 2) лист, 6) диапазон, 3) строка, 7) список. 4) столбец, Рабочая книга – это документ, который обрабатывается в Microsoft Ex- cel Каждая рабочая книга имеет собственное имя, построенное в соответствии со стандартами операционной системы Windows, и хранится в отдельном дис- ковом файле. По умолчанию, новым рабочим книгам Microsoft Excel присваи- вает имена Книга1, Книга2 и т.д. При их записи на диск к этому имени по умолчанию добавляется расширение .XLS, так что на диске будут записаны, соответственно, файлы Книга1.XLS, Книга2.XLS и т.д. В Microsoft Excel до- 9 пускается одновременная работа с несколькими рабочими книгами, при чем каждая из них открывается в отдельном окне документа. Количество одновре- менно открытых рабочих книг ограничивается только наличием свободных ресурсов компьютера. При этом активной, или текущей, может быть только одна из них. Каждая рабочая книга состоит из листов следующих типов: 1. Рабочий лист (или просто лист). Это самый распространенный тип листа. Он образует основное рабочее пространство пользователя, которое может состоять из таких компонентов: а) ячеек, организованных в виде строк и столбцов, б) одной или нескольких диаграмм, в) стандартных элементов управления, таких как кнопки, флажки, переключатели и т.д, и г) прочих связанных и внедренных объектов из других приложений. Все компоненты, кроме a), необязательны и могут отсутствовать на кон- кретном рабочем листе – они туда помещаются явным образом. По умолчанию рабочим листам присваиваются имена Лист1, Лист2 и т.д. 2. Лист диаграмм предназначен для размещения на нем диаграмм. На одном листе диаграмм может размещаться только одна диаграмма, в то время как на рабочем листе – произвольное их число Диаграммы можно сво- бодно перемещать между листами диаграмм и рабочими листами в любом направлении. Стандартные имена листов диаграмм – Диаграмма1, Диа- грамма2 и т.д. 3. Лист модуля. На нем записывается исходный текст программы на VBA (Visual Basic for Application). По умолчанию им присваиваются имена |