Лабораторная работа условная функция в excel. Лабораторная работа: Использование формул и функций в табличном процессоре Microsoft Office Excel

Лабораторная работа по Microsoft Excel .

Часть I .

Вычисления в Excel . Формулы и функции.

Основным достоинством электронной таблицы Excel является наличие мощного аппарата формул и функций. Любая обработка данных в Excel осуществляется при помощи этого аппарата. Вы можете складывать, умножать, делить числа, извлекать квадратные корни, вычислять синусы и косинусы, логарифмы и экспоненты. Помимо чисто вычислительных действий с отдельными числами, вы можете обрабатывать отдельные строки или столбцы таблицы, а также целые блоки ячеек. В частности, находить среднее арифметическое, максимальное и минимальное значение, среднеквадратичное отклонение, наиболее вероятное значение, доверительный интервал и многое другое.

Формулы.

Формулой в Excel называется последовательность символов, начинающаяся со знака равенства “=“. В эту последовательность символов могут входить постоянные значения, ссылки на ячейки, имена, функции или операторы. Результатом работы формулы является новое значение, которое выводится как результат вычисления формулы по уже имеющимся данным.

Если значения в ячейках, на которые есть ссылки в формулах, меняются, то результат изменится автоматически.

В качестве примера приведем формулы, вычисляющие корни квадратного трехчлена: ax 2 +bx+c=0. Они введены в ячейки A2 и A3 и имеют следующий вид:

=(-B1+ КОРЕНЬ (B1*B1-4*A1*C1))/2/A1

=(-B1- КОРЕНЬ (B1*B1-4*A1*C1))/2/A1

В ячейках A1 , B1 и C1 находятся значения коэффициентов a, b и с, соответственно. Если вы ввели значения коэффициентов a=1, b=-5 и с=6 (это означает, что в ячейках A1 , B1 и C1 записаны числа 1 , 5 и -6 ), то в ячейках A2 и A3 , где записаны формулы, вы получите числа 2 и 3 . Если вы измените, число в ячейке A1 на -1 , то в ячейках с формулами вы получите
числа -6 и 1 .

Использование ссылок и имен.

Ссылка однозначно определяет ячейку или группу ячеек рабочего листа. Ссылки указывают, в каких ячейках находятся значения, которые нужно использовать в качестве аргументов формулы. С помощью ссылок можно использовать в формуле данные, находящиеся в различных местах рабочего листа, а также использовать значение одной и той же ячейки в нескольких формулах.

Можно также ссылаться на ячейки, находящиеся на других листах рабочей книги, в другой рабочей книге, или даже на данные другого приложения. Ссылки на ячейки других рабочих книг называются внешними. Ссылки на данные в других приложениях называются удаленными.

Имя - это легко запоминающийся идентификатор, который можно использовать для ссылки на ячейку, группу ячеек, значение или формулу. Создать имя для ячейки можно в поле имени, или через меню Вставка | Имя. | Присвоить... Использование имен обеспечивает следующие преимущества:

Формулы, использующие имена, легче воспринимаются и запоминаются, чем формулы, использующие ссылки на ячейки.

Например, формула “=Активы-Пассивы” гораздо понятнее, чем формула “=F6-D6 ”.

При изменении структуры рабочего листа достаточно обновить ссылки лишь в одном месте - в определении имен, и все формулы, использующие эти имена, будут использовать корректные ссылки.

После того как имя определено, оно может использоваться в любом месте рабочей книги. Доступ ко всем именам из любого рабочего листа можно получить с помощью окна имени в левой части строки формул.

Вы можете также определить специальные имена, диапазон действия которых ограничивается текущим рабочим листом. Это означает, что эти имена можно использовать лишь на том рабочем листе, на котором они определены. Такие имена не отображаются в окне имени строки формул или окне диалога “Присвоить имя”, если активен другой рабочий лист книги.

Excel автоматически создает имена на основе заголовков строк и столбцов рабочего листа. Подробной информация о создании таких имен содержится в главе “Базы данных”.

После того, как имя определено, вы можете:

Например, определив имя “Прибыль” как “=$F$12”, можно заменить все ссылки на ячейку $F$12 именем “Прибыль”.

Перемещение и копирование формул. Относительные и абсолютные ссылки.

После того как формула введена в ячейку, вы можете ее перенести, скопировать или распространить на блок ячеек.

При перемещении формулы в новое место таблицы ссылки в формуле не изменяются, а ячейка, где раньше была формула, становится свободной. При копировании формула перемещается в другое место таблицы, ссылки изменяются, но ячейка, где раньше находилась формула, остается без изменения. Формулу можно распространить на блок ячеек.

При копировании формул возникает необходимость управлять изменением адресов ячеек или ссылок. Для этого перед символами адреса ячейки или ссылки устанавливаются символы “$ ”. Изменяться только те атрибуты адреса ячейки, перед которыми не стоит символ “$ ”. Если перед всеми атрибутами адреса ячейки поставить символ “$ ”, то при копировании формулы ссылка не изменится.

Например, если в записи формулы ссылку на ячейку D7 записать в виде $D7, то при перемещении формулы будет изменяться только номер строки “7”. Запись D$7 означает, что при перемещении будет изменяться только символ столбца “D”. Если же записать адрес в виде $D$7, то ссылка при перемещении формулы на этот адрес не изменится, и в расчетах будут участвовать данные из ячейки D7. Если в формуле указан интервал ячеек G3:L9, то управлять можно каждым из четырех символов: “G”, “3”, “L” и “9”, помещая перед ними символ “$”.

Если в ссылке используются символы $ , то она называется абсолютной , если символов $ в ссылке нет - относительной . Адреса таких ссылок называются абсолютными и относительными, соответственно.

Абсолютные адреса при перемещении формул не изменяются, а в относительных адресах происходит смещение на величину переноса.

Рассмотрим пример работы с формулами, за основу возьмем Таблицу 1. В данной таблице подсчитываются по формулам поля:

Закупочная цена ($): - в зависимости от текущего курса $, который заносится в отдельную ячейку D1 .

Сумма закупки: = Закупочная цена товара*Количество

Сумма реализации: = Розничная цена товара*Количество

Валовой доход: = Сумма реализации - Сумма закупки

Такие формулы записаны в ячейки таблицы:

Таблица 1

		Сегодня курс							Розничная цена
									виноград
	№ п/п	Наименование		Фирма поставщик	цена (руб)	цена ($)	Кол-во	Сумма закупки	Сумма реали-зации		Валовый доход

В данном примере ячейке D1 присвоено имя «kurs» , которое используется во всех формулах для пересчета закупочной цены в $. Эта ячейка содержит только число (в нашем примере 3.62, был и такой курс когда-то), а пояснительный текст содержится в ячейке С1 .

При подсчете суммы реализации , использован другой прием для того, чтобы можно было правильно копировать формулы: для ссылки на розничную цену конкретного товара используется частично абсолютный адрес: I$2 - в этом адресе запрещено изменение номера строки, поэтому при копировании такой формулы для товара ";бананы"; ошибок не возникнет. Для других товаров нужно создать формулы со ссылкой на их розничную цену.

Функции.

Понятие функции.

Функции в Excel используются для выполнения стандартных вычислений в рабочих книгах. Значения, которые используются для вычисления функций, называются аргументами. Значения, возвращаемые функциями в качестве ответа, называются результатами. Помимо встроенных функций вы можете использовать в вычислениях пользовательские функции, которые создаются при помощи средств Excel.

Чтобы использовать функцию, нужно ввести ее как часть формулы в ячейку рабочего листа. Последовательность, в которой должны располагаться используемые в формуле символы, называется синтаксисом функции. Все функции используют одинаковые основные правила синтаксиса. Если вы нарушите правила синтаксиса, Excel выдаст сообщение о том, что в формуле имеется ошибка.

Если функция появляется в самом начале формулы, ей должен предшествовать знак равенства, как и во всякой другой формуле.

Аргументы функции записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом точка с запятой “; ”. Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов. Внутри скобок должны располагаться аргументы. Помните о том, что при записи функции должны присутствовать открывающая и закрывающая скобки, при этом не следует вставлять пробелы между названием функции и скобками.

В качестве аргументов можно использовать числа, текст, логические значения, массивы, значения ошибок или ссылки. Аргументы могут быть как константами, так и формулами. В свою очередь эти формулы могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах Excel можно использовать до семи уровней вложенности функций.

Задаваемые входные параметры должны иметь допустимые для данного аргумента значения. Некоторые функции могут иметь необязательные аргументы, которые могут отсутствовать при вычислении значения функции.

Типы функций

Для удобства работы функции в Excel разбиты по категориям: функции управления базами данных и списками, функции даты и времени, DDE/Внешние функции, инженерные функции, финансовые, информационные, логические, функции просмотра и ссылок . Кроме того, присутствуют следующие категории функций: статистические, текстовые и математические .

При помощи текстовых функций имеется возможность обрабатывать текст: извлекать символы, находить нужные, записывать символы в строго определенное место текста и многое другое.

С помощью функций даты и времени можно решить практически любые задачи, связанные с учетом даты или времени (например, определить возраст, вычислить стаж работы, определить число рабочих дней на любом промежутке времени).

Логические функции помогают создавать сложные формулы, которые в зависимости от выполнения тех или иных условий будут совершать различные виды обработки данных.

Часть II.

Рабо чая книга Excel . Связь между рабочими листами.

Совместное использование данных.

Листы рабочей книги

До сих пор работали только с одним листом рабочей книги. Часто бывает полезно использовать несколько рабочих листов.

В нижней части экрана видны Ярлычки листов . Если щелкнуть на ярлычке левой клавишей мыши, то указанный лист становится активным и перемещается наверх. Щелчок правой кнопкой на ярлычке вызовет меню для таких действий с листом, как перемещение, удаление, переименование и т.д.

В левом нижнем углу окна рабочей книги находятся кнопки прокрутки с помощью которых можно переходить от одного рабочего листа к другому.

Щелкнув правой кнопкой мыши на кнопках прокрутки ярлычков, можно открыть констектно - зависимое меню для выбора нужного рабочего листа.

Расположение рабочих книг

Предположим, вы хотите видеть на экране сразу все открытые книги, Excel без труда выполнит ваше желание, причем вы сможете легко ориентироваться в своих книгах. С помощью команды Excel Окно Расположить можно расположить открытые рабочие книги на экране четырьмя способами.

Рядом - рабочие книги открываются в маленьких окнах, на которые делится весь экран ";плиточным"; способом;

Сверху вниз - открытые рабочие книги отображаются в окнах, имеющих вид горизонтальных полос,

Слева направо - открытые рабочие книги отображаются в окнах, имеющих вид вертикальных полос;

Каскадом - рабочие книги (каждая в своем окне) ";выкладываются"; на экране слоями.

Переходы между рабочими книгами

Независимо от того, решили ли вы расположить на экране все открытые рабочие книги или просто ";уложили"; их друг на друга в порядке открытия, вы можете легко переходить от одной книги к другой. В Excel предусмотрено несколько быстрых способов перехода к нужной книге. Для этого можно использовать мышь, клавиши экстренного доступа или меню Excel Окно. Вот эти способы:

Щелкните навидимой части окна рабочей книги;

Нажмите клавиши/B> Ctrl + F 6> дляперехода из окна одной книги в окно другой.

Откройте меню Excel Окно. В нижней его части содержится список открытых рабочих книг. Для перехода в нужную книгу просто щелкните по имени.

Копирование данных из одной рабочей книги в другую

С помощью команды Excel Копировать можно копировать данные из одной рабочей книги в другую. Например, вы открыли две рабочих книги одна из которых содержит квартальный бюджет, а другая - годовой. Для экономии времени было бы неплохо скопировать данные по первому кварталу из первой рабочей книги во вторую. При этом исходные данные в первой рабочей книге не изменяв появится копия этих данных.

Чтобы скопировать данные из одной рабочей книги в другую, откройте обе рабочие книги. Выделите данные в первой книге и щелкните на кнопке Копировать панели инструментов Стандартная переключиться в другую книгу, используйте любой из перечисленных выше методов. Например, согласно одному изних, выберите из меню Окно имя второй рабочей книги. Перейдите в нужный рабочий лист и выделите ячейку, в которую предполагаете вставить данные. Щелкните на кнопке Вставить панели инструментов Стандартная . Excel моментально скопирует данные во вторую рабочую книгу.

Перенос данных между рабочими книгами

Эта процедура аналогична копированию данных. Отличие заключается в использовании другой кнопки. Выделите данные в первой рабочей книге и щелкните на кнопке Вырезать панели инструментов Стандартная , чтобы извлечь данные. Перейдя в другую рабочую книгу, выделите нужную ячейку и щелкните на кнопке Вставить панели инструментов Стандартная . В результате Excel удалит данные из первой рабочей книги и вставит их во вторую.

Существует быстрый способ переноса данных рабочего листа (листов) между рабочими книгами. Он состоит в использовании метода ";перетащить и опустить";. Сначала откройте книги, задействованные в операции переноса данных. Выберите из меню Excel команду Окно/ Расположить . В открывшемся диалоговом окне Расположение окон выберите вариант рядом и щелкните на кнопке ОК . Вы должны видеть хотя бы небольшую часть окна каждой рабочей книги. Выделите ярлычок листа (листов), который вы хотите скопировать. Поместите указатель мыши поверх выделенного ярлычка листа, щелкните и, не отпуская кнопку мыши, перетащите ярлычок в окно другой рабочей книги. Когда вы отпустите кнопку мыши, лист будет ";прописан"; в новой (для него) рабочей книге.

Создание связей между рабочими листами и рабочими книгами.

Excel позволяет использовать в таблице данные с других листов и из других таблиц.

Связывание - это процесс динамического обновления данных в рабочем листе на основании данных другого источника (рабочего листа или рабочей книги). Связанные данные отражают любые изменения, вносимые в исходные данные.

Связывание выполняется посредством специальных формул, которые содержат так называемые внешние ссылки . Внешняя ссылка может ссылаться на ячейку из другого рабочего листа той же рабочей книги или на ячейку любого другого рабочего листа любой другой рабочей книги. Например, связи между двумя листами достигается за счёт введения в один лист формулы связи со ссылкой на ячейку в другом листе.

Excel позволяет создавать связи с другими рабочими листами и другими рабочими книгами трех типов:

Использование в формуле связывания ссылки на другой рабочий лист

При работе с большим количеством данных и создании многочисленных paбочих листов для хранения этих данных возникают ситуации, когда формула на одном рабочем листе использует данные из другого рабочего листа. Такие формулы весьма полезны, поскольку избавляют вас от необходимости хранить избыточные данные на многих рабочих листах.

зависимой рабочей книгой , исходной рабочей книгой .

Чтобы сослаться на ячейку в другом рабочем листе, поставьте восклицательный знак между именем листа и именем ячейки. Синтаксис для этого типа формул выглядит следующим образом: =ЛИСТ!Ячейка . Если ваш лист имеет имя, то вместо обозначения лист используйте имя этого листа. Например, Отчет! B 5 .

Если имя содержит пробелы (например, Бюджет 99 ), то при создании ссылок на другие листы это имя необходимо заключать в одинарные кавычки.

Связывание нескольких рабочих листов

Часто встречаются ситуации, когда формула должна ссылаться на диапазон ячеек, включающий два или даже больше листов в рабочей книге. Обычно это происходит, когда создаются идентичные рабочие листы для распределения бюджета за разные периоды времени, для сведений о работе различных бригад или для сведений о продажах в различных регионах. У вас также могут быть разные рабочие листы, но их итоговые значения специально содержатся в ячейках с идентичными адресами. И все эти итоговые значения можно затем свести воедино для получения общего итога в одной формуле, содержащей ссылку на все эти листы и адреса ячеек.

В таких случаях Excel ссылается на диапазоны ячеек с помощью трехмерных ссылок. Трехмерная ссылка устанавливается путем включения диапазона листов (с указанием начального и конечного листа) и соответствующего диапазона ячеек. Например, формула, использующая трехмерную ссылку, которая включает листы от Лист1 до Лист5 и ячейки А4:А8, может иметь следующий вид: = SUM (ЛИСТ1:ЛИСТ5!А4:А8).

Трехмерные ссылки можно включить в формулу и другим способом. Для этого достаточно щелкнуть на рабочем листе, который нужно включить в формулу. Но сначала начните формулу в ячейке, где хотите получить результат. Когда дойдет черед до использования трехмерной ссылки, щелкните на ярлычке первого листа, подлежащего включению в ссылку, затем нажмите (и не отпускайте) клавишу /B> Shift > и щелкните на ярлычке последнего листа, подлежащего включению в ссылку. После этого выделит нужные ячейки. Завершив построение формулы, нажмите клавишу /B> Enter > .

Связывание рабочих книг

При связывании рабочих книг используется ряд терминов, которые вам следует знать. Рабочая книга, содержащая формулу связывания, называется зависимой рабочей книгой , а рабочая книга, содержащая связываемые данные - исходной рабочей книгой .

Связь между двумя файлами достигается за счет введения в один файл формулы связи со ссылкой на ячейку в другом файле, файл, который получает данные из другого, называется файлом назначения, а файл, который предоставляет данные, - файлом-источником.

Как только связь устанавливается. Excel копирует величину из ячейки в файле-источнике в ячейку файла назначения. Величина в ячейке назначения автоматически обновляется.

При ссылке на ячейку, содержащуюся в другой рабочей книге, используется следующий синтаксис: [Книга]Лист!Ячейка . Вводя формулу связывания для ссылки на ссылку из другой рабочей книги, используйте имя этой книги, заключенное в квадратные скобки, за которыми без пробелов должно следовать имя рабочего листа, затем восклицательный знак (!), а после него - адрес ячейки (ячеек). Например " C :\Petrov\[ Журнал1 .хls]Литература"!L3.

Обновление связей

Работая с несколькими рабочими книгами и формулам связывания, необходимо знать, как эти связи обновляются. Будут ли результаты формул обновляться автоматически, если изменить данные в ячейках, на которые есть ссылки в только в том случае, если открыты обе рабочие книги.

Если данные в исходной рабочей книге изменяются в тот момент, когда зависимая книга (которая содержит формулу связывания) закрыта, то связанные данные не обновляются немедленно. Открывая зависимую рабочую книгу следующий раз, Excel запросит от вас подтверждение на обновление данных. Чтобы обновить все связанные данные в рабочей книге, выберите ответ Да. Если у вас есть связи, которые обновляются вручную, или вы хотите сами обновить связи, выберите ответ Нет.

Для удаления листа выберите команду Удалить из контекстно-зависимого меню для ярлычков. Для удаления сразу нескольких рабочих листов предварительно выделите их при нажатой клавише Ctrl .

Проверьте:

знаете ли вы, что такое: рабочая книга Excel; рабочий лист; правила записи формул для связи рабочих листов;

умеете ли вы: вставлять рабочий лист; удалять; переименовывать; перемещать; копировать; открывать окна; закрывать; упорядочивать; осуществлять связь между листами одной и разных рабочих книг.

Лабораторная работа по Microsoft Excel.

Часть III.

Построение диаграмм в EXCEL

С помощью Microsoft EXCEL можно создавать сложные диаграммы для данных рабочего листа. EXCEL представляет 9 типов плоских диаграмм и 6 объемных типов диаграмм. Диаграмма может находиться на рабочем листе вместе с исходными данными или на отдельном листе диаграмм, который является частью книги. Диаграмма, которая находится на рабочем листе, называется внедренной диаграммой. Прежде чем начать построение диаграммы, рассмотрим два важных определения.

Ряд данных - это множество значений, которые надо отобразить на диаграмме. В задании, например , это показатели по тестам.

Итак, ряд данных - это множество значений, которое наносится на диаграмму, а категории - это как бы «заголовки» к ним.

Создание внедренных диаграмм.

Задание 1. Постройте внедренную гистограмму по таблице «Результаты тестирования», (Рис. 1)

Рис. 1

Чтобы построить гистограмму по данным таблицы «Результаты тестирования», выполните следующие действия:

Выделите диапазон, содержащий исходные данные (в данном случае, B 1: H 11 ) и нажмите кнопку Мастер диаграмм (или выберите в меню Вставка команду Диаграмма ). В результате появится окно мастера для построения диаграммы.

Процесс создания диаграммы с помощью мастера включает несколько шагов. После выполнения каждого шага вы можете перейти к следующему, нажав кнопку Далее , или вернуться к предыдущему, нажав кнопку Назад . Вы можете в любое время отказаться от построения диаграммы, нажав кнопку Отмена . Можно также нажать кнопку Готово , чтобы пропустить оставшиеся шаги и построить диаграмму, основываясь на введенной к данному моменту информации.

Первое окно диалога мастера диаграмм предоставляет набор рисунков с типами диаграмм. Выберите тип Гистограмма в левой части окна и вид – Обычная гистограмма в правой части окна. Нажмите кнопку Далее .

Во втором окне мастера диаграмм вы можете подтвердить или задать данные, которые нужно отобразить на диаграмме. Так как мы выделили данные перед нажатием кнопки Мастер диаграмм , поэтому поле Диапазон уже содержит ссылку на диапазон, где хранятся исходные данные. Примечание: при задании диапазона, содержащего исходные данные для диаграммы, имеет смысл включать в него все заголовки, которые идентифицируют ряды данных и категории диаграммы. Мастер диаграмм вставит текст этих заголовков в диаграмму.

Используя переключатели Ряды данных , установите Ряды в столбцах . Примечание: Вкладка Ряд позволяет удалять или добавлять ряды данных из диаграммы . Нажмите кнопку Далее .

В третьем окне Мастера диаграмм устанавливаются различные параметры для создаваемой диаграммы. Во вкладке Заголовки назовите диаграмму «Результаты тестирования ». Во вкладке Линии сетки добавьте основные линии по оси X и Y. Во вкладке Легенда разместите легенду справа от диаграммы. Нажмите кнопку Далее .

В последнем окне диалога Мастер диаграмм , для создания внедренной гистограммы, установите переключатель в поле Поместить диаграмму на имеющемся листе . Нажмите кнопку Готово .

Измените размеры гистограммы:

выделите ее щелчком мыши;

перетащите в нужном направлении один из восьми маркеров выделения.

Создание диаграмм на отдельном листе

Задание 2. Создайте круговую диаграмму по средним показателям тестирования на отдельном листе.

Выделите диапазоны данных, содержащие фамилии студентов и средние показатели тестирования. В нашем случае это несмежные диапазоны ячеек B1:B11 и H2:H21. Для выделения несмежных диапазонов выделите сначала первый диапазон, затем нажмите на клавиатуре кнопку Ctrl и, удерживая ее, выделите второй из несмежных диапазонов.

Нажмите кнопку Мастер диаграмм (или выберите в меню Вставка команду Диаграмма ).

В диалоговом окне Мастера диаграмм выберите тип диаграммы Круговая . Вид диаграммы оставьте выбранным по умолчанию. Нажмите Далее .

Во втором окне диалога Мастера диаграмм убедитесь в правильности выбранного диапазона для построения диаграммы. Нажмите Далее .

В третьем окне диалога выберите вкладку Подписи данных и установите флажок напротив поля Значения . Выбрав вкладку Легенда , добавьте легенду внизу диаграммы.

В последнем окне Мастера диаграмм установите переключатель в поле Поместить диаграмму на отдельном листе . Нажмите Готово .

Настройка элементов диаграммы

Элемент диаграммы – это компонент диаграммы такой, например, как ось, точка данных, название или легенда, каждый из которых можно выделять и форматировать.

Задание 3. Внесите изменения в созданную на отдельном листе круговую диаграмму.

Активизируйте лист Excel, где вами была создана круговая диаграмма.

Добавьте название к диаграмме (или измените его) «Средние показатели тестирования в группе », выбрав команду меню Диаграмма – Параметры диаграммы – вкладка Заголовки .

Отформатируйте введенное название, вызвав контекстное меню щелчком правой клавиши мыши по названию диаграммы и выбрав Формат заголовка диаграммы . Установите рамку вокруг названия диаграммы, выберите цвет, тип и толщину лини для этой рамки по своему желанию. Сделайте установки для шрифта, используемого в названии, по своему усмотрению.

Щелкните в пределах самой диаграммы, активизировав ее. Измените тип диаграммы, вызвав щелчком правой клавиши мыши по области диаграммы контекстное меню и выбрав пункт Тип диаграммы или выполнив в меню Диаграмма команду Тип диаграммы . В окне Тип диаграммы выберите Объемный вариант разрезанной круговой диаграммы . Нажмите ОК.

Выполнив команду Параметры диаграммы из меню Диаграмма , во вкладке Легенда уберите флажок в поле Добавить легенду , а во вкладке Подписи данных включите флажок напротив поля Имена категорий . Нажмите ОК.

Поверните диаграмму на 90 0 . Для этого щелкните правой клавишей мыши по области диаграммы, вызвав тем самым контекстное меню. Выберите пункт меню Формат ряда данных и в открывшемся окне вкладку Параметры . Установите угол поворота 90 0 . Нажмите ОК .

Щелкните мышью в области диаграммы так, чтобы выделенной оказалась одна из долей диаграммы. Вызвав контекстное меню, выберите пункт Формат точки данных или выберите команду Выделенный элемент данных из меню Формат .

Во вкладке Вид окна диалога Формат элемента данных измените цвет заливки доли диаграммы. Для любой другой из долей диаграммы выберите заливку текстурой или узором в дополнительном окне Способы заливки .

Задание 4. Самостоятельно создайте кольцевую диаграмму по результатам тестирования для одного студента из группы на отдельном листе. Настройте ее по своему усмотрению.

Быстрый способ создания диаграмм

Для того чтобы быстро создать диаграмму необходимо выделить исходные данные (в нашем случае это диапазонB 1: H 11) и нажать клавишуF 11. Появится диаграмма на отдельном листе во весь лист.

Задание 5. Создайте диаграмму Результатов тестирования , используя быстрый способ создания диаграмм.

Построения графика

Графики обычно используются для отображения динамики изменений ряда значений.

Задание 6. Постройте график, отражающий динамику результатов тестирования первых трех студентов группы.

Выделите область для построения диаграммы, не захватывая средние показатели тестирования. (В нашем случае это диапазон B 1: G 4 ).

Нажмите кнопку Мастер диаграмм (или выберите в меню Вставка команду Диаграмма ).

В окне диалога Мастера диаграмм выберите тип диаграммы - График и первый из предложенных вариантов вида графиков. Нажмите Далее .

Во втором окне диалоги убедитесь, что диапазон данных для графика выбран верно. Нажмите Далее .

В третьем окне диалога во вкладке Заголовки дайте название диаграммы «График результатов тестирования ». Подпишите оси: Ось X – номер теста; Ось Y – результаты тестирования в % .

Во вкладке Линии сетки добавьте промежуточные линии сетки для оси X и оси Y.

Добавьте легенду справа от диаграммы.

Добавьте к графику таблицу данных. (Вкладка Таблица данных ). Нажмите Далее .

В последнем окне диалога включите переключатель в окне Поместить диаграмму на имеющемся листе . Нажмите Готово .

Изменяя размеры графика, добейтесь наилучшего отображения всех данных приведенных на нем.

Добавьте в полученный график Microsoft WINDOWS 4 2 Подготовка документов в текстовом процессоре Microsoft Word 10 3 Проектирование...

1. Документ

   ... . Лабораторная работа № 5 Тема: Использование логических функций в формулах Цель работы : Знакомство и освоение логических функций в MS Excel : Применение логических функции ...

2. Лабораторный практикум по информатике

   Лабораторная работа

   ... Работа с текстом. Вычисления Лабораторная работа 40. Вычисление выражений с переменными значениями. Математические функции . Ранжированные переменные. Графики функций Лабораторная работа ... . Формулы Для вычислений в Microsoft Excel используются формулы . ...

3. Лабораторный практикум по курсу “субд” для слушателей получающих дополнительную квалификацию «разработчик профессионально-ориентированных компьютерных технологий» лабораторная работа №1 проектирование бд

   Лабораторная работа

   ... части полей по возрастанию, а других полей по убыванию). Лабораторная работа ... вычисляемая формула ; дата... Вычисление итоговых значений для записей формы выполняется с помощью встроенных статистических функций ... документы Microsoft Word и Microsoft Excel , ...

4. К ВЫПОЛНЕНИЮ ЛАБОРАТОРНЫХ РАБОТ ПО ДИСЦИПЛИНЕ «Информационная безопасность в сетях»

   Лабораторная работа

   ... Microsoft Office. Задание на лабораторную работу Изучить теоретический материал по данной лабораторной работе . Ознакомиться с указаниями по ... рабочем листе Excel . В первой части следует... по формуле : Например, Приведем код на Паскале вычисления функции ...

1. Откройте табличный процессор Microsoft Excel 2007 и создайте рабочую книгу с именем Лабораторная работа №1 .

2. Необходимо создать таблицу расчета заработной платы сотрудников предприятия.

3. Для упрощения ввода данных в таблицу создайте раскрывающийся список (рис. 22), содержащий ФИО сотрудников предприятия.

Рис. 22. Раскрывающийся список

4. Вставьте еще один лист в рабочую книгу Excel, используя ярлычок в строке Ярлычок листа .

5. На новом листе создайте список сотрудников (рис. 23).

Рис. 23. Список сотрудников предприятия

6. Для сортировки ФИО по алфавиту выполните команду: вкладка ленты Данные группа Сортировка и фильтр кнопка .

7. Выделите диапазон ячеек А1:А10 и щелкните поле Имя у левого края строки формул. Введите имя для ячеек, например Сотрудники . Нажмите клавишу Enter .

8. Чтобы запретить другим пользователям просмотр и изменение полученного списка, защитите и скройте лист, на котором он находится.

9. Правой кнопкой мыши щелкните по ярлычку листа. В контекстном меню выберите команду .

10. В диалоговом окне Защита листа (рис. 24) введите пароль для отключения защиты листа. В разделе Разрешить всем пользователям этого листа снимите флажки со всех элементов. Нажмите кнопку ОК .

Рис. 24. Диалоговое окно Защита листа

11. В диалоговом окне Подтверждение пароля введите пароль еще раз.

12. Правой кнопкой мыши щелкните по ярлычку листа и в контекстном меню выберите команду Скрыть .

13. Перейдите на Лист 1 и создайте таблицу Расчет заработной платы
(рис. 25). Столбец ФИО заполните, используя раскрывающийся список.

Рис. 25. Структура таблицы

14. Выделите диапазон ячеек, в который требуется поместить раскрывающийся список.

15. На вкладке Данные в группе Работа с данными выберите команду Проверка данных .

16. В диалоговом окне Проверка данных укажите тип и источник данных (рис. 26).

17. Откройте вкладку Сообщение для ввода (рис. 27). Заполните пустые поля.

Рис. 26. Диалоговое окно Проверка данных

Рис. 27. Сообщение при вводе данных

18. Перейдите на вкладку Сообщение об ошибке (рис. 28). Заполните поля Вид , Заголовок и Сообщение .

Рис. 28. Сообщение при ошибке ввода данных

19. Для заголовков таблицы установите перенос текста (кнопка , расположенная на панели инструментов Выравнивание вкладки ленты Главная ).

20. Закрепите два первых столбца и строку заголовков таблицы. Для этого выделите диапазон ячеек C5:I20 и выполните команду: вкладка ленты Вид группа Окно кнопка .

21. Столбец Оклад заполните произвольными данными и установите денежный формат ячеек, используя команду:

вкладка ленты Главная панель инструментов Число в раскрывающемся списке форматов выберите Денежный формат .

22. Составим формулу для вычисления премии, которая составляет 20% от оклада. Любая формула начинается со знака = , поэтому переходим в ячейку F5 ивводим формулу =E5*20% (или =Е5*0,2 ).

23. С помощью маркера автозаполнения (черный крестик возле правого нижнего угла выделенной ячейки) скопируйте формулу в область
F6: F11 .

24. Между столбцами Премия и Подоходный налог вставьте столбец Итогоначислено , в котором посчитайте сумму Оклад+ Премия .

25. Заполните остальные столбцы таблицы, учитывая, что подоходный налог составляет 13% от начисленной суммы.

26. Посчитайте сумму к выдаче в долларах, для этого задайте текущий курс доллара, например 32, и в ячейку J5 введите формулу: =I5/$C$14 . Знак $ используется в формуле для того, чтобы при копировании с помощью маркера автозаполнения, адресация ячейки не изменялась.

27. Для ячеек, в которых содержатся денежные данные, установите соответствующий формат.

28. Используя функцию СУММ , посчитайте общую сумму подоходного налога. Для этого:

· установите курсор в ячейку Н12 ;

· поставьте знак =;

· в строке формул нажмите кнопку ;

· в появившемся диалоговом окне мастера функций (рис. 29) выберите категорию Математические , функцию СУММ ;

· в качестве аргумента функции СУММ выделите диапазон суммирования Н5:Н11 ;

· нажмите кнопку ОК.

29. Аналогичным образом посчитайте общую сумму к выдаче в долларах и общую сумму к выдаче в рублях.

Рис. 29. Мастер функций

30. Найдите среднюю (СРЗНАЧ ), минимальную (MИН ) и максимальную (MAКС ) заработные платы.

31. Используя условное форматирование, обозначьте красным цветом Суммы к выдаче , менее 5 500 руб. Выполните команду: вкладка ленты Главная группа Стили раскрывающийся список Условное форматирование Правила выделения ячеек .

32. Постройте диаграмму Заработная плата сотрудников предприятия
(рис. 30). Выделите одновременно столбцы Ф.И.О. и Сумма к выдаче (удерживая клавишу Сtrl), и на вкладке ленты Вставка на панели инструментов Диаграммы выберите вид Гистограмма .

33. Используя вкладку ленты Макет, вставьте подписи осей и название диаграммы.

Рис. 30. Пример оформления диаграммы

34. Постройте круговую диаграмму, показывающую соотношение между общей суммой к выдаче и суммарным подоходным налогом (рис. 31).

Рис. 31. Пример оформления круговой диаграммы

Лабораторная работа №1. Формулы и функции MS Excel

Подготовить соответствующие ведомости, воспользовавшись приведенными рекомендациями.

Задание 1 . Сформировать объявления о продаже квартир согласно образцу (рис. 1).

Рис. 1. Объявления о продаже квартир

Сгруппировать имеющиеся данные по квартирам в виде списка (рис. 2).

Рис. 2. Данные о квартирах, выставленных на продажу

В ячейку G2 ввести формулу:

А2&" кв., по "&В2&", площадь: "SD2&", "&Е2&"этаж, "&ТЕКСТ(С2;"# ##0р.")&", "&ECJIИ(F2="+"; "телефон"; "телефона нет")

Объясните данную формулу, воспользовавшись справкой.

Для диапазона G3:G5 воспользоваться маркером автозаполнения либо скопировать данную формулу.

При необходимости отформатировать полученные объявления, используя команду Формат | Автоформат.

Задание 2. Сформировать и заполнить ведомость переоценки основных средств производства по форме, приведенной на рис. 3.

В ячейку А1 ввести название ведомости.

В ячейки A4:F4 ввести названия полей ведомости: Наименование объекта, Балансовая стоимость (БС), Износ объекта (ИО), Остаточная стоимость (ОС), Восстановительная полная стоимость (ВПС), Восстановительная остаточная стоимость (вое). Поле Наименование объекта включает следующие строки: Отдел менеджмента и маркетинга, Отдел транспортировок, Сборочный цех, Отделочный цех, Склад № 1, Склад № 2, Склад № 3, Итого.

Формулы для расчетов:

ОС = БС - ИО

ВПС = БС * К

ВОС = ОС * К

где к - коэффициент, равный:

3,3 - если БС меньше либо равен 650 млн руб.;

4,2 - если БС больше 650 млн руб., но меньше 1000 млн руб.;

5,1 - если БС равен 1000 млн руб. или более.

Для формирования автоматических расчетов используйте следующие формулы:

для ячейки D5 : =В5-С5

для ячейки Е5 : =В5*ЕСЛИ(В5<=650;3,3;ЕСЛИ(И(В5>б50;В5<1000);4,2;5,1))

для ячейки F5 : =D5*ЕСЛИ(В5<=650;3.3;ЕСЛИ(И(В5>650;В5<1000);4,2;5.1))

Результирующую строку итого получить использованием, например, для ячейки В12 формулы:

СУММ(В5:В11), либо следует выделить диапазон ячеек B12:F12 и воспользоваться возможностью автосуммирования (нажать кнопку Автосумма на панели инструментов).

Отформатировать полученные в таблице результаты, а также название ведомости.

Рис. 3. Ведомость переоценки основных средств производства

Задание 3 . Сформировать и заполнить отчетную ведомость работы сети компьютерных клубов по форме, приведеной на рис. 4.

Рис. 4. Ведомость работы сети компьютерных клубов

В ячейку А1 ввести название ведомости.

В ячейки АЗ:НЗ ввести названия полей ведомости: клуб, Январь, Февраль, Март, Суммарная выручка, Место, Средняя выручка, процент. Поле Клуб включает следующие строки: Альтаир, Грувит, Полигон, Гелакс, Звезда, Хексен, Антей, Арсенал, Арена, Блиндаж, Итого.

Основные формулы для вычислений, которые копируются для аналогичных вычислений по строкам, представлены в таблице 1.

Формулы для расчета

Отформатировать полученную ведомость.

Задание 4. Сформировать на рабочем листе ведомость "Расчет заработной платы работников научно-проектного отдела "Альфа"(рис. 5).

Рис. 5 Ведомость по расчету заработной платы

В ячейку А2 поместить название ведомости - Расчет заработной платы работников научно-проектного отдела "Альфа", отцентрировать по левому краю (например, командой Формат | Ячейки | вкладка Выравнивание либо соответствующей кнопкой По правому краю (на панели инструментов).

В ячейки АЗ:КЗ ввести названия полей ведомости: № пп, Фамилия И.О., Должность, Тарифная ставка, Стаж, к, Надбавка за стаж, Итого, Процент налога, Удержать, Выплата.

шапке ведомости - к каждому столбцу - создать скрытые примечания. Примечания создаются командой Вставка | Примечание (также можно использовать панель инструментов Рецензирование, которая добавляет командой Вид | Панели инструментов | Рецензирование):

№ пп - номер работника отдела;

Фамилия и.о. - заносятся все фамилии работающих в научно-проектном отделе;

Должность - занимаемая должность на момент заполнения ведомости;

тарифная ставка- денежный эквивалент занимаемой должности;

Стаж- вносится целое число отработанных лет на момент заполнения ведомости;

к- коэффициент за стаж работы;

надбавка за стаж - денежный эквивалент за стаж работы;

итого- начисление заработанной платы с учетом тарифной ставки и стажа работы;

процент налога - определяет процент отчислений в бюджет;

Удержать - денежный эквивалент отчислений в бюджет;

Выплата - сумма, предназначенная к выдаче.

При расчетах в ведомости учитывать следующее:

к, Надбавка за стаж, Итого, Процент налога, Удержать, выплата- вычисляются с помощью соответствующих формул, с использованием автозаполнения или копирования формулы.

Коэффициент к присваивается из следующего расчета:

0,1 - отработано до 5 лет включительно, 0,2- от 5 до 10 лет включительно, 0,25 - от 10 до 15 лет включительно, 0,3 - свыше 15 лет. Формула для ячейкиF4 :

ЕСЛИ(Е4<=5;0,1;ЕСЛИ(И(Е4>5;Е4<=10);0,2;ЕСЛИ(И(Е4>10; Е4<=15);0,25;0,3)))

надбавка за стаж - денежный эквивалент за стаж работы. Формула для ячейки G4 :

G4 :

(вводится командой Формат | Ячейки | вкладка Число, из списка Числовые форматы выбрать Все форматы и в поле Тип ввести указанный формат).

итого - тарифная ставка с учетом стажа. Формула для ячейки Н4 :

Пользовательский формат числа для ячейки Н4 :

Процент налога - учитывает, что: 2% - начисление (по итого) составляет до 7000 р. включительно, 10%- более 7000 р. до 10 000 р. включительно, 20%- более 10 000 р. до 25 000 р. включительно, 35%- превышающие 25 000 р. Формула для ячейки I4 :

ЕСЛИ(Н4<=7000;0,02;ЕСЛИ(И(Н4>7000;Н4<=10000);0,1;ЕСЛИ(И(Н4>10000;Н4<=25000);0,2;0,35)))

Формат числа для ячейки I4 - Процентный.

Удержать - денежный эквивалент налогов. Формула для ячейки J4 :

Пользовательский формат числа для ячейки J4 :

Выплата - сумма К выдаче: Итого без Удержать.

Требования к столбцу стаж:

Создать пользовательский формат данных, учитывающий стаж работы: до 5 лет - данные представлены желтым цветом, от 5 до 10 - синим, от 10 до 15 - зеленым, свыше 15 - красным.

Воспользоваться командой Формат | Ячейки и ввести пользовательский формат для ячейкиЕ4 :

[Красный]# ##0;

а также использовать команду Формат | Условное форматирование.

В случае ввода отрицательного числа лет должно появляться соответствующее окно. Для проверки

ввода чисел использовать команду Данные | Проверка| вкладка Сообщение об ошибке.

Для поля Тарифная ставка - вывести постоянное сообщение: Тарифная ставка. Будьте внимательны при вводе тарифной ставки для получения которого использовать командуДанные | Проверка | вкладка Сообщение для ввода.

В случае ввода отрицательных значений в столбце тарифная ставка появляется соответствующее предупреждение: Тарифная ставка не может быть отрицательной , которое формируется через пользовательский формат:

# ##0,00р.;[Красный]"Тарифная ставка не может быть

отрицательной!"

Решение

Для основных платежей по займу, который погашается равными платежами в конце или начале каждого расчетного периода, в MS Excel XP используется функция:

ОСПЛТ (Ставка, Период, Кпер, Пс, Бс)

(в более ранних версиях MS Excel эта функция называлась ОСНПЛАТ) В нашем случае функция СППЛТ имеет вид: ОСПЛТ(12%, 4, 5, 1000000000)

Ввод данных и расчеты производятся в соответствии с рис. 4.4.

Рис. 11. Расчет основных платежей по займу

В ячейкуВ8 вводится формула:

ОСПЛТ (В5;В6;В4;ВЗ)

Решение

Для вычисления величины постоянной периодической выплаты ренты (например, регулярных платежей по займу) при постоянной процентной ставке используется функция ПЛТ (в ранних версиях - функция ППЛАТ):

ПЛТ(Ставка; Кпер; Бс; Пс; Тип)

В нашем случае функция ПЛТ имеет вид:

ПЛТ(10%/12; 20*12; -(350000* (1-25%)))- ежемесячные выплаты;

ПЛТ(10%; 20; -(350000* (1-25%)))-ежегодные выплаты.

Решение задачи приведено на рис. 12 и 13.

Рис. 12. Расчет ипотечной ссуды

Рис. 13. Формулы для расчета ипотечной суды

Задание 3. Определить, какая сумма окажется на счете, если 52 000 руб. положены на 20 лет под 11% годовых. Проценты начисляются ежемесячно.

Решение

Для расчета будущей стоимости единой суммы вклада используются сложные проценты, а расчетная формула основана на функции БС (в ранних версиях - функция БЗ):

БС(Ставка; Кпер; Плт; Пс; Тип)

Для нашей задачи функция БС примет вид:

БС(11%/12;20*12; ,-52000)

Решение задачи приведено на рис. 14, а формула для ячейки В26 :

БС(B22/B23;B24*B23;;-B21)

Рис. 14. Расчет будущей стоимости вклада

Задание 4 . Облигация номиналом 200 000 руб. выпущена на 7 лет. Предусматривается следующий порядок начисления процентов: в первый год- 11%, последующие три года- по 16%, в оставшиеся

Решение

Для расчета наращенной стоимости облигации по сложной процентной ставке используется функция:

БЗРАСПИС(Первичное; План)

Для нашей задачи функция принимает вид:

БЗРАСПИС(200000; {11%; 16%; 16%; 16%; 20%; 20%; 20%))

Решение приведено на рис. 15, а формула для расчета в ячейке В42 :

БЗРАСПИС(В30;В34:В40)

Аналогичным образом можно использовать встроенные функции MS Excel и для других финансовых расчетов.

Рис. 15 Расчет наращенной стоимости облигации по сложной процентной ставке

Решение

Расчет внутренней скорости оборота инвестиций производится с помощью функции ВСД (в ранних версиях - ВНДОХ): ВСД (Значения; Предположения)

Рис. 16 Рабочий лист для определения первоначальных затрат по проекту

Ввод исходных данных производится в соответствии с рис. 16. Первоначально для расчета величина затрат на проект выбирается произвольно (ячейку для этой суммы можно оставить даже пустой) и производятся вычисления.

В ячейку В12 вводится формула =ВСД(В4:В9).

примера представлен на рис. 18.

Рис. 17 Окно Подбор параметра

Рис. 18 Рассчитанная величина первоначальных затрат по проекту

Пример расчета эффективности неравномерных капиталовложений

Задание 2. Вас просят дать в долг 15 000 руб. и обещают вернуть через год 3000 руб., через два - 5000 руб., через три - 9000 руб. При какой процентной ставке эта сделка выгодна?

Решение

При решении этой задачи следует использовать функцию ЧПС и средство Подбор параметра:

ЧПС(Ставка; значение 1; значение 2; . . .)

Ввод исходных данных производится в соответствии с рис. 19. Первоначально для расчета выбирается произвольный процент годовой учетной ставки (ячейку с этой величиной можно оставить даже пустой) и производятся вычисления. В ячейку В9 вводится формула: =ЧПС(В6;В2:В4)

Рис. 19. Рабочий лист для решения задачи с неравномерными капиталовложениями

В ячейку С7 можно ввести следующую формулу:

ЕСЛИ(B7=1;"год";ЕСЛИ(И(B7>=2;B7<=4);"года";"лет"))

В поле Установить в ячейке введите В9 , т. е. адрес ячейки, в которой необходимо получить искомое значение суммы сделки (15000);

В поле Значение введите 15000, т. е. само искомое значение суммы сделки;

В поле Изменяя значение ячейки введите адрес ячейки - В8, в которой с помощью средства Подбор параметра будет получена необходимая процентная ставка для рассматриваемой задачи (в случае, если такая существует).

Окончательное решение задачи приведено на рис. 20.

Рис. 19. Окно Подбор параметра для задачи о неравномерных капиталовложениях

Рис. 20 Оптимальная процентная ставка

Лабораторная работа №6. Таблица подстановки

Таблица подстановки позволяет проводить анализ изменения результата при произвольном диапазоне исходных данных. На одном рабочем листе можно расположить несколько таблиц подстановок. Это дает возможность одновременно анализировать различные формулы и статистические данные.

Таблицу подстановки можно использовать для:

Изменения одного исходного значения, просматривая при этом результаты одной или нескольких формул;

- изменения двух исходных значений, просматривая результаты только одной формулы.

Использование таблицы подстановки с одной изменяющейся переменной и несколькими формулами

Рассмотрим эту методику на примере расчета ежемесячных выплат по займу (расчет происходит с помощью функции ПЛТ) и платежей по процентам (функция ПРОЦПЛАТ):

Решение такой задачи предполагает следующие шаги:

1. Создать или перейти на рабочий лист, где будет решаться анализируемая задача.

2. Организовать интерфейс таким образом, чтобы все вводимые данные были понятны пользователю:

В соответствующие ячейки рабочего листа вводятся необходимые подписи и данные (рис. 21).

В ячейку В5 - формула: =ПЛТ($В$4/12;$В$3*12;$В$2)

В ячейку D6 - формула: =ПРОЦПЛАТ($D$4;$D$5;$D$3;$D$2)

Рис. 21 Подготовка исходных данных

Совет: При решении задач, связанных с использованием таблицы подстановки, рекомендуется применять в формулах абсолютную адресацию ячеек. Это способствует правильному выполнению вычислений в ячейках рабочего листа.

3. После подготовки исходных данных перейти к тому месту рабочего листа, где будут располагаться рассчитываемые значения в зависимости от изменения одной переменной и от различных рассчитываемых формул (рис. 22).

В верхней строке будущей таблицы над местом расположения результатов указать адрес каждой формулы, для которой нужно получить список результатов. Вместо адреса можно поместить в ячейку саму формулу (ячейки B10 и C10 содержат формулы).

Слева расположить различные значения исходных данных, которые необходимо протестировать.

Рис. 22 Подготовка изменяемого диапазона и расчетных формул для использования одномерной таблицы подстановки

4. Выделить ячейки, которые будут содержать таблицу. При этом самым левым столбцом этой таблицы должен быть столбец исходных значений, а самой верхней строкой должна быть строка анализируемых формул. Результат будет помещен в пустые ячейки.

5. Воспользоваться командой Данные | Таблица подстановки и в диалоговом окне Таблица подстановки (рис. 23) указать, куда и какие значения необходимо подставлять.

В нашем примере - подстановка значений процентной ставки (столбец исходных значений А10:А19) происходит в ячейку В4 , т. к. в этой ячейке изначально указывается величина процентной ставки, входящей в рассчитываемые формулы. Результаты вычислений будут помещены в незаполненные ячейки выделенного диапазона (рис. 24).

Рис. 23 Использование таблицы подстановки

Рис. 24 Рассчитанные значения для одномерной таблицы подстановки

Использование таблицы подстановки с двумя изменяющимися переменными и одной формулой

Рассмотрим эту методику на примере расчета ежемесячных выплат по займу в зависимости от различных сроков погашения и различных процентных ставок. Решение задачи предполагает

следующие шаги:

1. Организовать на рабочем листе соответствующий интерфейс пользователя для некоторого набора входных данных (рис. 25):

Конкретная процентная ставка - 3% (ячейка В4 );

Конкретный срок погашения - 3 года (ячейка ВЗ );

Формула для ячейки В5 :

ПЛТ($В$4/12;$В$3*12;$В$2)

Рис. 25. Подготовка данных задачи

2. Подготовить следующую таблицу (рис. 26):

Изменяемые данные поместить в левый столбец и верхнюю строку- в нашем случае значения процентной ставки (ячейка В4) располагаются в диапазоне В10:В14, а значения срока погашения (ячейка ВЗ ) - в диапазоне C9:F9 ;

На пересечении строки и столбца в верхнем левом углу расположить необходимую формулу или ссылку на нее (ячейка В9 содержит формулу ячейки В5 , в которой обязательна абсолютная адресация ячеек).

Рис. 26. Подготовка диапазона для использования двумерной таблицы подстановки

3. Выделить диапазон ячеек, представляющий собой прямоугольную область и содержащий формулу для расчета, изменяемые данные для двух переменных и ячейки для результатов вычислений.

4. Выполнить команду Данные | Таблица подстановки и в появившемся окне (рис. 27) указать, куда и какие значения необходимо подставлять.

Рис. 27 Использование таблицы подстановки при расчетах по двум параметрам

В рассматриваемом примере подстановка значений процентной ставки (столбец исходных значений В10:В14 ) происходит в ячейку В4 , т. к. в этой ячейке изначально указывается величина процентной ставки, входящей в рассчитываемую формулу, а подстановка значений сроков погашения (строка значений C9:F9 ) - в ячейку ВЗ . Результаты вычислений будут помещены в незаполненные ячейки выделенного диапазона (28).

Рис. 28.Рассчитанные данные с использованием двумерной таблицы подстановки

Замечание: После построения таблицы подстановки нельзя редактировать отдельно взятую формулу внутри таблицы. Значения данных внутри таблицы можно изменить, меняя значения исходных данных в левом столбце и верхней строке.

Исходные данные: затраты по проекту составят 700 млн руб. Ожидаемые доходы в течение последующих 5 лет составят, соответственно, 70, 90,300,250, 300 млн руб. Оценить экономическую

целесообразность проекта по скорости оборота инвестиции, если рыночная норма дохода 12%. Рассмотреть также следующие варианты (затраты на проект представлены числом со знаком минус):

600; 50;100; 200; 200; 300;

650; 90;120;200;250; 250;

500, 100,100, 200, 250, 250.

Рис. 29 Окно Диспетчер сценариев

Решение

Для вычисления внутренней скорости оборота инвестиции (внутренней нормы доходности) используется функция ВСД.:

ВСД (Значения; Предположения)

В нашем случае функция для решения задачи использует только аргумент Значения, один из которых обязательно отрицателен (затраты по проекту). Если внутренняя скорость оборота инвестиций окажется больше рыночной нормы доходности, то проект считается экономически целесообразным. В противном случае проект должен быть отвергнут.

1. Решение приведено на рис. 30. Формулы для расчета:

в ячейкеВ11: =ВСД(В75:В80)

в ячейкеС11: =ЕСЛИ(В84>В82;"Проект экономически целесообразен"; "Проект необходимо отвергнуть")

Рис. 30. Расчет внутренней скорости оборота инвестиций

2. Рассмотрим этот пример для всех комбинаций исходных данных. Для создания сценария следует использовать команду Сервис | Сценарии | кнопка Добавить (рис. 31).

Рис. 31. Добавление сценария для первой комбинации исходных данных

После нажатия на кнопку ОК появляется возможность внесения новых значений для изменяемых ячеек (рис. 32).

Рис. 32. Окно для изменения значений ячеек сценария

Для сохранения результатов по первому сценарию нет необходимости редактировать значения ячеек- достаточно нажать кнопку ОК для подтверждения значений, появившихся по умолчанию, и выхода в окно Диспетчер сценариев (рис. 33).

Рис. 33 Окно Диспетчер сценариев с первым сохраненным сценарием

3. Для добавления к рассматриваемой задаче новых сценариев достаточно нажать кнопку Добавить в окне Диспетчер сценариев и повторить вышеописанные действия, изменив значения в ячейках исходных данных (рис. 34).

На рис. 34 сценарий Скорость_оборота_1 соответствует данным (-700; 70; 90; 300; 250; 300), сценарий Скорость_оборота_2 - данным (-600; 50; 100; 200; 200; 300), сценарий Скорость_оборота_З- данным (-650; 90; 120; 200; 250; 250), сценарий скорость_оборота_4- данным (-500, 100, 100, 200,

Нажав кнопку Вывести, можно просмотреть на рабочем листе результаты расчета для соответствующей комбинации исходных значений.

Рис. 34. Окно Диспетчер сценариев с добавленными сценариями

по расчету скорости оборота инвестиций

Рис. 4.31. Добавление ячеек результата в окно Отчет по сценарию

4. Для полученияитогового отчета по всем добавленным сценариям следует нажать кнопу Отчет в окне диспетчера сценариев. В появившемся окне Отчет по сценарию (рис. 35) выбрать необходимый тип отчета и дать ссылки на ячейки, в которых вычисляются результирующие функции. При нажатии на кнопку ОК на соответствующий лист рабочей книги выводится отчет по сценариям (рис. 36 и рис. 37)

Рис. 36 Отчет типаСтруктура

Рис. 37 Отчет типаСводная таблица по сценариям расчета скорости оборота инвестиций

Решение

Для выполнения задания:

1. Введите данные на рабочий лист в соответствии с рис. 38.

Рис.38 Подготовка данных для построения диаграммы

2. Выделите мышью диапазон А5:В12 и выполните команду Вставка | Диаграмма либо нажмите кнопку мастера диаграмм напанели инструментов Стандартная.

Выбор вида диаграммы - с учетом того, что в примере предложены дискретные статистические данные, подходящий вид диаграммы - гистораммного типа.

Какой ряд выбран в качестве значения данных (заполните поля Ряд и Значения). В нашем случае в поле Ряд вносим объем и в поле Значения - диапазон $В$5: $В$12;

D какой ряд будет служить подписями по оси X - в поле Подписи по оси X вносим год и указываем диапазон $А$5:$А$12.

Рис. 39. Определение рядов данных

4. Отформатируйте полученную диаграмму, используя контекстное меню каждого ее элемента (рис. 40).

Рис. 40. Построенная диаграмма Объем продаж

Задание 2. Построить график функции: у = cos 3 (πx).

Решение

Результат для этого примера представлен на рис. 41.

Для выполнения задания:

1. Задайте область определения X вводом начальных данных: 0 и 0,1, а затем маркером автозаполнения подготовьте весь диапазон А7:А27.

2. В ячейку В7 введите формулу:

=(СОЗ(ПИ()*А7))^3 и скопируйте ее на диапазон В7:В27.

3. Постройте график функции с помощью мастера диаграмм.

4. Отформатируйте полученный график.

Рис. 41 Пример построения графика функции

Решение

А1:J35, A1:J1 )

2. Сформируйте диапазон критериев для расширенного фильтра в соответствии с рис. 43.

Рис. 43. Диапазон критериев для расширенного фильтра к задаче про белые и черные машины

3. Выполните команду

4. Отфильтрованные данные приведены на рис. 44.

Рис. 44. Данные к задаче про белые и черные машины, отобранные расширенным фильтром

Задание 2. Определить, имеются ли в списке (см. рис. 42) машины, год выпуска которых больше 2000 и пробег которых более 100 00 км, но менее 100 000 км, или черные Мерседесы, цена которых более 20 000 у. е., но менее 30 000 у. е.

Решение

1. Откройте список, подлежащий фильтрации (список располагается в диапазоне А1:JЗ5, строка заголовка- в диапазоне A1:J1 )

МЗ:М4 . В ячейку МЗ введите Условие. В ячейку М4 введите формулу:

ИЛИ(И(G2>10000;G2<100000;D2>1990);И(C2="Мерседес";F2="Черный";H2>20000;H2<30000))

3. Выполните команду Данные | Фильтр | Расширенный фильтр.

4. Отфильтрованные данные представлены на рис. 45.

Рис. 45. Данные к задаче о пробеге, отобранные расширенным фильтром

Задание 3. Определить автомобили белого или красного цвета, цена которых меньше средней цены для всех автомобилей и пробег которых больше либо равен среднему пробегу для всех автомобилей (см. рис. 42).

Решение

1. Откройте список, подлежащий фильтрации (список располагается в диапазоне А1:JЗ5 , строка заголовка- в диапазонеA1:J1 )

2. Сформируйте вычисляемый критерий для расширенного фильтра в диапазоне L1:L2 . В ячейку L1 введите Условие. В ячейку L2 введите формулу:

И(ИЛИ(Г2="белый";Г2="красный");Н2<СРЗНАЧ($Н$2:$Н$133); G2 >=СРЗНАЧ($G$ 2:$G$13 3))

3. Выполните команду Данные | Фильтр | Расширенный фильтр.

Решение

1. Выделите список (или - установите в список указатель ячейки) и проведите сортировку (команда Данные | Сортировка) сначала - по полю Продавец, затем - по полю Дата продажи (рис. 47).

Рис. 46. Список продаж

2. Примените команду Данные | Итоги. В окне Промежуточные итоги установите параметры в соответствии с рис. 48: для получения верхнего (первого) уровня итогов - общее количество товаров, проданных конкретным продавцом.

Рис. 47.Сортировка списка

Рис. 48. Окно Промежуточные итоги для получения итогов по полю Продавец

3. Для получения второго уровня итогов поместите указатель ячейки в список с полученными итогами, затем выполните команду Данные | Итоги, установив в окне Промежуточные итоги параметры в соответствии с рис. 49.

Рис.49. Окно Промежуточные итоги для получения итогов по полю Дата продажи

4. Полученные промежуточные итоги представлены на рис. 50.

Рис. 50.Вложенные промежуточные итоги

Консолидация данных

Консолидация предназначается для обобщения однородных данных. Ее осуществление предполагает использование следующей методики:

1. Указать местоположение будущих консолидированных данных.

2. Выбрать команду Данные | Консолидация.

3. В открывшемся окне указать диапазоны данных, подлежащие консолидации.

4. Указать способ консолидации:

Согласно расположению в диапазоне - сняты все флажки области Использовать в качестве имен;

Согласно заголовкам строк и столбцов- установлены

флажки подписи верхней строки и значения левого столбца.

5. Выбрать тип консолидации, т. е. указать, какая операция будет проводиться с консолидируемыми данными.

6. При необходимости указать добавление структуры - установить флажок Создавать связи с исходными данными.

Задание 1. Объединить данные о количестве и стоимости проданных товаров в сети магазинов, которые представлены в виде списка со следующими полями (рис. 51): Товар, Стоимость, Количество, расположены на листе 2, листе 4 и листе 5.

Рис. 51.Данные о реализованных товарах

Консолидация в соответствии с данными рис. 52. Объединенные данные представлены на рис. 53.

Сводные таблицы

Сводные таблицы представляют собой средство для группировки, обобщения и анализа данных, находящихся в списках MS Excel или в таблицах, созданных в других приложениях. Внешне сводные

Рис. 52. Ввод данных в окно Консолидация

Рис. 53. Представление консолидированных данных

Сводные таблицы

Сводные таблицы представляют собой средство для группировки, обобщения и анализа данных, находящихся в списках MS Excel или таблицах, связанных в других приложениях. Внешние сводные таблицы являются структурой, позволяющей размещать данные в трехмерном виде. Сводные таблицы могут использоваться:

Для обобщения большого количества однотипных данных;

Для реорганизации данных (с помощью перетаскивания);

Для отбора и группировки данных;

Для построения диаграмм.

Сводные таблицы создаются с помощью мастера сводных таблиц (команда Данные | Сводная таблица) по следующей методике:

1. Выбрать место для сводной таблицы, т. е. установить указатель ячейки в необходимое место на рабочем листе.

2. Выполнить команду Данные | Сводная таблица.

3. Задать исходный диапазон данных, выполнив шаги 1 и 2 мастера (рис. 54 и 55). После нажатия кнопки Далее в окне мастера, приведенном на рис. 55, откроется окно 3-го шага мастера (рис. 56).

4. Прежде чем указать местоположение будущей таблицы (рис. 56), необходимо нажать кнопку Макет и в открывшемся окне (рис. 57) сформировать макет сводной таблицы (т. е. задать страницу, строки, столбцы, итоговые и вычисляемые поля сводной таблицы).

Рис. 54Определение местоположения данных для сводной таблицы

Рис. 55.Диапазон данных для сводной таблицы

Рис. 56.Указание местоположения будущей сводной таблицы

5. Для определения необходимой операции для полей, помещенных в область Данные, либо задания вычисляемого поля дважды щелкнуть левой кнопкой мыши на поле, помещенном в область Данные (рис. 57), и выбрать необходимые действия в окне Вычисление поля сводной таблицы (рис. 58).

6. Нажать кнопку Параметры (рис. 56) и в открывшемся окне (рис. 59) установить необходимые параметры сводной таблицы.

Рис. 57.Формирование макета сводной таблицы

Рис. 58. Окно Вычисление поля сводной таблицы

Рис. 59. Установка параметров сводной таблицы

7. После проведения всех подготовительных операций нажать кнопку Готово (рис. 56).

При создании, редактировании и работе со сводными таблицами необходимо учитывать следующее:

Местоположение сводной таблицы - желательно располагать ее на отдельном листе, т. к. при обновлении, группировках сводной таблицы информация, заключающаяся на рабочих листах рядом со сводной таблицей, может оказаться скрытой;

Местонахождение исходных данных – список MS Excel, внешний источник, диапазоны консолидации, находящиеся в другой сводной таблице;

Необходимость при создании структуры сводной таблицы определить:

1) поля, находящиеся в строках и столбцах таблицы;

2) поля, по которым подводятся итоги (с выбором необходимой операции);

3) поля для страниц, что позволяет представить информацию в трехмерном виде.

Сводная таблица – это средство только для отображения данных. Поэтому в самой таблице данные редактировать нельзя. Для изменения данных в сводной таблице необходимо внести изменения в источник данных, а затем обновить сводную (кнопкой Обновить данные на панели инструментовСводные таблицы (рис. 60);

Рис. 60. Панель инструментов Сводные таблицы

В сводных таблицах можно изменять названия полей, что не влечет изменений в полях исходных данных. Манипулирование элементами сводной таблицы можно также осуществлять мышью.

ВВЕДЕНИЕ

1.ТЕХНИЧЕСКОЕ ОПИАНИЕ ЗАДАЧИ

1.1 Достоинства и недостатки программного продукта

1.3 Алгоритм установки Excel

1.4 Актуальность темы

1.ТЕХНОЛОГИЧЕСКОЕ ОПИСАНИЕ

2.1 Формулы

2.2 Порядок ввода формул

2.3 Относительные, абсолютные и смешанные ссылки

2.5 Копирование формул

2.7 Просмотр зависимостей

2.8 Редактирование формул

2.9 Функции Excel

2.10 Автовычисление итоговых функций

2.12 Выбор недавно использовавшихся функций

3. ТЕХНИКА БЕЗОПАСНОСТИ

3.1 Требования к помещению для эксплуатации компьютера

3.2 Требования к организации и оборудованию рабочих мест

3.3 Санитарно-гигиенические нормы работы на ПЭВМ

ЗАКЛЮЧЕНИЕ

ПЕРЕЧЕНЬ СОКРАЩЕНИЙ

Список литературы

ПРИЛОЖЕНИЯ

ВВЕДЕНИЕ

Что можно сказать о современных программах обработки электронных таблиц? Сегодня это средства, позволяющие не только выполнять вычисления, но и управлять списками и создавать диаграммы. Если же говорить об EXCEL, которая является одной из наиболее известных программ обработки электронных таблиц, то без преувеличения можно утверждать, что ее возможности практически неисчерпаемы. Лично я считаю, что такие программы на сегодняшний день представляют собой один из наиболее мощных и гибких инструментов, созданных для компьютера.

1.1 Достоинства и недостатки программного продукта

Для того, чтобы лучше понимать причины, по которым стоит заниматься модификацией электронных таблиц (вроде Excel), я привожу обзор основных достоинств и недостатков.

Достоинства

· Реализация алгоритмов в табличном процессоре не требует специальных знаний в области программирования.

· Программа в табличном процессоре оперирует понятиями пространства и отношений, так как создается путём задания взаимосвязи ячеек, расположенных в пространстве листа.

· В отличие от обычного программирования, требующего строгой последовательности команд для работы программы, табличные процессоры “прощают” ошибки и незаконченность структуры.

· Вычисление результата в табличном процессоре может быть разбито на необходимое число шагов, каждый из которых может быть определён через формулу в своей ячейке.

· Ячейки таблицы могут содержать не только формулы, но и простой текст, что позволят описывать и комментировать логику работы программы, располагая на листе текстовые комментарии.

· Весь процесс вычисления осуществляется в виде таблиц,

· Современные табличные процессоры позволяют использовать средства оформления, такие как, например, цвет и шрифты, которые облегчают понимание программы и несут дополнительную смысловую нагрузку.

Недостатки

· Формулы, выраженные в терминах адресов ячеек, при их большом количестве представляют большую проблему, так как адреса ячеек сами по себе не несут никакой смысловой нагрузки.

· Реализация сложной структуры в рамках электронной таблицы требует огромного внимания к деталям, так как автор программы с некоторого момента становится не в состоянии запомнить смысл множества адресов, встречающихся в сотнях формул.

· Программы в табличных процессорах сильно зависимы от своей размерности и жестко привязаны к сетке.

· Темпы разработки табличных программ значительно снижаются за счёт того, что разработчику приходится работать на уровне ячеек.

· Пользователь, имеющий доступ к таблице, может случайно или намеренно внести в неё изменения, которые могут нарушить работу программы.

· Недостаток контроля за исправлениями повышает риск ошибок, возникающих из-за невозможности отследить, протестировать и изолировать изменения.

1.2 Требования к аппаратным и программным средствам

· Персональный компьютер с процессором Pentium 100 МГц или более мощным.

· Операционная система MicrosoftWindows 95 или более поздней версии либо MicrosoftWindowsNTWorkstation версии 4.0 с пакетом обновления 3 или более поздним.

· Оперативная память:

· 16 Мбайт памяти - для операционной системы Windows 95 или Windows 98 (Windows 2000). 32 Мбайт памяти - для операционной системы WindowsNTWorkstation версии 4.0 или более поздней.

1.3 Алгоритм установки Excel

Exсel – достаточно популярная программа, облегчающая работу с цифрами и таблицами, а также позволяющая проводить анализ достаточно больших объемов информации. Программа входит в пакет Microsoft Office. Ее можно купить на диске либо скачать с официального сайта компании Microsoft.

Для того чтобы установить Excel правильно, нужно воспользоваться автоматической установкой всего пакета Microsoft Office. Если его компоненты уже установлены (например, при выборочной установке других программ), или же пользователь в силу различных причин переустанавливает только программу Exсel, тогда нужно в процессе установки убрать все галочки напротив установленных ранее программ, оставив ее лишь напротив установки Exсel (рисунок 1.1.)

Затем появится диалоговое окно, которое сообщит Вам, что установка успешно завершена (рисунок 1.3.)

Рисунок 1.3. Завершение установки

По всей вероятности, Excel - это второй по востребованности компонент Microsoft Office после приложения Word.

По мере информатизации общества табличные процессоры перестают использоваться только для бухгалтерских и финансовых задач и становятся инструментами анализа и визуализации самых различных данных. Будучи самым распространенным табличным процессором, Excel имеет огромную аудиторию пользователей, весьма неоднородную по составу. Поэтому если опытным специалистам не хватает существующих возможностей, то вновь осваивающим продукт не хватает функций, упрощающих овладение элементарными операциями.

В связи с этим развитие продукта ставит перед разработчиками достаточно сложную задачу: «усложнения» для одной категории пользователей и «упрощения» - для другой. По свидетельству разработчиков, основное внимание в Excel 2003 как раз и было направлено на упрощение работы с программой, на оптимизацию доступа к различного рода информации и одновременно на развитие эффективности работы и добавление новых возможностей.

Добавилось множество новых функций, в то же время некоторые «лишние» предупреждения были удалены. Благодаря новым функциям упростилось разрешение целого ряда задач. По мнению ряда специалистов, из всех приложений Office XP больше всего аргументов в пользу обновления дает именно Excel 2003.

Формулы – это выражение, начинающееся со знака равенства и состоящее из числовых величин, адресов ячеек, функций, имен, которые соединены знаками арифметических операций. К знакам арифметических операций, которые используются в Excel относятся: сложение; вычитание; умножение; деление; возведение в степень.

Некоторые операции в формуле имеют более высокий приоритет и выполняются в такой последовательности:

возведение в степень и выражения в скобках;

умножение и деление;

сложение и вычитание.

Результатом выполнения формулы является значение, которое выводится в ячейке, а сама формула отображается в строке формул. Если значения в ячейках, на которые есть ссылки в формулах, изменяются, то результат изменится автоматически.

2.2 Порядок ввода формул

Вводить формулу надо со знака равенства. Это надо для того, чтобы Excel понял, что в ячейку вводится именно формула, а не данные.

Выделим произвольную ячейку, например А1. В строке формул введем =2+3 и нажмем Enter. В ячейке появится результат (5). А в строке формул останется сама формула (рисунок 2.1.)

Рисунок 2.1. Результат формулы

Целесообразно пользоваться скобками при написании формулы. В этом случае вы обезопасите себя от случайной ошибки в вычислениях с одной стороны, а с другой - скобки значительно облегчают чтение и анализ формул. Если в формуле количество закрывающих и открывающих скобок не совпадает, Excel выдаст сообщение об ошибке и предложит вариант ее исправления. Сразу после ввода закрывающей скобки Excel отображает жирным шрифтом (или другим цветом) последнюю пару скобок, что очень удобно при наличии в формуле большого количества скобок.

Введите в ячейку А1 число 10, а в ячейку А2 - число 15. В ячейке А3 введите формулу =А1+А2. В ячейке А3 появится сумма ячеек А1 и А2 - 25. Поменяйте значения ячеек А1 и А2 (но не А3!). После смены значений в ячейках А1 и А2 автоматически пересчитывается значение ячейки А3 (согласно формулы) (рисунок 2.2.)

2.4 Использование текста в формулах

С текстовыми значениями можно выполнять математические операции, если текстовые значения содержат только следующие символы:

Цифры от 0 до 9, + - е Е /

Еще можно использовать пять символов числового форматирования:

$ % () пробел

При этом текст должен быть заключен в двойные кавычки.

Неправильно: =$55+$33

Правильно: ="$55"+$«33»

При выполнении вычислений Excel преобразует числовой текст в числовые значения, так результатом вышеуказанной формулы будет значение 88.

Для объединения текстовых значений служит текстовый оператор & (амперсанд). Например, если ячейка А1 содержит текстовое значение «Юрий», а ячейка А2 - «Кордык», то введя в ячейку А3 следующую формулу =А1&А2, получим «ЮрийКордык». Для вставки пробела между именем и фамилией надо написать так =А1&" "&А2. Амперсанд можно использовать для объединения ячеек с разными типами данных. Так, если в ячейке А1 находится число 10, а в ячейке А2 - текст «мешков», то в результате действия формулы =А1&А2, мы получим «10мешков». Причем результатом такого объединения будет текстовое значение.

Копирование формулы в смежные ячейки производится методом автозаполнения, т.е. протягиванием маркера заполнения ячейки с формулой на соседние ячейки (по столбцу или по строке). Это самый удобный и быстрый способ копирования.

Другие способы копирования формул:

1. выделить диапазон для заполнения (включая ячейку с введенной формулой) и выполнить команду меню ПравкаЗаполнитьВниз (если копирование выполняется по столбцу).

1. протянуть маркер заполнения ячейки с формулой правой кнопкой мыши, в появившемся контекстном меню выбрать нужную команду

1.1. копировать ячейки;

2.6 Имена ячеек для абсолютной адресации

Любой ячейке (диапазону) можно присвоить имя и в дальнейшем использовать его в формулах вместо адреса ячейки. Именованные ячейки всегда имеют абсолютную адресацию.

Имя ячейки не должно начинаться с цифры; нельзя использовать в имени пробелы, знаки пунктуации и знаки арифметических операций. Нельзя также давать имя похожее на адрес ячейки.

Присвоение имени текущей ячейке (диапазону):

Первый способ:

1. щелкнуть в поле адреса строки формул, ввести имя;

2. нажать клавишу .

Второй способ:

1. выполнить команду ВставкаИмяПрисвоить ;

2. в диалоговом окне ввести имя.

Это же диалоговое окно можно использовать для удаления имени, однако следует иметь в виду, что если имя уже использовалось в формулах, то его удаление вызовет ошибку (сообщение – «# имя?»)

программный формула электронная таблица

Команда меню СервисЗависимости формул позволяет увидеть на экране связь между ячейками.

Для просмотра влияющих ячеек, нужно сделать текущей ячейку с формулой и выполнить команду СервисЗависимости формулВлияющие ячейки .

Если нужно увидеть, в какой формуле имеется ссылка на текущую ячейку, то следует выполнить команду СервисЗависимости формулЗависимые ячейки .

Все зависимости в таблице изображаются стрелками. Для удаления стрелок служит команда СервисЗависимости формулУбрать все стрелки .

При необходимости просмотра многих зависимостей удобно отобразить панель инструментов Зависимости командой СервисЗависимости формулПанель зависимостей .

2.8 Редактирование формул

Для редактирования формулы нужно выполнить щелчок в строке формул или дважды щелкнуть в ячейке, содержащей формулу. При редактировании можно изменить адрес ячейки, на которую имеется ссылка, тип ссылки и др.

· выделить в строке формул адрес ячейки двойным щелчком;

· отщелкнуть в таблице ячейку, на которую должна быть ссылка.

Изменение типа адресации:

· выделить адрес ячейки двойным щелчком;

· нажать клавишу .

Для подтверждения внесенных изменений использовать клавишу или кнопку Ввод в строке формул; для отмены изменений – клавишу или кнопку Отмена в строке формул.

2.9 Функции Excel

Функциив Excel используются для выполнения стандартных вычислений в рабочих книгах. Значения, которые используются для вычисления функций, называются аргументами. Значения, возвращаемые функциями в качестве ответа, называются результатами.

Помимо встроенных функций вы можете использовать в вычислениях пользовательские функции, которые создаются при помощи средств Excel.

Чтобы использовать функцию, нужно ввести ее как часть формулы в ячейку рабочего листа. Последовательность, в которой должны располагаться используемые в формуле символы, называется синтаксисом функции. Все функции используют одинаковые основные правила синтаксиса. Если вы нарушите правила синтаксиса, Excel выдаст сообщение о том, что в формуле имеется ошибка.

Если функция появляется в самом начале формулы, ей должен предшествовать знак равенства, как и во всякой другой формуле.

Аргументы функции записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом точка с запятой “; ”. Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов. Внутри скобок должны располагаться аргументы. Помните о том, что при записи функции должны присутствовать открывающая и закрывающая скобки, при этом не следует вставлять пробелы между названием функции и скобками.

В качестве аргументов можно использовать числа, текст, логические значения, массивы, значения ошибок или ссылки. Аргументы могут быть как константами, так и формулами. В свою очередь эти формулы могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах Excel можно использовать до семи уровней вложенности функций.

2.10 Автовычисление итоговых функций

В Excel автоматизирован вызов функций, вычисляющих итоговые значения в диапазонах ячеек с числовыми данными, такие функции выбирают из раскрывающегося списка кнопки Автосуммав панели инструментов (таблица 2.2.)

Таблица 2.2. Итоговые функции

2.11 Использование Мастера функций

Для вызова Мастера функций предназначена кнопка Вставка функции в строке формул или в панели инструментов.

Примечание. Мастер функций можно также вызвать:

· в списке кнопки Автосумма (пункт Другие функции…);

· командой менюВставкаФункция ;

· комбинацией клавиш <Shift > + <F3 >.

Диалоговое окно Мастера функций (рисунок 2.4.) содержит два списка: раскрывающийся список Категория и список функций . При выборе категории отображается соответствующий список функций.

При выборе функции в нижней части окна появляется ее краткое описание. После щелчка на кнопке Ok (или нажатия клавиши <Enter >) имя выбранной функции заносится в строку формул вместе со скобками, ограничивающими список аргументов, и одновременно открывается окно Аргументы функции .

Пример такого окна функции показан на рисунке 2.5.

3.1 Требования к помещению для эксплуатации компьютера

1. Помещение должно иметь искусственное и естественное освещение.

2. В помещении должны быть встроенные или пристенные шкафы (полки) для хранения сумок, портфелей учащихся.

3. Помещение не должно граничить с помещениями, в которых уровни шума и вибрации превышают нормируемые значения.

4. Помещение должно оборудоваться системами отопления, кондиционерами, а также вентиляционными отверстиями.

5. Для внутренней отделки интерьера помещений, в помещении должны использоваться диффузно-отражающие материалы с коэффициентом отражения от потолка – 0,7-0,8; для стен – 0,5-0,6; для пола – 0,3-0,5.

1. Площадь на одно рабочее место во всех учебных заведениях должна составлять не менее 6,0 квадратных метров, а объем не менее 20,0 кубических метров.

2. Перед тем, как сесть за рабочее место необходимо произвести внешний осмотр, убедиться в использовании соединительных кабелей.

3. Не следует подвергать толчкам и вибрациям системный блок и другие части компьютера.

4. Экран должен находиться на расстоянии от глаз – 40-50 см.

5. Не следует прикасаться к токоведущим частям компьютера.

6. Необходимо соблюдать режим работы за компьютером, 40-50 минут непрерывной работы и 5-10 минут перерыва. Если во время работы сильно устают глаза, то необходимо периодически отводить взгляд от экрана на любую дальнюю точку помещения.

1. Периодически перед работой протирать монитор специальной тканью.

2. Не допускать попадания пыли и жидкости на клавиатуру компьютера и дискету и на другие части компьютера.

3. На рабочем месте (за компьютером) не следует употреблять пищу и воду.

4. В помещении должна ежедневно проводиться влажная уборка и по возможности проветривание.

5. Учащиеся обязательно должны иметь при себе сменную обувь и белые халаты

Знание формул и функций и умение работать с ними является основным аспектом успешной и приятной работы в Excel, в противном случае простые вычисления в программе будут затрачивать много времени и сил.

В настоящее время, табличный процессор MSExcel, является самым удобным и распространенным среди аналогичных программ, и я с этим полностью согласна.

АС - автоматизированная система

КС - компьютерная система

ОС - операционная система

ЭВМ - электронно-вычислительная машина

ПК – персональный компьютер

1. Биллиг В.А., Дехтярь М.И. VBA и Office ХР. Офисное программирование. –М.: Русская редакция, 2004. –693 с.

2. Гарнаев А. Использование MS Excel и VBA в экономике и финансах. –СПб.: БХВ–Петербург, 2002. –420 с.

6. Информатика: учебник. Курносов А.П., Кулев С.А., Улезько А.В., Камалян А.К., Чернигин А.С., Ломакин С.В.: под ред. А.П. Курносова Воронеж, ВГАУ, 1997. –238 с.

7. Информатика: Учебник. /Под ред. Н.В. Макаровой – М.: Финансы и статистика, 2002. –768 с.

8. Пакеты прикладных программ: Учеб. пособие для сред, проф. образования / Э. В. Фуфаев, Л. И. Фуфаева. -М.: Издательский центр «Академия», 2004. –352 с.

9. Колесников Р. Excel 97 (русифицированная версия). - Киев: Издательская группа BHV, 1997

Приложение 1

Ввод чисел

Приложение 2

Использование формулы «Автосумма»

Шаталова Л.М.

Лабораторная работа №8

«Вычисления в EXCEL . Использование функций»

Цель работы – изучить возможности стандартных функций программы Excel и приобрести практические навыки использования механизма ввода аргументов функций на примере логических функций, функций ссылок и массивов, а также некоторых финансовых функций.
Программа Excel имеет более трехсот встроенных функций, которые можно использовать как отдельно, так и в составе формулы. Любая функция – это заранее созданная формула, выполняющая определенные операции. Для ввода функций используются элементы управления раздела библиотеки функций вкладки ФОРМУЛЫ ЛЕНТЫ. При выборе пункта «вставить функцию» (Shift + F3) появляется окно «Мастера функций» шаг 1, в котором выполняется выбор конкретной функции (здесь имеется возможность получить справку по выбранной функции). После нажатия клавиши Ok появляется окно шага 2, в котором заполняются строки с аргументами. Справа при этом отображаются введенные данные, а в конце – результат. На этом этапе можно выполнить «работу над ошибками ввода». Функция имеет следующий формат:
Имя функции > (Аргумент 1 ; Аргумент 2 ;…; Аргумент N ) ,
где “;” –разделитель. Разделитель «точка с запятой» определяется Windows .

Аргумент – это: 1)Число или текст, 2)ссылка на адрес ячейки, 3)Диапазон ячеек, 4)Арифметическое выражение (например, А7/А10*35), 5)Другая функция

Программа Excel содержит следующие категории стандартных функций:

Финансовые, Дата и время, Математические, Статистические, Ссылки и массивы, Работа с базой данных, Текстовые, Логические, Проверка свойств и значений, Инженерные, Аналитические.