Каждый раз, используя табличный процессор Excel для решения задачи, выполняется некоторая последовательность шагов, которую можно назвать этапами решения. Какие это этапы и в какой последовательности они следуют друг за другом, рассмотрим на примере составления простой таблицы расчета сметы оборудования офиса, в которой подсчитаем затраты на приобретение электронно-вычислительной техники.
Первый этап - анализ исходных данных и постановка задачи. Проанализируем текст задачи и определим исходные данные задачи: наименование оборудования (текст), количество приобретаемых единиц оборудования (число), стоимость единицы оборудования (число в ин. валюте), курс ин. валюты (число). Установим, что мы должны рассчитать в задаче следующие величины: стоимость всех единиц оборудования одного наименования с учетом курса валюты, итоговую сумму затрат.
Второй этап — создание новой таблицы и ввод исходных данных. Для создания таблицы можно щелкнуть кнопку Создать на панели инструментов Стандартная. Затем ввести данные и изменить размеры столбцов, так чтобы в них полностью отображался текст.
|
|
Третий этап — описание расчетов. В ячейку D4 необходимо поместить сумму затрат на приобретение компьютеров в рублях, которая подсчитывается по формуле В4*С4*В2.
Для ввода формулы нужно выделить ячейку D4, затем щелкнуть кнопку = в строке ввода формул, щелкнув ячейку В4, ввести первый операнд, щелкнув клавишу «*», задать операцию умножения, щелкнув ячейку С4, ввести второй операнд, щелкнув клавишу «*», задать операцию умножения, щелкнув ячейку В2, ввести третий операнд.
Так как адрес ячейки В2, в которой размещен курс ин. валюты, должен оставаться неизменным при последующем копировании формулы вычисления суммы в рублях, то зададим абсолютную адресацию ячейки В2, для чего щелкнем клавишу F4. После этого в изображении ссылки на ячейку В2 появится знак доллара ($): $В$2. Завершим создание формулы вычисления, щелкнув кнопку ОК. После этого в ячейке D4 немедленно появится результат вычислений, а в строке формул будет изображена формула, по которой выполняется вычисление: =В4*С4*$В$2.
Так как суммы затрат на приобретение остальных наименований оборудования вычисляются по аналогичной формуле, скопируем формулу из ячейки D4 в D5:D9.
Для уточнения порядка копирования формул в справке Excel выберем в меню «?» команду Вызов справки, на вкладке Поиск зададим образ «копирование», в списке найденных разделов выберем Перемещение или копирование формулы и щелкнем кнопку Показать. После этого на экране будет открыто окно справки Excel. Изучив справочную информацию, закроем окно справки, щелкнув кнопку Закрыть окно в правом верхнем углу окна.
|
|
Итак, для копирования формулы из ячейки D4 в D5:D9 выделим ячейку D4, затем установим курсор мыши на рамку выделения и нажмем левую кнопку мыши. Прижав клавишу Ctrl, перетащим мышью выделение с D4 на D5, удерживая нажатой кнопку мыши.
Для копирования формулы в примыкающий к данной ячейке диапазон внутри столбца или строки удобно использовать маркер заполнения. Для этого выделим ячейку D4, содержащую формулу, и перетащим маркер заполнения в диапазон в D5:D9.
Для вычисления итоговой суммы затрат в ячейку D10 введем формулу вычисления суммы ячеек D4:D9, для чего выделим диапазон ячеек D4:D9 и щелкнем кнопку ∑(Автосумма).
Четвертый этап — анализ полученных результатов..
Оцените полученные результаты. Обратите внимание, что фактически составлена небольшая программа, которую можно использовать для многократных пересчетов. Например, если изменить одно или несколько чисел в исходных данных, все суммы будут пересчитаны автоматически. Более того, можно модифицировать структуру таблицы, например, удалить строку с записью о модеме или вставить новую строку и формулы в итогах будут изменены автоматически.
При оценке результатов часто возникает необходимость просмотреть формулы в ячейках таблицы. Для просмотра формулы нужно выделить ячейку и в строке формул будет выведена формула в данной ячейке. Если требуется просмотреть формулы во всех ячейках таблицы на данном листе, то для переключения режимов просмотра формул и просмотра значений формул следует нажать Ctrl + «(левая кавычка).
Изменение режима отображения формул и результатов вычислений на листе можно выполнить, выбрав команду Параметры в меню Сервис. На вкладке Вид" для отображения формул в ячейках включите флажок Формулы. Если вы хотите отображать в ячейках результаты вычислений, то снимите данный флажок.
Пятый этап — редактирование таблицы. В большинстве случаев, после анализа полученных результатов выявляются недочеты, которые требуется исправить. Поэтому редактирование таблицы является важным этапом в ее разработке.
Если в ячейках таблицы появляются ошибки, то можно воспользоваться справкой Excel для уточнения характера ошибки. Вызовите справку, выбрав команду Вызов справки в меню «?». На вкладке Указатель задайте слово «ошибка», затем установите курсор на разделе ошибка ##### и щелкните кнопку Показать. В окне справки Excel Разрешение вопросов, возникающих при появлении ошибок, выбирая тип ошибки, вы можете ознакомиться с причинами возникновения данной ошибки и мерами по ее устранению.
Для изменения содержимого ячейки следует дважды щелкнуть ячейку, затем отредактировать содержимое ячейки. После изменения содержимого ячейки нажать клавишу Enter для сохранения изменений или нажать клавишу Esc, если вы хотите отменить внесенные изменения. Если вы уже нажали Enter, то для отказа от внесенных изменений нужно воспользоваться командой Отменить из меню Правка.
Если нужно, вы можете вставить новые столбцы или строки. Например, для вставки в нашу таблицу строки с наименованием Сетевой фильтр в количестве 2 шт., цена которых 5,60, выделим строку 9 и в меню Вставка выберем команду Строки. После этого все строки, расположенные ниже, сместятся на одну строку вниз, и строка вставится в таблицу. Введем в соответствующие столбцы этой строки данные и скопируем в ячейку D9 формулу расчета из ячейки D8. Обратите внимание, что сумма затрат в ячейке D11 автоматически пересчитана с учетом добавленного оборудования.
Шестой этап — оформление таблицы. Когда таблица проверена, найденные ошибки исправлены, наступает очередь этапа оформления таблицы. Подробную информацию о параметрах форматирования листа, содержимого ячеек вы можете получить, выбрав на вкладке Содержание Справочной системы Excel тему Форматирование листов.
|
|
Итак, задавая высоту строк и ширину столбцов, выбирая тип границ, цвет иузор заливки для ячеек, изменяя шрифт и выравнивание данных в ячейках, вы можете сделать таблицу более наглядной.
Один из самых быстрых способов оформления таблицы заключается в использовании команды Автоформат меню Формат. Для его применения выделите все ячейки таблицы и выберите в меню Формат команду Автоформат. В диалоговом окне Автоформат выберите нужный тип формата в поле Список форматов, а в поле Образец просматривайте вариант оформления таблицы с избранным типом формата. Если нужно сделать дополнительный выбор, то для частичного применения автоформата нажмите кнопку Параметры и снимите флажки для форматов, которые не нужно применять. По окончании выбора нужного типа формата щелкните кнопку ОК и просмотрите результат избранного вами варианта оформления таблицы. Если этот вариант вас не устраивает, то можно воспользоваться отменой операции, выбрав в меню Правка команду Отменить Автоформат.
Седьмой этап — построение диаграмм. ВMicrosoft Excel имеется возможность графического представления данных в виде диаграммы. Диаграммы связаны с данными листа, на основе которых они были созданы, и изменяются каждый раз, когда изменяются данные на листе.
Построим диаграмму, которая будет отображать расходы на приобретение отдельных наименований оборудования. Для построения диаграммы выделим ячейки A3:D10, содержащие данные, которые должны быть отображены на диаграмме.
Примечание. Если необходимо, чтобы в диаграмме были отображены и названия строк или столбцов, нужно выделить содержащие их ячейки.
Щелкнув кнопку Мастер диаграмм, следуя инструкциям мастера, зададим параметры диаграммы:
· на первом шаге выбрать тип диаграммы, например, круговая;
· на втором шаге определить источник данных диаграммы: строки или столбцы и уточнить диапазон ячеек, данные из которых отображаются на диаграмме, на вкладке Ряд можно уточнить состав рядов с данными, участвующих в формировании диаграммы;
|
|
· на третьем шаге задать параметры диаграммы: заголовки, подписи осей и данных, отображение линий сетки, состав и место размещения легенды на диаграмме;
· на четвертом шаге выбрать место размещения диаграммы и щелкнуть кнопку Готово.
Диаграмма будет выведена на экран. Если необходимо отредактировать диаграмму, то, щелкнув на ней мышью, откроем на экране панель инструментов редактирования диаграммы.
Восьмой этап – защита таблицы. Если вы не хотите, чтобы кто-либо, открыв таблицу, увидел, по каким формулам выполняются расчеты, то вы можете скрыть формулы на листе. Для того чтобы таблица была именно такая, как вы предполагали при ее разработке, вы должны быть уверены, что никто не сможет изменить формулы, по которым в ней
выполняются расчеты. С этой целью Excel обеспечивает возможность скрыть формулы и защитить лист от изменений.
Для скрытия формул выделите диапазон ячеек, в которых нужно скрыть формулы. (При необходимости можно выделить несмежные диапазоны ячеек или весь лист.)
Затем в меню Формат выберите команду Ячейки. На вкладке Защита установите флажки Скрыть формулы и Защищаемая ячейка, после чего нажмите кнопку ОК. После этого в меню Сервис выберите команду Защита, а затем — команду Защитить лист. Проверьте, чтобы в открывшемся диалоговом окне был установлен флажок Содержимое.
Примечание. Чтобы скрытые формулы снова изображались в строке формул, снимите защиту листа, выбрав в меню Сервис команду Защита, а затем — команду Сиять защиту листа. После этого выделите группу ячеек, формулы которых нужно показать, в меню Формат выберите команду Ячейки, а затем на вкладке Защита снимите флажок Скрыть формулы.
Девятый этап — сохранение таблицы и использование ее для расчетов. Для сохранения новой книги выберите в меню Файл команду Сохранить как. В диалоговом окне Сохранение документа в поле Папка укажите диск и папку, в которую будет помещена книга. Чтобы сохранить книгу в новой папке, щелкните кнопку Создать папку и, задав ей имя, открыть ее. В поле Имя файла введите имя книги и нажмите кнопку Сохранить.
Примечание. Чтобы упростить в последующем поиск данной книги, в меню Файл выберите команду Свойства. На вкладке Документ введите заголовок книги, тему, автора, ключевые слова и заметки. Данные используются затем для размещения файла в диалоговом окне Открыть (меню Файл).
Десятый этап — печать таблицы. Заключительным и одним из важных этапов в решении задач с помощью электронных таблиц является получение бумажной копии таблицы, так как бумажные копии большинства таблиц, например счета-фактуры, накладные и другие расчетно-финансовые документы, должны быть подшиты в дела. В программе Excel для этих целей предусмотрены разнообразные средства, со многими из которых вы уже познакомились при изучении процессора Word.
Перед выводом таблицы на печать необходимо выполнить подготовку к печати. Вначале целесообразно перейти в режим редактирования таблицы с разметкой страницы, для чего в меню Вид выбрать команду Разметка страниц. После этого на листе Excel будет показано размещение таблицы на странице. Вы можете, ухватив мышью за край страницы, перетащить его в другую позицию, изменив состав ячеек, отображаемых на странице.
Если необходимо распечатать не весь лист Excel, Td можно задать область печати, выделив нужный диапазон ячеек и выбрав в меню Файл команду Область печати — Задать. Область печати можно определить, выбрав в режиме Разметка страницы нужную область и щелкнув правой кнопкой мыши одну из выделенных ячеек, а затем, выбрав в контекстном меню команду Установить область печати.
Для того чтобы увидеть, как будет выглядеть лист, выведенный на печать, со всеми колонтитулами и заголовками печати, нажмите кнопку Предварительный просмотр в панели инструментов Стандартная или выберите аналогичную команду в меню Файл. В режиме предварительного просмотра в нижней части экрана в строке состояния отображаются номер текущей страницы и общее число страниц на выделенном листе, а в верхней части экрана отображается панель управления.
Чтобы увеличить масштаб или вернуться в режим отображения полной страницы, нажмите кнопку Масштаб. Курсор мыши имеет вид лупы, щелкнув мышью в любой области листа, можно увеличить масштаб или вернуться в режим отображения полной страницы. При изменении масштаба размер печатной страницы не изменяется.
Кнопки Назад / Далее служат для просмотра предыдущей / следующей страницы листа. Кнопка Печать служит для установки параметров печати и печати выделенного листа. Щелчок кнопки Страница открывает диалоговое окно настройки параметров распечатываемых страниц. На вкладке Страница этого окна можно выбрать размер бумаги и ориентацию страницы, задать масштаб печати страницы на бумаге. Вкладка Поля позволяет установить размеры полей и расположение колонтитулов на странице. Вкладка Колонтитулы предназначена для создания колонтитулов и ввода в них данных: номер страницы, дата и время, имя файла. Вкладка Лист позволяет определить такие опции печати: печатать ли сетку, заголовки столбцов и строк, определить порядок печати страниц. Кнопка Поля служит для отображения и скрытия маркеров настройки полей страницы. Если маркеры настройки полей страницы отображены, то можно брать их указателем мыши и тащить, изменяя размеры полей страницы, верхнего и нижнего колонтитулов и ширину столбцов. Кнопка Разметка страницы служит для переключения в режим просмотра разрывов страниц. В этом режиме выполняется настройка разрывов страниц активного листа Excel. Также возможно изменение размеров области печати и изменение листа Excel. Кнопка Обычный режим служит для отображения активного листа в обычном режиме. Имя кнопки изменяется на Обычный, если при нажатии кнопки Предварительный просмотр был активен режим просмотра разрывов страниц.
Внешний вид страниц в окне предварительного просмотра зависит от доступных шрифтов, разрешения принтера, количества доступных цветов. Так как в нашем примере лист Excel содержит встроенную диаграмму, то в окне предварительного просмотра он отображается вместе с диаграммой. Если перед нажатием кнопки Предварительный просмотр была выделена диаграмма, Microsoft Excel отобразит только ее. Для закрытия окна предварительного просмотра и перехода на текущий лист служит кнопка Закрыть.
Для вывода подготовленной таблицы на бумагу следует выбрать в меню Файл команду Печать, затем задать параметры печати и щелкнуть кнопку ОК для начала процесса печати. Пронаблюдать процесс печати можно в окне состояния принтера.