ЛР EXCEL1. Практическая работа 1 Построение и настройка диаграмм. Макросы Задание Ввод, редактирование и форматирование данных
 Скачать 2.33 Mb.
|
ПРОГРАММА MICROSOFT EXCELПрактическая работа № 1Построение и настройка диаграмм. МакросыЗадание 1. Ввод, редактирование и форматирование данных Группа специалистов получила заказ на издание монографии. Распределение работы и вознаграждение для каждого автора приведены в таблице на рис. 1. Используя МS Excel, создайте таблицу, введите исходные данные, выполните необходимые расчеты. (Внимание! В таблице на Рис 1 показано как необходимо делать, а в Таблице на Рис 2 показано, что должно получиться). Основные правила: 1.Ввод формул начинайте со знака «=». Внутри формулы не допускаются пробелы, а в адресах используются только латинские буквы. 2.Завершайте ввод в ячейку и ее редактирование нажатием клавиши Enterиликнопки ввод  для сохранения выполненных изменений. Для форматирования данных необходимо активизировать ячейку с данными или выделить блок ячеек. Основные команды форматирования вынесены на закладку ленты Главная.3.Заголовок таблицы введите в ячейку, расположенную выше строки, с которой начинается таблица. Для центрирования заголовка таблицы по ширине таблицы выделите блок ячеек с заголовком в 2-х строках от первого до последнего столбца таблицы (А1:Е2) и выполните команду Объединить и поместить в центре (вкл. Главная, область Выравнивание  ).Для создания красивой шапки таблицы следует выделить диапазон ячеек: А3:Е3, вызвать на выделенном контекстное меню, выполнить команду:Формат ячеек-Выравнивание, выполнить настройки как на рисунке:  4.Для выбора функции =СУММ() использовать вкладку Главная группу Редактирование далее кн. Сумма. Ввод формул в столбцы производится через копирование формулы из ячейки Е4 вниз до ячейки Е9.  Рис.1. Исходные данные для расчета  Рис.2. Образец для форматирования Выполните форматирование в соответствии с образцом, приведенном на рис. 2. Задайте тип данных – денежный в соответствующих столбцах. Сохраните табличный документ в файле Лаб1. Задание 2. Копирование и перенос фрагментов Основные правила: Выделите фрагмент таблицы, который необходимо скопировать или перенести. Выполните команду на панели Главная -Копировать для копирования или Вырезать для переноса. Активизируйте ячейку – позицию вставки фрагмента (левый верхний угол). Выполните команду Вставить. Откройте файл Лаб1 (Задание 1.).   Для определения суммарного количества страниц, написанных каждым автором, а также для анализа работы коллектива авторов на том же листе, на котором находится таблица рис.2 (Задание 1.).составьте еще одну таблицу, приведенную на рис.4 (на рис.3 показано как необходимо делать, а на рис.4, что должно получиться). В максимально возможной степени используйте копирование уже существующих фрагментов.Обратите внимание: в формулах  в адресе ячеек С18 и Е18 появились значки доллара($). Это переводит ссылки из относительных, которые меняется при копировании в зависимости от нового положения формулы в абсолютные, которые при копировании не меняется, т.е. всегда остаются $С$18 и $Е$18. Чтобы изменить тип ссылки ее надо выделить в формуле и нажать функциональную клавишу F4 Выполните форматирование данных в соответствии с образцом, приведенном на рис.4. Сохраните табличный документ в файл Лаб1.  Рис. 3. Общий вид таблицы и формулы для расчета  Рис. 4. Образец для форматирования. Задание 3. Построение диаграмм Основные правила: Для создания диаграммы необходимо выделить блок данных, на основании которых строится диаграмма. В выделяемый блок данных включите не только числовые данные, но и заголовки строк (столбцов) таблицы, в которых они расположены. Заголовки будут использованы в качестве подписей по осям (меток) и для формирования условных обозначений (легенды). При выделении блоков с данными для построения диаграмм необходимо соблюдать два правила: Выделенный фрагмент должен состоять из равновеликих столбцов; В выделенном фрагменте не должно быть объединенных ячеек. Откройте файл Лаб1. Выделите данные в столбцах Автор и Вклад автора (ячейки B13:B17 и F13:F17). Несмежные диапазоны выделяются с зажатой клавишей Ctrl. Для построения круговой диаграммы (рис.5) необходимо перейти на вкладку Вставка, открыть список Круговая и выбрать нужную диаграмму. Для настройки диаграммы используйте инструменты на вкладке Макет: Подписи данных, Легенда, Название диаграммы.  Рис.5. Круговая диаграмма, отображающая вклад автора.  Рис.6. Гистограмма, отображающая соотношение вклада и затрат. Выделите данные в столбцах Автор, Вклад автора и Доля затрат с зажатой клавишей Ctrl. Для построения Гистограммы (рис.6) необходимо перейти на вкладку Вставка, открыть список Гистограмма и выбрать нужную гистограмму.  Рис. 7 Контекстное меню Круговая диаграмма и гистограмма строится сразу. Иногда необходимо выделить построенную диаграмму и провести изменение размера шрифта или растянуть диаграмму для лучшего чтения данных в поле диаграммы. Сохраните свою работу. Если вызвать контекстное меню в поле всей диаграммы (рис. 7), то меню предлагает три операции при построении диаграммы: Изменить тип диаграммы; Выбрать данные; Переместить диаграмму. Перемещение диаграммы предлагает разместить диаграмму на отдельном листе или на имеющемся, совместно с исходной таблицей.  Рис. 8 Окно диалога Перемещение диаграммы Если для диаграмм выбраны объемные фигуры, то через диалоговое окно рис.7 можно осуществить поворот объемной фигуры. На рис.7 вверху показана появляющаяся при вызове контекстного меню панель форматирования различных областей диаграммы. При выборе из списка области диаграммы эта область выделяется в поле диаграммы для её форматирования. Задание 4.Экспорт и импорт документов между программами Excel и Word и создание простых сайтов Вставить через буфер в документ Word Информационные технологии-2 таблицу (рис. 4) с листа Excel. Вставить через буфер в тот же документ Word диаграмму с листа Excel (рис.5). Сохранить документ Word с именем Лаб1_EXCEL. Затем еще раз сохранить документ Word через меню Файл-Сохранить как под другим именем, выбрав тип файла Web-страница в одном файле или Web-страница. Задание 5. Выбор данных для построения диаграмм из нерегулярных таблиц На рис.9 приведен пример нерегулярной таблицы. В таблице проведено объединение ячеек, с информацией необходимой при построении диаграмм. Для построения приведенной на рис.9 диаграммы выделяется три столбца без заголовков: Месяц, Доход и Расход. После построения диаграммы вызывается контекстное меню и пункт Выбрать данные на вкл. Конструктор. В левой части удаляются Элементы легенды (ряды). Затем нажимается в левом окне кн. Добавить и в появившемся окне Изменение ряда (рис.10) в поле Имя ряда вводится щелчком мыши клетке основной таблицы Доход, а в поле Значения, после его очищения вводится диапазон чисел столбца Доход. Операция повторяется для клетки Расход и диапазона чисел столбца Расход. Так как при удалении рядов автоматически удаляется и содержание поля Подписи горизонтальной оси (категории), то в левое поле, нажав кн. Изменить надо ввести диапазон месяцев с Января по Декабрь.   Рис.9 Нерегулярная таблица  Рис. 10 Окно диалога Иногда в диаграмме необходимо по горизонтальной оси расположить тоже числа. Если такой числовой столбик выделить совместно с другими числовыми столбцами, то он будет считаться числовыми данными и по ним будет строиться диаграмма. В этом случае диаграмма также строится в два этапа. Сначала выделяются только числовые столбцы для построения диаграммы. Затем после построения диаграммы вызывается контекстное меню и пункт Выбрать данные. в правом поле диалогового окна Выбор источника данных добавляется числовой столбец для горизонтальной оси диаграммы. Задание 7. Макросы Создание макроса. Будем создавать макрос, который на листе Excel в ячейке К1 (или другой по выбору) формирует: текст Фамилия Имя Отчество; шрифт Times New Roman; размер 16; цвет красный; начертание Полужирный курсив. До создания макроса необходимо уточнить все действия, которые будут осуществляться при его создании (т.е. надо провести репетицию записи макроса и проделать все действия без записи). Будем создавать макрос для конкретной книги (можно создавать для всех книг). Закладка Вид – Макросы – Запись макроса, в появившемся окне «Запись макроса» введите имя макроса, например свою фамилию, в поле «Сохранить в» выберите текущую книгу и дайте описание макроса в поле «Описание». В данном окне для макроса необходимо назначить комбинацию клавиш, например Ctrl – Д. Нажмите ОК. С этого момента начинается запись макроса. Активизируйте ячейку К1 и введите текст Фамилия Имя Отчество. Закончите ввод, нажав ENTER. Затем форматируйте содержание ячейки К1: через контекстное меню на ячейке выполнить команду Формат ячеек – Выравнивание (выравнивание по горизонтали и вертикали - по центру; включить флажок - Переносить по словам). шриф Times New Roman; размер 16; цвет красный; начертание Полужирный курсив. Остановите запись макроса через вкладку Вид-Макросы. Удалите все в ячейке К1, выделите ячейку, далее вкладка Главная –Р (справа ниже кнопки автосуммы)(меню Правка-Очистить-Все). Просмотр созданного макроса. Выберите Вкладку Вид – Макросы- Макросы. Откроется окно диалога «Макрос». Выберите из списка имя созданного макроса. Нажмите кнопку Изменить. Откроется окно редактора Visual Basic. В правой части экрана будет помещен выбранный макрос. Оцените свой труд. В окне редактора можно править ошибки. Запуск макроса комбинацией клавиш. Нажмите одновременно две клавиши Ctrl – A, на листе в ячейке К1 должен появиться текст Фамилия Имя Отчество в заданном формате. Удалите все в ячейке К1. Удалите макрос. Задание 8. Расчет рентабельности фирмы А) Используются таблицы для расчета рентабельности небольшой фирмы и доказательной иллюстрации этой рентабельности. Первая таблица (см. рис. 11) содержит сведения о расходах фирмы в первом полугодии 2018 г. Расходы, равно как и доходы расписаны по статьям, их составляющим, а также по месяцам. Последний столбец содержит итог по каждой статье за рассматриваемый период, а последняя строка – итог по всем статьям расходов за месяц. Доходы по статьям представлены во второй таблице. Уровень доходов в примере показан из расчета увеличения доходов по каждой статье на 5%. Это число выраженное в процентах находится в ячейке Е20. Формула в ячейке Е13 будет выглядеть так: =D13+D13* $E$20 (символ $ не позволит адресу Е20 меняться при копировании формулы в нижние ячейки) В третьей таблице вычислена разность между затратами и доходами по месяцам. В работе (рис.12) представлены два графика. Первый график иллюстрирует распределение доходов, получение которых ожидается в течение первого полугодия деятельности фирмы, по статьям. На втором графике представлено соотношение доходов и расходов по месяцам, а также по итогам полугодия. Здесь «Ряд 1»-доходы, «Ряд 2»- затраты.  Рис. 11. Представление формул  Рис.12 Результат Задание 9. Математические операции
Произвести вычисление функционального ряда: (1+1/(2*х/В)^1)+ (1+1/(2*х/В)^2)+ (1+1/(2*х/3)^3)+…….+ (1+1/(2*х/В)^n) 10 = (1 1 /(2 * X/ B)^ n) , n1 для n=10, x=2, B=0,5. Для вычисления по формуле необходимо подготовить таблицу и произвести суммирование через вкладку Главная-Автосумма. Построить график функции (Вставка-Диаграмм-Точечная)    Рис.13 Вычисление функционального ряда |