Учебник Информатика. Базовый курс. Симонович С.В.. С. В. Симоновичаинформатикабазовый курс2е издание
 Скачать 17.96 Mb.
|
|
Применение электронных таблиц для расчетов В научно-технической деятельности программу Excel трудно рассматривать как основной вычислительный инструмент. Однако ее удобно применять в тех случаях, когда требуется быстрая обработка больших объемов данных. Она полезна для выполнения таких операций, как статистическая обработка и анализ данных, реше- 3 2 6 Глава Обработка данных средствами электронных таблиц ние задач оптимизации, построение диаграмм и графиков. Для такого рода задач применяют как основные средства программы Excel, так и дополнительные (над- стройки). Итоговые вычисления Итоговые вычисления предполагают получение числовых характеристик, описы- вающих определенный набор данных в целом. Например, возможно вычисление суммы значений, входящих в набор, среднего значения и других статистических характеристик, количества или доли элементов набора, удовлетворяющих опреде- ленных условиям. Проведение итоговых вычислений в программе Excel выполня- ется при помощи встроенных функций. Особенность использования таких итого- вых функций состоит в том, что при их задании программа «угадать», в каких ячейках заключен обрабатываемый набор данных, и задать параметры функции автоматически. В качестве параметра итоговой функции обычно задается некоторый диапазон ячеек, размер которого определяется автоматически. Выбранный диапазон рассмат- ривается как отдельный параметр («массив»), и в вычислениях используются все ячейки, составляющие его. Суммирование. Для итоговых вычислений применяют ограниченный набор функ- ций, наиболее типичной из которых является функция суммирования (СУММ). Это единственная функция, для применения которой есть отдельная кнопка на стан- дартной панели инструментов (кнопка Автосумма). Диапазон суммирования, выби- раемый автоматически, включает ячейки с данными, расположенные над текущей (предпочтительнее) или слева от нее и образующие непрерывный блок. При неоднозначности выбора используется диапазон, непосредственно примыка- ющий к ячейке. Автоматический подбор диапазона не исключает возможности редактирования формулы. Можно переопределить диапазон, который был выбран автоматически, а также задать дополнительные параметры функции. Функции для итоговых вычислений. Прочие функции для итоговых вычислений выбираются обычным образом, с помощью раскрывающегося списка в строке фор- мул или с использованием мастера функций. Все эти функции относятся к катего- рии Статистические. В их число входят функции ДИСП (вычисляет дисперсию), МАКС (максимальное число в диапазоне), СРЗНАЧ (среднее арифметическое значе- ние чисел диапазона), СЧЕТ (подсчет ячеек с числами в диапазоне) и другие. Функции, предназначенные для выполнения итоговых вычислений, часто приме- няют при использовании таблицы Excel в качестве базы данных, а именно на фоне фильтрации записей или при создании сводных таблиц. Использование надстроек Надстройки — это специальные средства, расширяющие возможности программы Excel. На практике именно надстройки делают программу Excel удобной для исполь- зования в научно-технической работе. Хотя эти средства считаются внешними, Применение электронных таблиц для расчетов 327 дополнительными, доступ к ним осуществляется при помощи обычных команд строки меню (обычно через меню Сервис или Данные). Команда использования настройки обычно открывает специальное диалоговое окно, оформление которого не отличается от стандартных диалоговых окон программы Excel. Подключить установленные надстройки можно с помощью команды Сервис • Надстройки (рис. 12.6). Подключение надстроек увеличивает нагрузку на вычислительную систему, поэтому обычно рекомендуют подключать только те над- стройки, которые реально используются. надстроек Описание выбранной надстройки V Кнопка выбора папки, в которой хранятся надстройки Рис. 12.6. Диалоговое окно для подключения и отключения надстроек Вот основные надстройки, поставляемые вместе с программой Excel. Пакет анализа {Analysis ToolPak). Обеспечивает дополнительные возможности анализа наборов данных. Выбор конкретного метода анализа осуществляется в диалоговом окне Data Analysis (Анализ данных), которое открывается командой Сервис • Data Analysis (Анализ данных). Мастер суммирования Wizard). Позволяет автоматизировать созда- ние формул для суммирования данных в столбце таблицы. При этом ячейки могут включаться в сумму только при выполнении определенных условий. Запуск мас- тера осуществляется с помощью команды Сервис • Conditional Sum (Частичная сумма). Мастер подстановок {Lookup Wizard). Автоматизирует создание формулы для поиска данных в таблице по названию столбца и строки. Мастер позволяет произ- вести однократный поиск или предоставляет возможность ручного задания пара- метров, используемых для поиска. Вызывается командой Сервис • Lookup (Поиск). Поиск решения Add-in). Эта надстройка используется для решения задач оптимизации. Ячейки, для которых подбираются оптимальные значения и зада- ются ограничения, выбираются в диалоговом окне Solver Parameters (Поиск реше- ния), которое открывают при помощи команды Сервис • Solver (Поиск решения). 3 2 8 Глава Обработка данных средствами электронных таблиц Построение диаграмм и графиков В программе Excel термин «диаграмма» используется для обозначения всех видов графического представления числовых данных. Построение графического изобра- жения производится на основе ряда данных. Так называют группу ячеек с данными в пределах отдельной строки или столбца. На одной диаграмме можно отображать несколько рядов данных. Диаграмма представляет собой вставной объект, внедренный на один из листов рабочей книги. Она может располагаться на том же листе, на котором находятся данные, или на любом другом листе (часто для отображения диаграммы отводят отдельный лист). Диаграмма сохраняет связь с данными, на основе которых она построена, и при обновлении этих данных немедленно изменяет свой вид. Для построения диаграммы обычно используют Мастер диаграмм, запускаемый щелчком на кнопке Мастер диаграмм на стандартной панели инструментов. Часто удобно заранее выделить область, содержащую данные, которые будут отображаться на диаграмме, но задать эту информацию можно и в ходе работы Выбор типа диаграммы На первом этапе работы мастера выбирают форму диаграммы. Доступные формы перечислены в списке Тип на вкладке Стандартные. Для выбранного типа диаграммы справа указывается несколько вариантов представления данных (палитра Вид), из которых следует выбрать наиболее подходящий. На вкладке Нестандартные отоб- ражается набор полностью сформированных типов диаграмм с готовым формати- рованием. После задания формы диаграммы следует щелкнуть на кнопке Далее. Выбор данных Второй этап работы мастера служит для выбора данных, по которым будет стро- иться диаграмма (рис. Если диапазон данных был выбран заранее, то в области предварительного просмотра в верхней части окна мастера появится приблизи- тельное отображение будущей диаграммы. Если данные образуют единый прямо- угольный диапазон, то их удобно выбирать при помощи вкладки Диапазон данных. Если данные не образуют единой группы, то информацию для отрисовки отдельных рядов данных задают на вкладке Ряд. Предварительное представление диаграммы автоматически обновляется при изменении набора отображаемых данных. Оформление диаграммы Третий этап работы мастера (после щелчка на кнопке Далее) состоит в выборе оформления диаграммы. На вкладках окна мастера задаются: • название диаграммы, подписи осей (вкладка Заголовки); • отображение и маркировка осей координат (вкладка Оси); • отображение сетки линий, параллельных осям координат (вкладка Линии сетки); описание построенных графиков (вкладка Легенда); Построение диаграмм и графиков 329 На этой вкладке ряды данных выбираются вручную Список рядов данных добавления и удаления рядов данных предварительного просмотра Имя текущего ряда данных Значения, используемые при построении графика Рис. 12.7. Выбор данных, отображаемых диаграмме • отображение надписей, соответствующих отдельным элементам данных на гра- фике (вкладка Подписи данных); • представление данных, использованных при построении графика, в виде таб- лицы (вкладка Таблица данных). В зависимости от типа диаграммы некоторые из перечисленных вкладок могут отсутствовать. Размещение диаграммы На последнем этапе работы мастера (после щелчка на кнопке Далее) указывается, следует ли использовать для размещения диаграммы новый рабочий лист или один из имеющихся. Обычно этот выбор важен только для последующей печати доку- мента, содержащего диаграмму. После щелчка на кнопке Готово диаграмма строится автоматически и вставляется на указанный рабочий лист (рис. 12.8). Редактирование диаграммы Готовую диаграмму можно изменить. Она состоит из набора отдельных элемен- тов, таких, как сами графики данных), оси координат, заголовок диаграм- мы, область построения и прочее. При щелчке на элементе диаграммы он выделя- ется маркерами, а при наведении на него указателя мыши — описывается всплывающей подсказкой. Открыть диалоговое окно для форматирования элемента диаграммы можно через меню Формат (для выделенного элемента) или через кон- текстное меню (команда Формат). Различные вкладки открывшегося диалогового окна позволяют изменять параметры отображения выбранного элемента данных. 330 Глава О б р а б о т к а данных средствами электронных таблиц Выбор элементов диаграммы в строке формул Кнопка Панель Заголовок Мастер Диаграммы Выбранный элемент диаграммы (Легенда) 12.8. Готовая диаграмма Excel Если требуется внести в диаграмму существенные изменения, следует вновь вос- пользоваться мастером диаграмм. этого следует открыть рабочий лист с диа- граммой или выбрать диаграмму, внедренную в рабочий лист с данными. Запустив мастер диаграмм, можно изменить текущие параметры, которые рассматриваются в окнах мастера как заданные по умолчанию. Чтобы удалить диаграмму, можно удалить рабочий лист, на котором она располо- жена (Правка • Удалить лист), или выбрать диаграмму, внедренную в рабочий лист с данными, и нажать клавишу DELETE. Практическое занятие Упражнение Обработка данных 1. Запустите программу Excel (Пуск • Программы • Microsoft Excel). 2. Создайте новую рабочую книгу (кнопка Создать на стандартной панели инст- рументов). мин Практическое занятие 3. Дважды щелкните на ярлычке текущего рабочего листа и дайте этому рабочему листу имя Данные. 4. Дайте команду Файл • Сохранить как и сохраните рабочую книгу под именем book.xls. 5. Сделайте текущей ячейку А1 и введите в нее заголовок Результаты измерений. 6. Введите произвольные числа в последовательные ячейки столбца А, начиная с ячейки А2. 7. Введите в ячейку строку Удвоенное значение. 8. Введите в ячейку строку Квадрат значения. 9. Введите в ячейку D1 строку Квадрат следующего числа. 10. Введите в ячейку В2 формулу =2*А2. Введите в ячейку С2 формулу =А2*А2. 12. Введите в ячейку D2 формулу 13. Выделите протягиванием ячейки В2, С2 и D2. 14. Наведите указатель мыши на маркер заполнения в правом нижнем углу рамки, охватывающей выделенный диапазон. Нажмите левую кнопку мыши и пере- тащите этот маркер, чтобы рамка охватила столько строк в столбцах В, С и D, сколько имеется чисел в столбце А. Убедитесь, что формулы автоматически модифицируются так, чтобы работать со ячейки в столбце А текущей строки. Измените одно из значений в столбце А и убедитесь, что соответствующие зна- чения в столбцах В, С и D в этой же строке были автоматически пересчитаны. 17. Введите в ячейку Е1 строку Масштабный множитель. 18. Введите в ячейку Е2 число 5. 19. Введите в ячейку F1 строку Масштабирование. 20. Введите в ячейку F2 формулу =А2*Е2. Используйте метод автозаполнения, чтобы скопировать эту формулу в ячейки столбца F, соответствующие заполненным ячейкам столбца А. 22. Убедитесь, что результат масштабирования оказался неверным. Это связано с тем, что адрес Е2 в формуле задан относительной ссылкой. 23. Щелкните на ячейке F2, затем в строке формул. Установите текстовый курсор на ссылку Е2 и нажмите клавишу F4. Убедитесь, что формула теперь выглядит как =А2*$Е$2, и нажмите клавишу ENTER. 24. Повторите заполнение столбца F формулой из ячейки F2. 25. Убедитесь, что благодаря использованию абсолютной адресации значения ячеек столбца F теперь вычисляются правильно. Сохраните рабочую книгу book.xls. Мы научились вводить текстовые и числовые данные в электронные таблицы Excel. Мы узнали, как производится ввод и вычисление формул. Мы также выяснили, как осуществляется копирование формул методом автозаполнения, и определили, в каких случаях следует использовать относительные и абсолютные ссылки. 3 3 2 Глава Обработка данных средствами электронных таблиц Т Упражнение Применение итоговых функций мин 1. Запустите программу Excel (Пуск • Программы • Microsoft Excel) и откройте рабо- чую книгу созданную ранее. 2. Выберите рабочий лист Данные. 3. Сделайте текущей первую свободную ячейку в столбце А. 4. Щелкните на кнопке Автосумма на стандартной панели инструментов. 5. Убедитесь, что программа автоматически подставила в формулу функцию СУММ и правильно выбрала диапазон ячеек для суммирования. Нажмите кла- вишу ENTER. 6. Сделайте текущей следующую свободную ячейку в столбце А. 7. Щелкните на кнопке Вставка функции в строке формул. 8. В раскрывающемся списке Категория выберите пункт Статистические. 9. В списке Функция выберите функцию СРЗНАЧ и щелкните на кнопке ОК. 10. Переместите методом перетаскивания окно Аргументы функции, если оно засло- няет нужные ячейки. Обратите внимание, что автоматически выбранный диапа- зон включает все ячейки с числовым содержимым, включая и ту, которая содер- жит сумму. Выделите правильный диапазон методом протягивания и нажмите клавишу ENTER. Используя порядок действий, описанный в пп. 6-10, вычислите минимальное число в заданном наборе (функция МИН), максимальное число (МАКС), коли- чество элементов в наборе (СЧЕТ). 12. Сохраните рабочую книгу book.xls. Мы познакомились с некоторыми итоговыми функциями. Мы научились использовать итоговые функции для вычисления значений, характеризующих набор данных. Мы выяснили, как автоматически определяется диапазон значений, обрабатываемых функ- цией, и как изменить его вручную. Упражнение Подготовка и форматирование прайс-листа 30 мин 1. Запустите программу Excel (Пуск • Программы • Microsoft Excel) и откройте рабо- чую книгу book.xls. 2. Выберите щелчком на ярлычке неиспользуемый рабочий лист или создайте новый (Вставка • Лист). Дважды щелкните на ярлычке нового листа и переиме- нуйте его как Прейскурант. 3. В ячейку А1 введите текст Прейскурант и нажмите клавишу ENTER. 4. В ячейку А2 текст Курс пересчета: и нажмите клавишу ENTER. В ячейку В2 введите текст 1 у.е.= и нажмите клавишу ENTER. В ячейку С2 введите теку- щий курс пересчета и нажмите клавишу ENTER. занятие 3 3 3 5. В ячейку A3 введите текст Наименование товара и нажмите клавишу ENTER. В ячейку ВЗ введите текст Цена (у.е.) и нажмите клавишу ENTER. В ячейку СЗ введите текст Цена (руб.) и нажмите клавишу ENTER. 6. В последующие ячейки столбца А введите названия товаров, включенных в прейскурант. 7. В соответствующие ячейки столбца В введите цены товаров в условных единицах. 8. В С4 введите формулу: которая используется для пересчета цены из условных единиц в рубли. 9. Методом автозаполнения скопируйте формулы во все ячейки столбца С, кото- рым соответствуют заполненные ячейки столбцов А и В. Почему при таком копировании получатся верные формулы? 10. Измените курс пересчета в ячейке С2. Обратите внимание, что все цены в руб- лях при этом обновляются автоматически. 11. Выделите методом протягивания диапазон А1:С1 и дайте команду Формат • Ячейки. На вкладке Выравнивание задайте выравнивание по горизонтали По центру и установите флажок Объединение ячеек. 12. На вкладке Шрифт задайте размер шрифта равный пунктам и в списке На- чертание выберите вариант Полужирный. Щелкните на кнопке ОК. 13. Щелкните правой кнопкой мыши на ячейке В2 и выберите в контекстном меню команду Формат ячеек. Задайте выравнивание по горизонтали По правому краю и щелкните на кнопке ОК. 14. Щелкните правой кнопкой мыши на ячейке и выберите в контекстном меню команду Формат ячеек. Задайте выравнивание по горизонтали По левому краю и щелкните на кнопке ОК. 15. Выделите методом протягивания диапазон В2:С2. Щелкните на раскрываю- щей кнопке рядом с Границы на панели инструментов Форматирование и задайте для этих ячеек толстую внешнюю границу (кнопка в правом нижнем углу открывшейся палитры). Дважды щелкните на границе между заголовками столбцов А и В, В и С, С и Обратите внимание, как при этом ширина столбцов А, В и С. 17. Посмотрите, ли вас полученный формат таблицы. Щелкните на кнопке Предварительный просмотр на стандартной панели инструментов, чтобы увидеть, как документ будет выглядеть при печати. 18. Щелкните на кнопке Печать и напечатайте 19. Сохраните рабочую книгу book.xls. Мы научились форматировать документ Excel. При этом мы использовали такие сред- ства, как изменение ширины столбцов, объединение ячеек, управление выравнива- нием текста, создание рамок ячеек. Мы выяснили, что в готовом документе заданные и вычисленные ячейки отображаются одинаково. Мы познакомились с использова- нием средства предварительного просмотра и произвели печать документа. 3 3 4 Глава Обработка данных средствами электронных таблиц Т Упражнение Построение экспериментального графика мин 1. Excel (Пуск • Программы • Microsoft Excel) и откройте рабо- чую книгу book.xls, созданную ранее. 2. Выберите щелчком на ярлычке неиспользуемый рабочий лист или создайте новый (Вставка • Лист). Дважды щелкните на ярлычке листа и переименуйте его как Обработка эксперимента. 3. В столбец А, начиная с ячейки введите произвольный набор значений неза- висимой 4. В столбец В, начиная с ячейки введите произвольный набор значений функции. 5. Методом протягивания выделите все заполненные ячейки столбцов А и В. 6. Щелкните на значке Мастер диаграмм на стандартной панели инструментов. 7. В списке Тип выберите пункт Точечная (для отображения графика, заданного парами значений). В палитре Вид выберите средний пункт в первом столбце (маркеры, соединенные гладкими кривыми). Щелкните на кнопке Далее. 8. Так как диапазон ячеек был выделен заранее, мастер диаграмм автоматически определяет расположение рядов данных. Убедитесь, что данные на диаграмме выбраны правильно. На вкладке Ряд в поле Имя укажите: Результаты измере- ний. Щелкните на кнопке Далее. 9. Выберите вкладку Заголовки. Убедитесь, что заданное название ряда данных автоматически использовано как заголовок диаграммы. Замените его, введя в поле Название диаграммы заголовок Экспериментальные точки. Щелкните на кнопке Далее. 10. Установите переключатель Отдельном. По желанию, задайте произвольное имя добавляемого рабочего листа. Щелкните на кнопке Готово. Убедитесь, что диаграмма построена и внедрена в новый рабочий лист. Рассмо- трите ее и щелкните на построенной кривой, чтобы выделить ряд данных. 12. Дайте команду Формат • Выделенный ряд. Откройте вкладку Вид. 13. На панели Линия откройте палитру Цвет и выберите красный цвет. В списке Тип линии выберите пунктир. 14. На панели Маркер выберите в списке Тип маркера треугольный маркер. В палит- рах Цвет и Фон выберите зеленый цвет. 15. Щелкните на кнопке снимите выделение с ряда данных и посмотрите, как изменился вид графика. Сохраните рабочую книгу. Мы научились строить графики на основе данных, содержащихся на рабочем листе, настраивать формат диаграммы, задавать отображаемые данные и оформлять получаю- щуюся диаграмму. Мы также узнали, как можно изменить формат готовой диаграммы. |