Четвертый этап. Анализ полученных результатов
Табличный процессор, его функциональные возможности.
Ввод и редактирование информации разных типов.
Форматирование ячейки и диапазона ячеек
Содержание учебного материала:
Организация расчетов в табличном процессе. Относительная и абсолютная адресация. Типы ошибок
План лекции:
1. Этапы решения задач в Microsoft Excel
2. Типы адресации в Microsoft Excel
3. Типы ошибок в Microsoft Excel
Этапы решения задачи с помощью табличного процессора
Каждый раз, используя табличный процессор Excel для решения задачи, мы выполняем некоторую последовательность шагов, которую можно назвать этапами решения. Какие это этапы и в какой последовательности они следуют друг за другом, рассмотрим на примере составления простой таблицы расчета сметы оборудования офиса, в которой подсчитаем затраты на приобретение электронно-вычислительной техники.
Первый этап. Анализ исходных данных и постановка задачи. На первом этапе проанализируем текст задачи и определим исходные данные задачи: наименование оборудования (текст), количество приобретаемых единиц оборудования (число), цена за единицу оборудования (число в иностранной валюте), курс иностранной валюты (число). Установим, что мы должны рассчитать в задаче следующие величины: стоимость всех единиц оборудования одного наименования с учетом курса валюты, итоговую сумму затрат.
Второй этап. Создание новой таблицы и ввод исходных данных. Для создания таблицы можно щелкнуть кнопку «Создать» на панели инструментов Стандартная. Затем ввести данные и изменить размеры столбцов, так чтобы в них полностью отображался текст.
|
|
|
Рис. 9. Окно Microsoft Excel с введенными данными задачи
Третий этап. Описание расчетов. В ячейку D4 необходимо поместить сумму затрат на приобретение компьютеров в рублях, которая подсчитывается по формуле В4*С4*В2.
Так как адрес ячейки В2, в которой размещен курс иностранной валюты, должен оставаться неизменным при последующем копировании формулы вычисления суммы в рублях, зададим абсолютную адресацию ячейки В2, для чего щелкнем клавишу F4. После этого в изображении ссылки на ячейку В2 появится знак доллара ($): $В$2. Завершим создание формулы вычисления, щелкнув кнопку «ОК». После этого в ячейке D4 немедленно появится результат вычислений, а в строке формул будет изображена формула, по которой выполняется вычисление: =В4*С4*$В$2.
Так как суммы затрат на приобретение остальных наименований оборудования вычисляются по аналогичной формуле, скопируем формулу из ячейки D4 в D5:D9.
Итак, для копирования формулы из ячейки D4 в D5:D9 выделим ячейку D4, затем установим курсор мыши на рамку выделения и нажмем левую кнопку мыши. Прижав клавишу Ctrl, перетащим мышью выделение с D4 на D5, удерживая нажатой кнопку мыши.
|
|
|
Для копирования формулы в примыкающий к данной ячейке диапазон внутри столбца или строки удобно использовать маркер заполнения. Для этого выделим ячейку D4, содержащую формулу, и перетащим маркер заполнения в диапазон в D5:D9 (рис. 11).
Рис. 11. Маркер заполнения в выделенной ячейке
Для вычисления итоговой суммы затрат в ячейку D10 введем формулу вычисления суммы ячеек D4:D9, для чего выделим диапазон ячеек D4:D9 и щелкнем кнопку (Автосумма).
Четвертый этап. Анализ полученных результатов
В результате выполненных действий получилась следующая таблица:
| А | В | С | D | |
| 1 | Смета оборудования офиса | |||
| 2 | Курс валюты | 28,45 | ||
| 3 | Наименование | Количество | Цена | Сумма, руб. |
| 4 | Компьютер Pentium III | 3 | 651,4 | 55596,99 |
| 5 | Принтер/копир/сканер | 1 | 693 | 19715,85 |
| 6 | Источник бесперебойного питания | 1 | 98,5 | 2802,325 |
| 7 | Сетевая карта | 3 | 55 | 4694,25 |
| 8 | Модем | 1 | 60,5 | 1721,225 |
| 9 | Бокс для дисков | 5 | 3 | 426,75 |
| 10 | Итого | 84957,39 |
Оцените полученные результаты. Обратите внимание, что фактически мы составили небольшую программу, которую можно использовать для многократных пересчетов. Например, если вы измените одно или несколько чисел в исходных данных, все суммы будут пересчитаны автоматически. Более того, вы можете модифицировать структуру таблицы, например, удалить строку с записью о модеме или вставить новую строку, и формулы в итогах будут изменены автоматически.
|
|
|
При оценке результатов часто возникает необходимость просмотреть формулы в ячейках таблицы. Для просмотра формулы нужно выделить ячейку, и в строке формул будет выведена формула в данной ячейке. Если требуется просмотреть формулы во всех ячейках таблицы на данном листе, то для переключения режимов просмотра формул и просмотра значений формул следует нажать Ctrl+' (левая кавычка).
Справа показано, как изменяется вид ячеек столбца D при переключении режима просмотра.
Изменение режима отображения формул и результатов вычислений на листе можно выполнить, выбрав команду Параметры в меню Сервис. На вкладке Вид для отображения формул в ячейках включите флажок Формулы. Если вы хотите отображать в ячейках результаты вычислений, то снимите данный флажок.
Для переключения в режим отображения всех формул на листе и доступа к средству проверки формул можно использовать панель инструментов окна контрольного значения. В меню Сервис выберите команду Зависимости формул, а затем на панели Зависимости щелкните кнопку «Показать окно контрольного значения».
|
|
|
После этого в окне Excel откроется окно контрольного значения, как показано на рис. 12. Щелкнув кнопку «Добавить контрольное значение», в окне Добавление контрольного значения уточните адрес ячейки с проверяемой формулой и щелкните кнопку «Добавить». После этого в окне контрольного значения будут отображены: название листа, адрес ячейки, значение и формула.
Для проверки вычислений в формуле выделите нужную ячейку и щелкните кнопку «Вычислить формулу» на панели Зависимости. После этого в окне Вычисление формулы нажимайте кнопку «Вычислить», чтобы проверить значение подчеркнутой ссылки. Результат вычислений показывается в формуле курсивом.
Рис. 12. Окно Excel с панелью зависимости и окном контрольного значения
Если подчеркнутая часть формулы является ссылкой на другую формулу, нажмите кнопку «Шаг с заходом», чтобы отобразить другую формулу в полеВычисление. Нажмите кнопку «Шаг с выходом», чтобы вернуться в предыдущие ячейку и формулу. Продолжайте, пока каждая часть формулы не будет вычислена.
Чтобы снова увидеть вычисления, нажмите кнопку «Начать сначала». Чтобы закончить вычисления, нажмите кнопку «Закрыть».
Проверка формулы на наличие ошибок. Microsoft Excel имеет средства для проверки правильности формул. При этом в Excel используются определенные правила, которые не гарантируют отсутствие ошибок на листе, но они помогают избежать общих ошибок в формулах. Эти правила можно независимо включать и отключать. Для проверки ошибок в формулах можно щелкнуть кнопку «Проверка наличия ошибок на панели Зависимости». При обнаружении ошибки в ячейке в ее левом верхнем углу появляется треугольник. В справочной системе Excel можно получить подробную информацию об ошибках и способах их исправления.
Пятый этап. Редактирование таблицы.
Для изменения содержимого ячейки следует дважды щелкнуть ячейку, содержимое которой необходимо изменить, и отредактировать содержимое ячейки. После изменения содержимого ячейки нажмите клавишу Enter для сохранения изменений или клавишу Esc, если вы хотите отменить внесенные изменения. Если вы уже нажали Enter, то для отказа от внесенных изменений нужно воспользоваться командой Отменить из меню Правка.
Если нужно, вы можете вставить новые столбцы или строки. меню Вставка выберем команду Строки. После этого все строки, расположенные ниже, сместятся на одну строку вниз, и строка вставится в таблицу.
Шестой этап. Оформление таблицы. Задавая высоту строк и ширину столбцов, выбирая тип границ, цвет и узор заливки для ячеек, изменяя шрифт и выравнивание данных в ячейках, вы можете сделать таблицу более наглядной.
Один из самых быстрых способов оформления таблицы заключается в использовании команды Автоформат меню Формат. Для его применения выделите все ячейки таблицы и выберите в меню Формат команду Автоформат. В диалоговом окне Автоформат выберите нужный тип формата в поле Список форматов, а в поле Образец просматривайте вариант оформления таблицы с избранным типом формата. Если нужно сделать дополнительный выбор, то для частичного применения автоформата нажмите кнопку «Параметры» и снимите флажки для форматов, которые не нужно применять. По окончании выбора нужного типа формата щелкните кнопку «ОК» и просмотрите результат избранного вами варианта оформления таблицы. Если этот вариант вас не устраивает, то можно воспользоваться отменой операции, выбрав в меню Правка команду Отменить Автоформат.
Седьмой этап. Построение диаграмм. Построим диаграмму, которая будет отображать расходы на приобретение отдельных наименований оборудования. Для построения диаграммы выделим ячейки A3:D10, содержащие данные, которые должны быть отражены на диаграмме.
Примечание. Если необходимо, чтобы в диаграмме были отражены и названия строк или столбцов, нужно выделить содержащие их ячейки.
Щелкнув кнопку Мастер диаграмм, следуя инструкциям мастера, зададим параметры диаграммы:
на первом шаге выберем тип диаграммы, например круговая;
на втором шаге определим источник данных диаграммы:(строки или столбцы) и уточним диапазон ячеек, данные из которых отображаются на диаграмме, на вкладке Ряд можно уточнить состав рядов с данными, участвующих в формировании диаграммы;
на третьем шаге зададим параметры диаграммы: название диаграммы, подписи осей и данных, отображение линий сетки, состав и место размещения легенды на диаграмме;
на четвертом шаге выбираем место размещения диаграммы и щелкнем кнопку «Готово».
Диаграмма будет выведена на экран. Просмотрим полученную диаграмму. Если необходимо отредактировать диаграмму, то, щелкнув на ней мышью, откроем на экране панель инструментов редактирования диаграммы, показанную на рис. 13. Пользуясь всплывающей подсказкой, можно изучить назначение отдельных кнопок на этой панели.
Рис.13. Панель инструментов редактирования диаграммы
Восьмой этап. Защита таблицы. Если вы не хотите, чтобы кто-либо, открыв таблицу, увидел, по каким формулам выполняются расчеты, вы можете скрыть формулы на листе. Для того чтобы таблица работала именно так, как вы предполагали при ее разработке, вы должны быть уверены, что никто не сможет изменить формулы, по которым в ней выполняются расчеты. С этой целью Excel обеспечивает вам возможность скрыть формулы и защитить лист от изменений.
Для скрытия формул выделите диапазон ячеек, в которых нужно скрыть формулы. (При необходимости можно выделить несмежные диапазоны ячеек или весь лист.)
Затем в меню Формат выберите команду Ячейки. На вкладке Защита установите флажки Скрыть формулы и Защищаемая ячейка, после чего нажмите кнопку «ОК». После этого в меню «Сервис» выберите команду Защита, а затем - команду Защитить лист. Проверьте, чтобы в открывшемся диалоговом окне был установлен флажок Содержимое.
Примечание. Чтобы скрытые формулы снова изображались в строке формул, снимите защиту листа, выбрав в меню Сервис команду Защита, а затем - команду Снять защиту листа. После этого выделите группу ячеек, формулы которых нужно показать, в меню Формат выберите команду Ячейки, а затем на вкладке Защита снимите флажок Скрыть формулы.
Девятый этап. Сохранение таблицы и использование ее для расчетов. Для сохранения новой книги выберите в меню Файл команду Сохранить как. В диалоговом окне Сохранение документа в поле Папка укажите диск и папку, в которую будет помещена книга. Чтобы сохранить книгу в новой папке, щелкните кнопку «Создать папку» и, задав ей имя, откройте ее. В поле Имя файла введите имя книги и нажмите кнопку «Сохранить».
Примечание. Чтобы упростить в последующем поиск данной книги, в меню Файл выберите команду Свойства. На вкладке Документ введите заголовок книги, тему, автора, ключевые слова и заметки. Эти данные используются затем для размещения файла в диалоговом окне Открыть (меню Файл).
Десятый этап. Печать таблицы. Перед выводом таблицы на печать необходимо выполнить подготовку к печати. Вначале целесообразно перейти в режим редактирования таблицы с разметкой страницы, для чего в меню Вид выбрать команду Разметка страниц. После этого на листе Excel будет показано размещение таблицы на странице. Вы можете, ухватив мышью за край страницы, перетащить ее в другую позицию, изменив состав ячеек, отображаемых на странице.
Если вы хотите распечатать не весь лист Excel, то можно задать область печати, выделив нужный диапазон ячеек и выбрав в меню Файл команду Область печати - Задать. Область печати можно определить, выбрав в режиме Разметка страницы нужную область и щелкнув правой кнопкой мыши одну из выделенных ячеек, а затем выбрав в контекстном меню команду Установить область печати.
Для того чтобы увидеть, как будет выглядеть лист, выведенный на печать, со всеми колонтитулами и заголовками печати, нажмите кнопку «Предварительный просмотр» в панели инструментов Стандартная или выберите аналогичную команду в меню Файл. В режиме предварительного просмотра в нижней части экрана в строке состояния отображаются номер текущей страницы и общее число страниц на выделенном листе, а в верхней части экрана отображается панель управления.
Чтобы увеличить масштаб или вернуться в режим отображения полной страницы, нажмите кнопку «Масштаб». Курсор мыши имеет вид лупы, щелкнув мышью в любой области листа, вы также можете увеличить масштаб или вернуться в режим отображения полной страницы. При изменении масштаба размер печатной страницы не изменяется.
Кнопки «Назад/Далее» служат для просмотра предыдущей / следующей страницы листа. Кнопка «Печать» служит для установки параметров печати и печати выделенного листа. Щелчок кнопки «Страница» открывает диалоговое окно настройки параметров распечатываемых страниц. На вкладке Страница этого окна можно выбрать размер бумаги и ориентацию страницы, задать масштаб печати страницы на бумаге. Вкладка Поля позволяет установить размеры полей и расположение колонтитулов на странице. Вкладка Колонтитулы предназначена для создания колонтитулов и ввода в них данных: номер страницы, дата и время, имя файла. Вкладка Лист позволяет определить такие опции печати: печатать ли сетку, заголовки столбцов и строк, определить порядок печати страниц. Кнопка «Поля» служит для отображения и скрытия маркеров настройки полей страницы. Если маркеры настройки полей страницы отображены, то можно брать их указателем мыши и тащить, изменяя размеры полей страницы, верхнего и нижнего колонтитулов и ширину столбцов. Кнопка «Разметка страницы» служит для переключения в режим просмотра разрывов страниц. В этом режиме выполняется настройка разрывов страниц активного листа Excel. Также возможно изменение размеров области печати и изменение листа Excel. Кнопка «Обычный режим» служит для отображения активного листа в обычном режиме. Имя кнопки изменяется на «Обычный», если при нажатии кнопки «Предварительный просмотр» был активен режим просмотра разрывов страниц.
Внешний вид страниц в окне предварительного просмотра зависит от доступных шрифтов, разрешения принтера, количества доступных цветов. Так как в нашем примере лист Excel содержит встроенную диаграмму, то в окне предварительного просмотра он отображается вместе с диаграммой. Если перед нажатием кнопки «Предварительный просмотр» была выделена диаграмма, Microsoft Excel отобразит только ее. Для закрытия окна предварительного просмотра и перехода на текущий лист служит кнопка «Закрыть».
Для вывода подготовленной таблицы на бумагу выберите в меню Файл команду Печать, затем задайте параметры печати и щелкните кнопку «ОК» для начала процесса печати. Пронаблюдать процесс печати можно в окне состояния принтера.
Расчетные операции в Excel
Заранее определенные формулы, которые выполняют вычисления по заданным величинам (аргументам) называются функциями. Эти функции позволяют выполнять простые и сложные вычисления. Функция имеет имя (например, SIN) и, как правило, аргументы, которые записываются в круглых скобках следом за именем функции. Скобки - обязательная принадлежность функции, даже если у нее нет аргументов. Если аргументов несколько, один аргумент отделяется от другого точкой с запятой. В качестве аргументов функции могут использоваться числа, адреса ячеек, диапазоны ячеек, арифметические выражения и функции. Смысл и порядок следования аргументов однозначно определены описанием функции, составленным ее автором. Например, если в ячейке F3 записана формула с функцией возведения в степень =СТЕПЕНЬ(ВЗ;2,3), значением этой ячейки будет значение ячейки ВЗ, возведенное в степень 2,3.
Работая с функциями, помните:
1. функция, записанная в формуле, как правило, возвращает уникальное значение (арифметическое или логическое);
2. существуют функции, которые не возвращают значение, а выполняют некоторые операции (например, объединяют текстовые строки);
3. существуют функции без аргументов (например, функция ПИ() возвращает число к = 3,1416...).
Ниже будут рассмотрены функции И (AND) и ИЛИ (OR), которые принимают логические значения (True или False).
Обратите внимание на то, что изложенные в этом пункте идеи и правила широко используются в прикладной информатике - в языках программирования, языках запросов, в других приложениях Windows.
Функции Excel разделены на категории (тематические группы): финансовые, даты и времени, математические, статистические, ссылки и массивы, работы с базой данных, текстовые, логические, проверки свойств и значений. Для упрощения ввода функций в Excel предусмотрен специальный Мастер функций, который можно вызвать нажатием кнопки «fx» на панели инструментов Стандартная. Предварительно следует выделить ячейку, в которую вставляется формула.
Подробное описание назначения и синтаксиса функций можно просмотреть в справочной системе Excel. Для этого вызовите справку Excel и на вкладке Поискзадайте образ поиска, например СРЗНАЧ, затем в списке найденных разделов выделите раздел СРЗНАЧ и щелкните кнопку «Показать». После этого на экране будет развернуто окно справки Excel по данной теме, в котором можно просмотреть описание назначения функции, ее синтаксиса и примеры ее применения.
Логические функции
При решении некоторых задач значение ячейки необходимо вычислять одним из нескольких способов, в зависимости от выполнения или невыполнения одного или нескольких условий. Для решения таких задач применяют условную функцию ЕСЛИ. Она имеет формат:
ЕСЛИ(<логическое выражение>;<выражение1>;<выражение2>).
Чтобы пользоваться этой функцией, вам целесообразно познакомиться с основными понятиями логической (булевой) алгебры. Первый аргумент функции ЕСЛИ - логическое выражение (в частном случае - условное выражение), которое принимает .одно из двух значений: «Истина» или «Ложь» (1 или 0). В первом случае ЕСЛИ принимает значение выражения!, а во втором -значение выражения2. В качестве выражения! или выражения2 можно записать вложенную функцию ЕСЛИ. Обратите внимание, что число вложенных функций ЕСЛИ не должно превышать семи. Если условий много, записывать вложенные функции ЕСЛИ становится неудобно. В этом случае на месте логического выражения мы можем указать одну из двух логических функций: H(AND) или ИЛИ (ОК).
Формат функций одинаков:
И (<логическое выражение 1>;<логическое выражение2>;...),
ИЛИ (<логическое выражение! >;<логическое выражение2>;...).
Функция И принимает значение «Истина», если одновременно истинны все логические выражения, указанные в качестве аргументов этой функции. В остальных случаях значение И - «Ложь». В скобках можно указать до 30 логических выражений.
Функция ИЛИ принимает значение «Истина», если истинно хотя бы одно из логических выражений, указанных в качестве аргументов этой функции. В остальных случаях значение ИЛИ - «Ложь».
2 Ссылка на ячейку или группу ячеек–способ, которым указывается конкретная ячейка или несколько ячеек. Ссылка на отдельную ячейку – ее адрес. Значение пустой ячейки равно нулю. Ссылки на ячейки бывают двух типов:
относительные–ячейки обозначаются относительным смещением от ячейки с формулой(например: F7).
абсолютные–ячейки обозначаются координатами ячеек в сочетании со знаком$(например:$F$7).
Комбинация предыдущих типов – смешанные (например: F$7, $F7).
Сообщения об ошибках
Если формула в ячейке не может быть правильно вычислена, Microsoft Excel выводит в ячейку сообщение об ошибке. Если формула содержит ссылку на ячейку, которая содержит значения ошибки, то вместо этой формулы также будет выводиться сообщение об ошибке. Значение сообщений об ошибках следующее:
#### – ширина ячейки не позволяет отобразить число в заданном формате;
#ИМЯ? – Microsoft Excel не распознает имя, используемое в формуле;
#ДЕЛ/0! – в формуле делается попытка деления на нуль;
#ЧИСЛО! – нарушены правила задания операторов, принятые в математике;
#Н/Д–такое сообщение может появиться,если в качестве аргумента задана ссылка на пустуюячейку;
#ПУСТО!–неверно указано пересечение двух областей,которые не имеют общих ячеек;#ССЫЛКА!–в формуле задана ссылка на несуществующую ячейку;
#ЗНАЧ! – использован недопустимый тип аргумента.
Контрольные вопросы:
1. Составьте алгоритм:
1. Создания новой Книги
2. Суммирования диапазона ячеек
3. Использования абсолютного адреса в ячейки
4. Определение процента из числа
5. Копирования ячейки
6. Вставки строки или нескольких строк
7. Отображения формул в таблице
8. Редактирования таблицы
9. Оформления таблицы
10. Построение диаграммы
11. Защиты таблицы и ячеек
12. Печать таблицы с «бордюром»
2. Что такое встроенная функция
3. Назовите расчетные функции
4. Назовите логические функции и алгоритм их использования
5. Что означает сообщение об ошибке
· ####
· #ИМЯ?
· #ДЕЛ/0!
· #ЧИСЛО!
· #Н/Д
· #ПУСТО!
· #ССЫЛКА!
· #ЗНАЧ!
Содержание отчета
Отчет оформляется в файле программы Excel по ходу выполнения задания.
Отчет должен содержать:
1. Номер практической работы.
2. Тему работы.
3. Цель работы.
4. Выполненные задания.
Ответы на контрольные вопросы 
Файл с отчетом отправляется на почту преподавателя -
Воздвиженской Елене Константиновне preymdget@mail.ru
тел. 071 43 116 19
Домашнее задание:
ЭБС КонсультантСтудента.
Информационные технологии в управлении [Электронный ресурс] : учебник для вузов / Н.В. Галиева, Ж.К. Галиев. - М. : МИСиС, 2020. Режим доступа: https://www.studentlibrary.ru/book/ISBN9785907226814.html
Табличный процессор Excel 2010
http://dl.khadi.kharkov.ua/pluginfile.php/37377/mod_resource/content/1/Лекція%2010р%20Excel%20Начало%20для%20іноземних%20студентів.pdf
Рекомендуемая литература
Литература
Основная:
1. Вишневский В.П., Ремезов В.В., Прокди Р.Г. Windows 7+ Office 2010 – Санкт-Петербург «Наука и Техника», 2011. – 559с.
2. Ярмуш О.В., Редько М.М. «Інформатика и комп’ютерна техніка», Навч.посіб. – К.:Вища освіта, 2006. – 359с.
3. Тихомиров А.Н., Колосков П.В., Прокди Р.Г. Весь Office 2010 – Санкт-Петербург «Наука и Техника», 2011.- 621с.
4. Стоцкий Ю., Васильев А., Телина И. Самоучитель Microsoft Office 2010 – Санкт-Петербург «Питер», 2011.- 425с.
5. Макарова Н.В. Информатика. Практикум по технологии работы на компьютере. – Москва «Финансы и статистика», 2010.- 255с.
6. Басюк Т.М., Уманский Н.А., Пасечник А.В. Основы информационных технологий Учебное пособие - Л: «Новый Свет-200», 2011.
Дополнительная:
1. Безека СВ. Создание презентаций в Ms PowerPoint 2007. - СПб.: ПИТЕР, 2010. - 275 с.
Интернет-ресурсы:
1. Электронный ресурс: MS Office 2007 Электронный видео учебник. Форма доступа:http:// gigasize.ru.
2. Электронный ресурс: Российское образование. Федеральный портал. Форма доступа:http:// www.edu.ru/fasi.
3. Электронный ресурс: Лаборатория виртуальной учебной литературы. Форма доступа:http:// www.gaudeamus.omskcity .
Дата добавления: 2020-11-23; просмотров: 89; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!