2. Отформатируйте таблицу

• Измените ширину столбца А так, чтобы весь текст помещался в столбцах

• Измените ширину столбца В так, чтобы данные были отражены корректно

• Установите в ячейках В2 и В6 денежный формат

• Измените цвет ячейки В6

• Создайте рамку вокруг диапазона В2: В6

• Установите шрифт текста в А1 - п/ж, 14п

• Объедините ячейки А1: В1 и отцентрируйте текст

• Отключите сетку командой Сервис|Параметры и на вкладке Вид сбросьте флажок сетка.

3. Переименуйте Лист 1 на Ссуда

4. Удалите лишние листы

Занятие 2. Вычисления по формулам. Построение диаграмм.

Типы ссылок

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

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

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

• относительные,

• абсолютные,

• смешанные (частично абсолютная адресация).

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

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

Смешанная ссылка.

Функции

Под функцией понимают зависимость некоторой переменной (y) от одной (х) или нескольких переменных (x₁, x₂, …, x_n), причем каждому набору значений переменных x₁, x₂, …, x_n должно соответствовать единственное значение зависимой переменной y. Одним из преимуществ программы Excel является наличие большого количества встроенных функций: математических, финансовых, статистических, текстовых и логических.

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

Все функции имеют одинаковый формат записи и включают в себя две части: имя функции и ее аргументы. Аргументы – это данные, которые используются функцией для получения результата. Аргументом функции могут быть: числа (константы), ссылки на ячейки (диапазоны), текст, формулы, другие функции, логические значения и др.

Аргументы записываются в круглых скобках после имени функции и разделяются точкой с запятой ( ; ). Если аргументов нет, то скобки остаются пустыми, например ПИ() – 3,1416…. Некоторые функции могут иметь несколько аргументов, например, функция СУММ может иметь до 30 аргументов. Но при этом общее количество символов в формуле не может быть больше 1024, а строка аргумента может содержать не более 255 символов.

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

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

=СУММ (А1; В2; С3:С13)

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

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

СУММ				=
……..

Кнопка, открывающая список функций

Мастер функций - инструмент, позволяющий ввести функцию по шагам с уточнением параметров на каждом шаге. Мастер функций Excel имеет два диалоговых окна (2 шага).Для вызова мастера функций можно щелкнуть по кнопке в строке формул или выполнить команду основного меню Вставка/ Функция. Открывается окно Мастера функций (рис.)

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

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

Excel содержит более 300 встроенных функций условно разделенных на насколько категорий:

• Математические и тригонометрические,

• Статистически

• Финансовые

• Логические

• Функции даты и времени

• Текстовые

• Ссылки и массивы

• Работа с базами данных

• Проверка свойств и значений

Примеры встроенных функций

Категории функции	Имена функций
Математические и тригонометрические	СУММ, СТЕПЕНЬ, КОРЕНЬ, Градусы, ABC,LN, LOG, EXP, SIN, COS
Статистически	СРЗНАЧ, МАКС, МИН, СЧЕТ, СЧЕТЕСЛИ, ВЕРОЯТНОСТЬ …
Текстовые	НАЙТИ, ЗАМЕНИТЬ. ПОИСК, СЦЕПИТЬ, ПОДСТАВИТЬ
Дата и время	ГОД, МЕСЯЧ, ЧАС, МИНУТЫ, СЕКУНДЫ, ДЕНЬ, СЕГОДНЯ
Логические	ЕСЛИ, И, ИОИ, НЕ, ИСТИНА, ЛОЖЬ.

Для ускорения и облегчения ввода данных при выполнении наиболее часто выполняемых операций суммирования данных (функция сумм) на стандартной панели Excel существует кнопка - Автосумма. С помощью этой кнопки можно вычислить:

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

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

В программе MS Excel термин «диаграмма» используется для обозначения всех видов графического представления числовых данных. Построение графического изображения производится на основе ряда данных. Так называют группу ячеек с данными в пределах отдельной строки или столбца. На одной диаграмме можно отобразить нескольких рядов данных.

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

Наиболее часто используются следующие типы диаграмм:

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

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

• Гистограмма – это столбиковая диаграмма с расположением оси Х снизу. Высота столбиков пропорциональна величине элементов (категории) ряда. Гистограмма может отображать несколько рядов данных.

Для построения диаграмм обычно используют Мастер диаграмм, запускаемый щелчком на кнопке панели инструментов Стандартная или командой Вставка/ Диаграмма. Стартовое диалоговое окно Мастера диаграмм (1 из 4) имеет вид, приведенный на рисунке.

Окно мастера диаграмм

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

Мастер диаграмм строит диаграмму по шагам. Шагов четыре:

1. Тип диаграммы. Доступные формы диаграмм перечислены в списке Тип: на вкладке Стандартные. На вкладке Нестандартные отображается набор полностью сформированных типов диаграмм с готовым форматированием. После задания типа и вида диаграммы щелчком по кнопке Далее переходят к следующему шагу мастера.

2. Источник данных диаграммы. Это второй шаг мастера диаграмм – выбор данных, по которым строится диаграмма. Если диапазон данных был выбран заранее , то в области предварительного просмотра окна мастера появится приблизительное изображение диаграммы. В поле Диапазон при этом содержится описание выбранного диапазона. Если диапазон не был выделен перед началом построения диаграммы, то, установив курсор в поле Диапазон, можно это сделать на данном этапе. На вкладе Ряд можно просмотреть имена и значения рядов данных, удалить и добавить ряды, а также задать категории данных или подписи по оси Х.

3. Параметры диаграммы. На шаге 3 мастер построения диаграмм задают:

на вкладке Заголовки – названия диаграммы, подписи осей;

на вкладке Оси – отображение и маркировка осей координат;

на вкладке Линии сетки – отображение сетки линий, параллельных осям координат;

на вкладке Легенда - описание построенных графиков и местоположение легенды;

на вкладке Подписи данных – отображение надписей, соответствующих отдельным элементам данных на графике;

на вкладке Таблицы данных - представление данных, использованных при построении графика в виде таблицы.

В зависимости от типа диаграммы некоторые из перечисленных вкладок могут отсутствовать.

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

После щелчка Готово диаграмма строится автоматически и вставляется на указанный рабочий лист.

Редактирование диаграмм

Готовую диаграмму можно изменить. Она состоит из набора отдельных элементов, таких как сами графики (ряды данных), оси координат, заголовки, область построения и прочее. При щелчке на элементе диаграммы он выделяется маркерами и при наведении на него указателя мыши описывает всплывающей подсказкой. Открыть диалоговое окно для форматирования элемента диаграммы, можно через меню Формат (для выделенного элемента) или через контекстное меню. Различные вкладки открывшегося диалогового окна позволяют изменять параметры отображения выбранного элемента данных. Если требуется внести в диаграмму существенные изменения, следует вновь воспользоваться Мастером диаграмм.

Чтобы удалить диаграмму, можно удалить рабочий лист, на котором она размещена, или выделить область диаграммы и нажать клавишу Delete.

Контрольные вопросы

1. Как выполняются вычисления в электронных таблицах?

2. Назовите типы ссылок, используемые в формулах.

3. Как работают ссылки разных типов при копировании формул?

4. Что такое функции?

5. Как можно ввести функцию?

6. Что такое Мастер диаграмм?

7. Как построить диаграмму?

8. Как отредактировать диаграмму?

9. Назовите типы диаграмм.