Автоматизация в excel реферат
Обновлено: 04.07.2024
Microsoft Office, самое популярное семейство офисных программных продуктов, включает в себя новые версии знакомых приложений, которые поддерживают технологии Internet, и позволяют создавать гибкие интернет-решения
Microsoft Office — семейство программных продуктов Microsoft, которое объединяет самые популярные в мире приложения в единую среду, идеальную для работы с информацией. В Microsoft Office входят текстовый процессор Microsoft Word, электронные таблицы Microsoft Excel, средство подготовки и демонстрации презентаций Microsoft PowerPoint и новое приложение Microsoft Outlook. Все эти приложения составляют Стандартную редакцию Microsoft Office. В Профессиональную редакцию входит также СУБД Microsoft Access.
Microsoft Excel – программа предназначенная для организации данных в таблице для документирования и графического представления информации.
Программа MSExcel применяется при создании комплексных документов в которых необходимо:
· использовать одни и те же данные в разных рабочих листах;
· изменить и восстанавливать связи.
Преимуществом MSExcel является то, что программа помогает оперировать большими объемами информации. рабочие книги MSExcel предоставляют возможность хранения и организации данных, вычисление суммы значений в ячейках. MsExcel предоставляет широкий спектр методов позволяющих сделать информацию простой для восприятия.
В наше время, каждому человеку важно знать и иметь навыки в работе с приложениями Microsoft Office, так как современный мир насыщен огромным количеством информацией, с которой просто необходимо уметь работать.
Более подробно в этой курсовой будет представлено приложение MSExcel, его функции и возможности. А также использование сценариев с их практическим применением.
1. Microsoft Excel
1.1 . Microsoft Excel . Понятия и возможности
Табличный процессор MS Excel (электронные таблицы) – одно из наиболее часто используемых приложений пакета MS Office, мощнейший инструмент в умелых руках, значительно упрощающий рутинную повседневную работу. Основное назначение MS Excel – решение практически любых задач расчетного характера, входные данные которых можно представить в виде таблиц. Применение электронных таблиц упрощает работу с данными и позволяет получать результаты без программирования расчётов. В сочетании же с языком программирования Visual Basic for Application (VBA), табличный процессор MS Excel приобретает универсальный характер и позволяет решить вообще любую задачу, независимо от ее характера.
Особенность электронных таблиц заключается в возможности применения формул для описания связи между значениями различных ячеек. Расчёт по заданным формулам выполняется автоматически. Изменение содержимого какой-либо ячейки приводит к пересчёту значений всех ячеек, которые с ней связаны формульными отношениями и, тем самым, к обновлению всей таблицы в соответствии с изменившимися данными.
Основные возможности электронных таблиц:
1. проведение однотипных сложных расчётов над большими наборами данных;
2. автоматизация итоговых вычислений;
3. решение задач путём подбора значений параметров;
4. обработка (статистический анализ) результатов экспериментов;
5. проведение поиска оптимальных значений параметров (решение оптимизационных задач);
6. подготовка табличных документов;
7. построение диаграмм (в том числе и сводных) по имеющимся данным;
8. создание и анализ баз данных (списков).
1.2. Основные элементы окна MS Excel
Основными элементами рабочего окна являются:
1. Строка заголовка (в ней указывается имя программы) с кнопками управления окном программы и окном документа (Свернуть, Свернуть в окно или Развернуть во весь экран, Закрыть);
2. Строка основного меню (каждый пункт меню представляет собой набор команд, объединенных общей функциональной направленностью) плюс окно для поиска справочной информации.
3. Панели инструментов (Стандартная, Форматирование и др.).
4. Строка формул, содержащая в качестве элементов поле Имя и кнопку Вставка функции (fx), предназначена для ввода и редактирования значений или формул в ячейках. В поле Имя отображается адрес текущей ячейки.
5. Рабочая область (активный рабочий лист).
6. Полосы прокрутки (вертикальная и горизонтальная).
7. Набор ярлычков (ярлычки листов) для перемещения между рабочими листами.
8. Строка состояния.
1.3 Структура электронных таблиц
Файл, созданный средствами MS Excel, принято называть рабочей книгой. Рабочих книг создать можно столько, сколько позволит наличие свободной памяти на соответствующем устройстве памяти. Открыть рабочих книг можно столько, сколько их создано. Однако активной рабочей книгой может быть только одна текущая (открытая) книга.
Рабочая книга представляет собой набор рабочих листов, каждый из которых имеет табличную структуру. В окне документа отображается только текущий (активный) рабочий лист, с которым и ведётся работа. Каждый рабочий лист имеет название, которое отображается на ярлычке листа в нижней части окна. С помощью ярлычков можно переключаться к другим рабочим листам, входящим в ту же рабочую книгу. Чтобы переименовать рабочий лист, надо дважды щёлкнуть мышкой на его ярлычке и заменить старое имя на новое или путём выполнения следующих команд: меню Формат, строка Лист в списке меню, Переименовать. А можно и, установив указатель мышки на ярлык активного рабочего листа, щёлкнуть правой кнопкой мыши, после чего в появившемся контекстном меню щёлкнуть по строке Переименовать и выполнить переименование. В рабочую книгу можно добавлять (вставлять) новые листы или удалять ненужные. Вставку листа можно осуществить путём выполнения команды меню Вставка, строка Лист в списке пунктов меню. Вставка листа произойдёт перед активным листом. Выполнение вышеизложенных действий можно осуществить и с помощью контекстного меню, которое активизируется нажатием правой кнопки мышки, указатель которой должен быть установлен на ярлычке соответствующего листа. Чтобы поменять местами рабочие листы нужно указатель мышки установить на ярлычок перемещаемого листа, нажать левую кнопку мышки и перетащить ярлычок в нужное место.
Рабочий лист (таблица) состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами и, далее, двухбуквенными комбинациями. Всего рабочий лист содержит 256 столбцов, поименованных от A до IV. Строки последовательно нумеруются числами от 1 до 65536.
На пересечении столбцов и строк образуются ячейки таблицы. Они являются минимальными элементами, предназначенными для хранения данных. Каждая ячейка имеет свой адрес. Адрес ячейки состоит из имени столбца и номера строки, на пересечении которых расположена ячейка, например, A1, B5, DE324. Адреса ячеек используются при записи формул, определяющих взаимосвязь между значениями, расположенными в разных ячейках. В текущий момент времени активной может быть только одна ячейка, которая активизируется щелчком мышки по ней и выделяется рамкой. Эта рамка в Excel играет роль курсора. Операции ввода и редактирования данных всегда производятся только в активной ячейке.
На данные, расположенные в соседних ячейках, образующих прямоугольную область, можно ссылаться в формулах как на единое целое. Группу ячеек, ограниченную прямоугольной областью, называют диапазоном. Наиболее часто используются прямоугольные диапазоны, образующиеся на пересечении группы последовательно идущих строк и группы последовательно идущих столбцов. Диапазон ячеек обозначают, указывая через двоеточие адрес первой ячейки и адрес последней ячейки диапазона, например, B5:F15. Выделение диапазона ячеек можно осуществить протягиванием указателя мышки от одной угловой ячейки до противоположной ячейки по диагонали. Рамка текущей (активной) ячейки при этом расширяется, охватывая весь выбранный диапазон.
Для ускорения и упрощения вычислительной работы Excel предоставляет в распоряжение пользователя мощный аппарат функций рабочего листа, позволяющих осуществлять практически все возможные расчёты.
В целом MS Excel содержит более 400 функций рабочего листа (встроенных функций). Все они в соответствии с предназначением делятся на 11 групп (категорий):
1. финансовые функции;
2. функции даты и времени;
3. арифметические и тригонометрические (математические) функции;
4. статистические функции;
5. функции ссылок и подстановок;
6. функции баз данных (анализа списков);
7. текстовые функции;
8. логические функции;
9. информационные функции (проверки свойств и значений);
Запись любой функции в ячейку рабочего листа обязательно начинается с символа равно (=). Если функция используется в составе какой-либо другой сложной функции или в формуле (мегаформуле), то символ равно (=) пишется перед этой функцией (формулой). Обращение к любой функции производится указанием её имени и следующего за ним в круглых скобках аргумента (параметра) или списка параметров. Наличие круглых скобок обязательно, именно они служат признаком того, что используемое имя является именем функции. Параметры списка (аргументы функции) разделяются точкой с запятой (;). Их количество не должно превышать 30, а длина формулы, содержащей сколько угодно обращений к функциям, не должна превышать 1024 символов. Все имена при записи (вводе) формулы рекомендуется набирать строчными буквами, тогда правильно введённые имена будут отображены прописными буквами.
1.4 Возможные ошибки при использовании функций в формулах
При работе с электронными таблицами важно не только уметь ими пользоваться, но и не совершать распространенных ошибок.
Исследования показали, что более половины людей, часто использующих Microsoft Excel в своей деятельности, держат на рабочем столе обычный калькулятор! Причина оказалась проста: чтобы произвести операцию суммирования двух или более ячеек для получения промежуточного результата (а такую операцию, как показывает практика, большинству людей приходится выполнять довольно часто), необходимо выполнить два лишних действия. Найти место в текущей таблице, где будет располагаться итоговая сумма, и активизировать операцию суммирования, нажав кнопку S (сумма). И лишь после этого можно выбрать те ячейки, значения которых предполагается просуммировать.
Следующие значения, называемые константами ошибок, Excel отображает в ячейках, содержащих формулы, в случае возникновения ошибок при вычислениях по этим формулам:
2. Анализ данных. Использование сценариев
2.1 Анализ данных
Данные — сведения:
— полученные путем измерения, наблюдения, логических или арифметических операций;
— представленные в форме, пригодной для постоянного хранения, передачи и (автоматизированной) обработки.
В Excelтип данных – тип, значения хранящегося в ячейке.
Когда данные вводятся на рабочий лист, Excel автоматически анализирует их определяет тип данных. Тип данных, присваиваемый ячейке по умолчанию, определяет способ анализа данных, который можно применять к данной ячейке.
Типы данных:
4. Числовые символы
Анализ данных — область информатики, занимающаяся построением и исследованием наиболее общих математических методов и вычислительных алгоритмов извлечения знаний из экспериментальных (в широком смысле) данных.
Анализ данных – сравнение различной информации.
Работа с таблицей не ограничивается простым занесением в нее данных. Трудно представить себе область, где бы ни требовался анализ этих данных.
MsExcel представляет широкие возможности для проведения анализа данных, находящихся в списке. К средствам анализа относятся :
· Обработка списка с помощью различных формул и функций;
· Построение диаграмм и использование карт MsExcel;
· Проверка данных рабочих листов и рабочих книг на наличие ошибок;
· Структуризация рабочих листов;
· Автоматическое подведение итогов (включая мстер частичных сумм);
· Специальные средства анализа выборочных записей и данных – подбор параметра, поиск решения, сценарии и др.
2.2 Сценарии
Одно из главных преимуществ анализа данных – предсказание будущих событий на основе сегодняшней информации.
Сценарий — это набор значений, которые в приложении Microsoft Office Excel сохраняются и могут автоматически подставляться в лист. Сценарии можно использовать для прогноза результатов моделей расчетов листа. Существует возможность создать и сохранить в листе различные группы значений, а затем переключаться на любой из этих новых сценариев, чтобы просматривать различные результаты. Или можно создать несколько входных наборов данных (изменяемых ячеек) для любого количества переменных и присвоить имя каждому набору. По имени выбранного набора данных MSExcel сформирует на рабочем листе результаты анализа. Кроме этого, диспетчер сценариев позволяет создать итоговый отчет по сценариям, в котором отображаются результаты подстановки различных комбинаций входных параметров.
При разработке сценария данные на листе будут меняться. По этой причине перед началом работы со сценарием придется создать сценарий, сохраняющий первоначальные данные, или же создать копию листа Excel.
Все сценарии создаются в диалоговом окне Добавление сценария. Прежде всего необходимо указать ячейки для отображения прогнозируемых изменений. Ссылки на ячейки отделяются друг от друга двоеточием или точкой с запятой. Затем в диалоговом окне Значение ячеек сценария каждой ячейке присваивается новое значение. Эти значения используются при выполнении соответствующего сценария. После ввода значений генерируется сценарий. При выборе другого сценария, значения в ячейках меняются так, как указано в сценарии.
Для защиты сценария используются флажки, которые выставляются в нижней части диалогового окна Добавление сценария. Флажок Запретить изменения не позволяет пользователям изменить сценарий. Если активизирован флажок Скрыть, то пользователи не смогут, открыв лист, увидеть сценарий. Эти опции применяются только тогда, когда установлена защита листа.
Если требуется одновременно сравнить несколько сценариев, то можно создать Итоговый отчет, щелкнув в диалоговом окне по кнопке Отчет .
Во многих экономических задачах результат расчета зависит от нескольких параметров, которыми можно управлять.
Диспетчер сценариев открывается командой Сервис/Сценарии (рис. 1). В окне диспетчера сценариев с помощью соответствующих кнопок можно добавить новый сценарий, изменить, удалить или вывести существующий, а также – объединить несколько различных сценариев и получить итоговый отчет по существующим сценариям.
2.3 Пример расчета внутренней скорости оборота инвестиций
Исходные данные: затраты по проекту составляют 700 млн. руб. Ожидаемые доходы в течение последующих пяти лет, составят: 70, 90, 300, 250, 300 млн. руб. Рассмотреть также следующие варианты (затраты на проект представлены со знаком минус):
— -600; 50;100; 200; 200; 300;
— -650; 90;120;200;250; 250;
— -500, 100,100, 200, 250, 250.
Рис 1. Окно Диспетчер сценариев
Для вычисления внутренней скорости оборота инвестиции (внутренней нормы доходности) используется функция ВСД (в ранних версиях — вндох):
ВСД - Возвращает внутреннюю ставку доходности для ряда потоков денежных средств, представленных их численными значениями. Эти денежные потоки не обязательно должны быть равными по величине, как в случае аннуитета. Однако они должны иметь место через равные промежутки времени, например ежемесячно или ежегодно.
Внутренняя ставка доходности — это процентная ставка, принимаемая для инвестиции, состоящей из платежей (отрицательные величины) и доходов (положительные величины), которые осуществляются в последовательные и одинаковые по продолжительности периоды.
ВСД (Значения; Предположения )
Значения — это массив или ссылка на ячейки, содержащие числа, для которых требуется подсчитать внутреннюю ставку доходности.
· Значения должны содержать, по крайней мере, одно положительное и одно отрицательное значение.
· ВСД использует порядок значений для интерпретации порядка денежных выплат или поступлений. Убедитесь, что значения выплат и поступлений введены в правильном порядке.
· Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, то такие значения игнорируются.
Предположение — это величина, о которой предполагается, что она близка к результату ВСД.
В нашем случае функция для решения задачи использует только
аргумент Значения, один из которых обязательно отрицателен (затраты по проекту). Если внутренняя скорость оборота инвестиций окажется больше рыночной нормы доходности, то проект считается экономически целесообразным. В противном случае проект должен быть отвергнут.
Решение приведено на рис. 2. Формулы для расчета:
• в ячейке В14:
• в ячейке С14:
Рис 2. Расчет внутренней скорости оборота инвестиций
2. Рассмотрим этот пример для всех комбинаций исходных данных. Для создания сценария следует использовать команду Сервис | Сценарии | кнопка Добавить (рис. 3). После нажатия на кнопку ОК появляется возможность внесения новых значений для изменяемых ячеек (рис. 4).
Для сохранения результатов по первому сценарию нет необходимости редактировать значения ячеек— достаточно нажать кнопку ОК ( для подтверждения значений, появившихся по умолчанию, и выхода в окно Диспетчер сценариев.
Рис 3. Добавление сценария для комбинации исходных данных
Рис 4. Окно для изменения значений ячеек.
3. Для добавления к рассматриваемой задаче новых сценариев достаточно нажать кнопку Добавить в окне Диспетчер сценариев и повторить вышеописанные действия, изменив значения в ячейках исходных данных (рис. 5).
Нажав кнопку Вывести, можно просмотреть на рабочем листе
результаты расчета для соответствующей комбинации исходных значений.
Рис 5. Окно Диспетчер сценариев с добавленными сценариями
4. Для получения итогового отчета по всем добавленным сценариям следует нажать кнопку Отчет в окне диспетчера сценариев. В появившемся окне отчет по сценарию выбрать необходимый тип отчета и дать ссылки на ячейки, в которых вычисляются результирующие функции. При нажатии на кнопку ОК на соответствующий лист рабочей книги выводится отчет по сценариям (рис. 6).
Рис 6. Отчет по сценариям расчета скорости оборота инвестиций
Характерной чертой современности является стремительный научно-технический прогресс, что требует от менеджеров и бизнесменов значительного повышения ответственности за качество принятия решений. Это основная причина, которая обусловливает необходимость научного принятия управленческих решений.
При помощи этого продукта можно анализировать большие массивы данных. В Excel можно использовать более 400 математических, статистических, финансовых и других специализированных функций, связывать различные таблицы между собой, выбирать произвольные форматы представления данных, создавать иерархические структуры.
Программа MS Excel, являясь лидером на рынке программ обработки электронных таблиц, определяет тенденции развития в этой области. Вплоть до версии 4.0 программа Excel представляла собой фактический стандарт с точки зрения функциональных возможностей и удобства работы. Теперь на рынке появились гораздо более новые версии, которые содержат много улучшений и приятных неожиданностей.
Список литературы
Исследование основных возможностей работы с Microsoft Excel. Создание приложения Windows Forms. Создание и заполнение нового документа Microsoft Excel. Чтение данных с документа Microsoft Excel. Создание графиков и диаграмм при помощи "Мастера диаграмм".
| Рубрика | Программирование, компьютеры и кибернетика |
| Вид | лабораторная работа |
| Язык | русский |
| Дата добавления | 24.04.2017 |
| Размер файла | 2,5 M |
Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.
Лабораторная работа: Автоматизация Microsoft Office Excel
1. Вводная часть
В этой работе будет рассмотрена работа с программой, входящей в состав известного пакета программ Microsoft Office. В частности, речь пойдёт о Microsoft Excel и некоторых возможностях по автоматизации работы с ним. Для демонстрации работы будет использована версия Excel 2010. В данной работе будет рассмотрено следующее:
1. Автоматическое создание и заполнение нового документа.
2. Открытие и изменение документа.
3. Чтение данных из документа Excel.
4. Сохранение документа Excel.
5. Установка парольной защиты на документ Excel.
6. Автоматическое заполнение документа Excel (функции автоматического заполнения ячеек Excel).
7. Объединение ячеек на листе документа Excel.
9. Работа с приложением, книгами и листами документа Excel.
10. Некоторые другие возможности.
2. Создание приложения Windows Forms
Запускаем Visual Studio 2010, откроется Начальная страница:
Рис. 2. 1. Создание нового проекта
Рис. 2. 2. Окно создания нового проекта
Рис. 2. 3. Вводим данные нового проекта приложения Windows Forms
После нажатия клавиши ОК мы увидим сформированный проект и исходный код приложения Windows Forms (не пустого изначально).
Рис. 2. 4. Обозреватель решений: состав проекта приложения Windows Forms сформированного средой разработки
Теперь, можно откомпилировать созданную программу, нажав клавишу F5 (Отладка -> Начать отладку или нажав на иконку . Тем самым мы запускаем приложение в режиме отладки (и производим компиляцию debug-версии программы) (Debug выбрано изначально).
Рис. 2. 5. Запуск приложения Windows Forms по конфигурации Debug
3. Модификация приложения Windows Forms: создание и заполнение нового документа Microsoft Excel
Что представляет из себя полная автоматизация документа? Заполнение документа и всех важных полей, например фамилий, дат и названий. Разумеется, для редактирования правописания или более глубокого изменения документа надо использовать сам Word. Автоматизация части документа подходит в основном для рутинного заполнения однотипных документов, будь то всякие бланки или ведомости. Применений можно найти массу.
Первое, что может сделать стороннее приложение, это создать полностью готовый документ получая данные, непосредственно из приложения, а следовательно и из базы данных или с удалённого сервера. Рассмотрим такой случай.
Для начала изменим размер нашей единственной формы. Для этого можно потянуть за уголок в нужном направлении на странице визуального представления формы 1 . Но также размер можно менять на панели свойств этой формы. Для этого нужно поменять значение размера в пикселях (высоту и ширину) в поле Size.
ПРИМЕЧАНИЕ № 1: Для перехода на визуальное представление формы, необходимо двойным нажатием в обозревателе решений нажать на значок формы () или выбрать вкладку на панели вкладок с именем .cs [Конструктор].
Задаём следующие параметры формы на панели Свойства:
(Name)
изменим с Form1.cs 2 на LWP13Main
^ Поменяем внутреннее имя формы.
^ Поменяем заголовок формы (то, что отображается в шапке приложения слева).
изменим изображение (иконку) приложения
^ Необходим файл значка *.ico.
изменим со значений 300; 300 на 500; 400
^ Поменяем размер формы.
ПРИМЕЧАНИЕ № 2: Для того, чтобы поменять имя файла нашей формы, необходимо выполнить следующее: выделить в обозревателе решений значок формы () и нажать правую кнопку мыши, затем выбрать Переименовать. Ввести необходимое новое имя, СОХРАНЯЯ расширение *.cs. После смены имени, автоматически поменяются имена проассоциированных непосредственно с формой файлов:
Также, поместим первую группу элементов для работы с автоматическим созданием нового документа. Получим нечто подобное:
Рис. 3. 1. Модифицированная форма приложения и расстановка первой группы элементов
Здесь у нас две кнопки Button, один GroupBox, три TextBox, два NumericUpDown и ToolTip.
Автоматизация решения задач пользователя в MS Excel ( реферат , курсовая , диплом , контрольная )
Выполнила: студентка гр.101 851
Проверил: ст. преподаватель Стефанович Н.В.
- Введение
- Теоретическая часть
- 1. Параметры форматирования ячейки. Применение Автоформата. Создание и использование стилей форматирования
- 2. Функции рабочего листа даты и времени. Использование Мастера функций
- Практическая часть
- 1. Построение графиков функций
- 2. Построение графика периодичной кусочно-ломаной функции с условиями
- 3. Выполнение экономических расчетов средствами MS Excel
- 4. Анализ данных средствами MS Excel
- 5. Решение задачи математического программирования средствами MS Excel
- Заключение
- Список использованных источников
Электронные таблицы (табличные процессоры) — прикладное программное обеспечение общего назначения, предназначенное для обработки различных данных, представимых в табличной форме. Главное достоинство электронной таблицы состоит в том, что в ней можно мгновенно пересчитывать все данные, связанные формульными зависимостями, при изменении любого значения. Так как я будущий экономист, для меня умение работать с электронными таблицами является неотъемлемой частью моей будущей профессии. Поэтому в ходе расчетно-графической работы я хочу приобрести практические навыки работы с электронными таблицами Microsoft Excel.
табличный процессор стиль excel
Теоретическая часть
1. Параметры форматирования ячейки. Применение Автоформата. Создание и использование стилей форматирования
Стиль ячеек включает в себя определенный набор параметров форматирования ячейки — шрифт с заданными характеристиками, цвет заливки и шрифта, наличие и тип границы ячейки, числовой формат ячейки. Вызывается нажатием правой клавиши мыши на ячейке и выбором пункта Формат ячеек…
Автоформат позволяет автоматически применить к текущей таблице параметры форматирования, включающие шрифты, затенение и границы.
Чтобы применить автоформат к таблице следует:
1. Выделить диапазон, содержащий таблицу.
2. Выполнить ФорматАвтоформат. Откроется окно Автоформат.
3. Выделить желаемый формат в списке форматов и просмотреть образец.
Существует несколько способов создания собственных стилей с помощью диалогового окна Стиль (меню Формат, команда Стили).
Создание нового стиля на основе существующего формата
1. В меню Формат выберите команду Стиль.
2. В поле Имя стиля введите имя нового стиля.
Создание стиля с нуля
1. В меню Формат выберите команду Стиль.
2. Введите имя нового стиля в поле Имя стиля.
4. В группе Стиль включает укажите нужные атрибуты стиля.
Изменение существующего стиля
1. В меню Формат выберите команду Стиль.
3. Измените стиль, используя вкладки диалогового окна Формат ячеек.
4. Дважды нажмите кнопку OK.
Использование существующих стилей
Существующий стиль, либо предопределенный, либо созданный, можно быстро использовать в своей книге. Стандартные стили, такие как Финансовый, Денежный и Процентный, также легко доступны на панели инструментов Форматирование. Другими стилями можно воспользоваться с помощью команды Стиль меню Формат.
1. Выберите ячейку или диапазон ячеек, к которому нужно применить требуемый стиль.
2. В меню Формат выберите команду Стиль.
3. В раскрывающемся списке Имя стиля щелкните применяемый стиль.
2. Функции рабочего листа даты и времени. Использование Мастера функций
Excel хранит дату в виде последовательных чисел (сериальных чисел), а время в виде десятичной части этого значения (время является частью даты). Значения даты и времени представляются числами, поэтому их можно использовать в вычислениях.
Функции даты и времени
ДАТА Возвращает заданную дату в числовом формате Microsoft Excel.
ДАТАЗНАЧ Преобразует дату из текстового формата в числовой.
ДЕНЬ Преобразует дату в числовом формате в день месяца.
ДНЕЙ360 Вычисляет количество дней между двумя датами на основе 360-дневного года.
ДАТАМЕС Возвращает дату в числовом формате, отстоящую на заданное число месяцев вперед или назад от начальной даты.
КОНМЕСЯЦА Возвращает дату в числовом формате для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев.
ЧАС Преобразует дату в числовом формате в часы.
МИНУТЫ Преобразует дату в числовом формате в минуты ["https://referat.bookap.info", 18].
МЕСЯЦ Преобразует дату в числовом формате в месяцы.
ЧИСТРАБДНИ Возвращает количество рабочих дней между двумя датами.
ТДАТА Возвращает текущую дату и время в числовом формате.
СЕКУНДЫ Преобразует дату в числовом формате в секунды.
ВРЕМЯ Возвращает заданное время в числовом формате.
ВРЕМЗНАЧ Преобразует время из текстового формата в числовой формат.
СЕГОДНЯ Возвращает числовой формат текущей даты.
ДЕНЬНЕД Преобразует дату в числовом формате в день недели.
НОМНЕДЕЛИ Преобразует числовое представление в число, которое указывает, на какую неделю года приходится указанная дата.
РАБДЕНЬ Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней.
ГОД Преобразует дату в числовом формате в год.
ДОЛЯГОДА Возвращает долю года, которую составляет количество дней между начальной и конечной датами.
После щелчка на кнопке Ok (или нажатия клавиши ) имя выбранной функции заносится в строку формул вместе со скобками, ограничивающими список аргументов, и одновременно открывается окно Аргументы функции.
В заголовке окна отображается имя функции. В верхней части располагаются поля ввода аргументов. Наименования обязательных аргументов выделены жирным шрифтом. Под полями приводится краткое описание функции. Ниже описывается назначение аргументов (это описание обновляется при переходе к очередному полю аргумента). В нижней части — результат вычисления и кнопки подтверждения и отмены ввода функции.
Практическая часть
1. Построение графиков функций
Для заданных функций необходимо:
1. Построить электронную таблицу (одну для обеих функций) для вычисления значений функций в заданном диапазоне с шагом 0,1. Для ввода значений аргумента в таблицу использовать механизм Автозаполнения (Правка/Заполнить /Прогрессия).
2. Вычислить значения функций в заданном диапазоне.
Аргумент и значения функций выводить в таблице с 2 знаками после запятой.
3. На основании табличных данных, построить графики функций на заданном диапазоне (тип диаграммы — точечная, два графика на одной диаграмме).
На рисунке 1 показано выполненное задание № 1. Построение графиков функции. Два графика на одной диаграмме.
2. Построение графика периодичной кусочно-ломаной функции с условиями
Для заданной периодичной кусочно-ломаной функции необходимо:
1. Построить электронную таблицу для вычисления значений кусочно-ломаной функции в диапазоне двух периодов, с заданным шагом. шаг h=0,25
При построении таблицы значений функции использовать встроенные функции Microsoft Excel ОСТАТ и ЕСЛИ.
2. По табличным данным с помощью Мастера диаграмм построить график функции (тип диаграммы — точечная).
На рисунке 2 показано выполненное практическое задание № 2. Построение графика периодичной кусочно-ломаной функции
3. Выполнение экономических расчетов средствами MS Excel
Фирма приобрела технологическую линию за начальную стоимость Sn.
Срок службы технологической линии составляет K лет.
Остаточная стоимость технологической линии в конце срока службы — Sk.
Построить (по варианту) таблицу для расчета суммы амортизационных отчислений и остаточной стоимости линии по годам, если применяется ускоренная амортизация по правилу суммы лет.
Срок эксплуатации, лет
В таблице 1 приведены данные для расчета амортизации.
На рисунке 3 показано выполнение задания № 3. Здесь показано, как с помощью MS Excel можно выполнять экономический расчеты.
4. Анализ данных средствами MS Excel
Используя команду Подбор параметра из меню Сервис, найти значение Х, при котором функция возвратит заданное значение.
Результат отобразить в виде таблицы:
В таблице 2 приведены данные для решения задачи с использованием команды Подбор данных Рисунок 4
На рисунке 5 показан результата подбора параметра Рисунок 5
На рисунке 4 показан процесс подбора параметра в MS Excel
5. Решение задачи математического программирования средствами MS Excel
На рисунке 6 показан принцип работы надстройки MS Excel Поиск решения
Используя надстройку Microsoft Excel Поиск решения, определить размеры бака (R, h), имеющего форму цилиндра с крышкой объемом 8000 см 3 , чтобы площадь материала, пошедшего на его изготовление, была минимальной.
На рисунке 7 показан отчет по результатам поиска решения к задаче № 5
Заключение
В ходе расчетно-графической работы я приобрела практические навыки работы с электронными таблицами в Microsoft Excel.
Список использованных источников
Excel располагает мощнейшей, но в то же время очень редко используемой, возможностью создавать автоматически выполняющиеся последовательности действий с помощью макросов. Макрос – идеальный выход, если Вы имеете дело с однотипной задачей, которая повторяется множество раз. Например, обработка данных или форматирование документов по стандартизированному шаблону. При этом Вам не требуется знание языков программирования.
Вам уже любопытно, что такое макрос, и как он работает? Тогда смелей вперёд – далее мы шаг за шагом проделаем весь процесс создания макроса вместе с Вами.
Что такое Макрос?
Макрос в Microsoft Office (да, этот функционал работает одинаково во многих приложениях пакета Microsoft Office) – это программный код на языке программирования Visual Basic for Applications (VBA), сохранённый внутри документа. Чтобы было понятнее, документ Microsoft Office можно сравнить со страницей HTML, тогда макрос – это аналог Javascript. То, что Javascript умеет делать с данными в формате HTML, находящимися на web-странице, очень похоже на то, что макрос может делать с данными в документе Microsoft Office.
Макросы способны выполнить практически любые действия в документе, которые Вы только можете пожелать. Вот некоторые из них (очень малая часть):
- Применять стили и форматирование.
- Выполнять различные операции с числовыми и текстовыми данными.
- Использовать внешние источники данных (файлы баз данных, текстовые документы и т.д.)
- Создавать новый документ.
- Проделывать все вышеперечисленные действия в любой их комбинации.
Создание макроса – практический пример
Для примера возьмём самый обычный файл CSV. Это простая таблица 10х20, заполненная числами от 0 до 100 с заголовками для столбцов и строк. Наша задача превратить этот набор данных в презентабельно отформатированную таблицу и сформировать итоги в каждой строке.
Как уже было сказано, макрос – это код, написанный на языке программирования VBA. Но в Excel Вы можете создать программу, не написав и строчки кода, что мы и сделаем прямо сейчас.
Чтобы создать макрос, откройте View (Вид) > Macros (Макросы) > Record Macro (Запись макроса…)
Дайте своему макросу имя (без пробелов) и нажмите ОК.
Начиная с этого момента, ВСЕ Ваши действия с документом записываются: изменения ячеек, пролистывание таблицы, даже изменение размера окна.
Excel сигнализирует о том, что включен режим записи макроса в двух местах. Во-первых, в меню Macros (Макросы) – вместо строки Record Macro (Запись макроса…) появилась строка Stop Recording (Остановить запись).
Во-вторых, в нижнем левом углу окна Excel. Иконка Стоп (маленький квадратик) указывает на то, что включен режим записи макроса. Нажатие на неё остановит запись. И наоборот, когда режим записи не включен, в этом месте находится иконка для включения записи макроса. Нажатие на неё даст тот же результат, что и включение записи через меню.
Теперь, когда режим записи макроса включен, давайте займёмся нашей задачей. Первым делом, добавим заголовки для итоговых данных.
Далее, введите в ячейки формулы в соответствии с названиями заголовков (даны варианты формул для англоязычной и русифицированной версии Excel, адреса ячеек – всегда латинские буквы и цифры):
Теперь выделите ячейки с формулами и скопируйте их во все строки нашей таблицы, потянув за маркер автозаполнения.
После выполнения этого действия в каждой строке должны появиться соответствующие итоговые значения.
Далее, мы подведем итоги для всей таблицы, для этого делаем ещё несколько математических действий:
- =SUM(L2:L21) или =СУММ(L2:L21)
- =AVERAGE(B2:K21) или =СРЗНАЧ(B2:K21) — для расчёта этого значения необходимо взять именно исходные данные таблицы. Если взять среднее значение из средних по отдельным строкам, то результат будет другим.
- =MIN(N2:N21) или =МИН(N2:N21)
- =MAX(O2:O21) или =МАКС(O2:O21)
- =MEDIAN(B2:K21) или =МЕДИАНА(B2:K21) — считаем, используя исходные данные таблицы, по причине указанной выше.
Теперь, когда с вычислениями закончили, займёмся форматированием. Для начала для всех ячеек зададим одинаковый формат отображения данных. Выделите все ячейки на листе, для этого воспользуйтесь комбинацией клавиш Ctrl+A, либо щелкните по иконке Выделить все, которая находится на пересечении заголовков строк и столбцов. Затем нажмите Comma Style (Формат с разделителями) на вкладке Home (Главная).
Далее, изменим внешний вид заголовков столбцов и строк:
- Жирное начертание шрифта.
- Выравнивание по центру.
- Заливка цветом.
И, наконец, настроим формат итоговых значений.
Вот так это должно выглядеть в итоге:
Если Вас все устраивает, остановите запись макроса.
Поздравляем! Вы только что самостоятельно записали свой первый макрос в Excel.
Чтобы использовать созданный макрос, нам нужно сохранить документ Excel в формате, который поддерживает макросы. Для начала необходимо удалить все данные из созданной нами таблицы, т.е. сделать из неё пустой шаблон. Дело в том, что в дальнейшем, работая с этим шаблоном, мы будем импортировать в него самые свежие и актуальные данные.
Чтобы очистить все ячейки от данных, щёлкните правой кнопкой мыши по иконке Выделить все, которая находится на пересечении заголовков строк и столбцов, и из контекстного меню выберите пункт Delete (Удалить).
Теперь наш лист полностью очищен от всех данных, при этом макрос остался записан. Нам нужно сохранить книгу, как шаблон Excel с поддержкой макросов, который имеет расширение XLTM.
Важный момент! Если Вы сохраните файл с расширением XLTX, то макрос в нём работать не будет. Кстати, можно сохранить книгу как шаблон Excel 97-2003, который имеет формат XLT, он тоже поддерживает макросы.
Когда шаблон сохранён, можно спокойно закрыть Excel.
Выполнение макроса в Excel
Прежде чем раскрыть все возможности созданного Вами макроса, считаю правильным обратить внимание на пару важных моментов, касающихся макросов в целом:
- Макрос может нанести вред.
- Ещё раз прочти предыдущий пункт.
VBA-код обладает очень большими возможностями. В частности, он может выполнять операции с файлами за пределами текущего документа. Например, макрос может удалять или изменять любые файлы в папке Мои документы. По этой причине запускайте и разрешайте выполнение макросов только из источников, которым Вы доверяете.
Чтобы запустить наш макрос, форматирующий данные, откройте файл шаблона, который мы создали в первой части этого урока. Если у Вас стандартные настройки безопасности, то при открытии файла сверху над таблицей появится предупреждение о том, что запуск макросов отключен, и кнопка, чтобы включить их выполнение. Так как шаблон мы сделали самостоятельно и себе мы доверяем, то нажимаем кнопку Enable Content (Включить содержимое).
Следующим шагом, мы импортируем последний обновлённый набор данных из файла CSV (на основе такого файла мы создавали наш макрос).
При выполнении импорта данных из CSV-файла, возможно, Excel попросит Вас настроить некоторые параметры для правильной передачи данных в таблицу.
Когда импорт будет закончен, зайдите в меню Macros (Макросы) на вкладке View (Вид) и выберите команду View Macros (Макросы).
В открывшемся диалоговом окне мы увидим строку с именем нашего макроса FormatData. Выберите его и нажмите Run (Выполнить).
Когда макрос начнёт работать, Вы увидите, как табличный курсор прыгает с ячейки на ячейку. Через несколько секунд с данными будут проделаны те же операции, что и при записи макроса. Когда всё будет готово, таблица должна выглядеть так же, как и оригинал, который мы форматировали вручную, только с другими данными в ячейках.
Заглянем под капот: Как работает макрос?
Как уже не раз упоминалось, макрос — это программный код на языке программирования Visual Basic for Applications (VBA). Когда Вы включаете режим записи макроса, Excel фактически записывает каждое сделанное Вами действие в виде инструкций на языке VBA. По-простому, Excel пишет программный код вместо Вас.
Чтобы увидеть этот программный код, нужно в меню Macros (Макросы) на вкладке View (Вид) кликнуть View Macros (Макросы) и в открывшемся диалоговом окне нажать Edit (Изменить).
Откроется окно Visual Basic for Applications, в котором мы увидим программный код записанного нами макроса. Да, Вы правильно поняли, здесь этот код можно изменить и даже создать новый макрос. Те действия, которые мы совершали с таблицей в этом уроке, вполне можно записать с помощью автоматической записи макроса в Excel. Но более сложные макросы, с тонко настроенной последовательностью и логикой действий требуют программирования вручную.
Добавим ещё один шаг к нашей задаче…
Представьте, что наш исходный файл с данными data.csv создаётся автоматически каким-то процессом и сохраняется на диске всегда в одном и том же месте. Например, C:\Data\data.csv – путь к файлу с обновляемыми данными. Процесс открытия этого файла и импорта данных из него тоже можно записать в макрос:
- Откройте файл шаблона, в котором мы сохранили макрос — FormatData.
- Создайте новый макрос с именем LoadData.
- В процессе записи макроса LoadData сделайте импорт данных из файла data.csv – как мы это делали в предыдущей части урока.
- Когда импорт будет завершён, остановите запись макроса.
- Удалите все данные из ячеек.
- Сохраните файл, как шаблон Excel с поддержкой макросов (расширение XLTM).
Таким образом, запустив этот шаблон, Вы получаете доступ к двум макросам – один загружает данные, другой их форматирует.
Если есть желание заняться программированием, Вы можете объединить действия этих двух макросов в один – просто скопировав код из LoadData в начало кода FormatData.
Так как таблицы часто содержат повторяющиеся или однотипные данные, программа Excel содержит средства автоматизации ввода. К числу предоставляемых средств относятся: автозавершение, автозаполнение числами и автозаполнение формулами.
Автозавершение.Для автоматизации ввода текстовых данных используется метод автозавершения. Его применяют при вводе в ячейки одного столбца рабочего листа текстовых строк, среди которых есть повторяющиеся. В ходе ввода текстовых данных в очередную ячейку программа Excel проверяет соответствие введенных символов строкам, имеющемся в этом столбце выше. Если обнаружено однозначное совпадение, введенный текст автоматически дополняется. Нажатие клавиши ENTER подтверждает операцию автозавершения, в противном случае ввод можно продолжать, не обращая внимания на предлагаемый вариант.
Можно прервать работу средства автозавершения, оставив в столбце пустую ячейку. И наоборот, чтобы использовать возможности средства автозавершения, заполненные ячейки должны идти подряд, без промежутков между ними.
Если ячейка содержит число (в том числе дату, денежную сумму), то при перетаскивании маркера происходит копирование ячеек или их заполнение арифметической прогрессией. Для выбора способа автозаполнения следует производить специальное перетаскивание с использованием правой кнопки мыши.
Пусть, например, ячейка А1 содержит число 1. Наведите указатель мыши на маркер заполнения, нажмите правую кнопку мыши, и перетащите маркер заполнения так, чтобы рамка охватила ячейки А1, 81 и С1, и отпустите кнопку мыши. Если теперь выбрать в открывшемся меню пункт Копировать ячейки, все ячейки будут содержать число 1. Если же выбрать пункт Заполнить, то в ячейках окажутся числа 1,2 и 3.
Чтобы точно сформулировать условия заполнения ячеек, следует дать команду Правка > Заполнить > Прогрессия. В открывшемся диалоговом окне Прогрессия выбирается тип прогрессии, величина шага и предельное значение. После щелчка на кнопке ОК программа Excel автоматически заполняет ячейки в соответствии с заданными правилами.
12.1. Создание электронных таблиц Microsoft Excel __ __ 309
Автозаполнение формулами.Эта операция выполняется так же, как автозаполнение числами. Ее особенность заключается в необходимости копирования ссылок на другие ячейки. В ходе автозаполнения во внимание принимается характер ссылок в формуле: относительные ссылки изменяются в соответствии с относительным расположением копии и оригинала, абсолютные остаются без изменений.
Для примера предположим, что значения в третьем столбце рабочего листа (столбце С) вычисляются как суммы значений в соответствующих ячейках столбцов А и В. Введем в ячейку С1 формулу =А1+В1. Теперь скопируем эту формулу методом автозаполнения во все ячейки третьего столбца таблицы. Благодаря относительной адресации формула будет правильной для всех ячеек данного столбца.
В таблице 12.1 приведены правила обновления ссылок при автозаполнении вдоль строки или вдоль столбца.
Таблица 12.1. Правила обновления ссылок при автозаполнении
Читайте также: