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

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

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

- выделить требуемые ячейки и повторить действия, которые выполнялись для поименования одной ячейки;

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

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

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

Визуализация зависимостей и примечания

Можно просмотреть на экране установленные аналитические зависимости между ячейками. Это выполняется через опции Сервис – Зависимости. Состав опций следующий:

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

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

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

4. Убрать все стрелки: убирает все стрелки с листа.

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

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

Задание к работе

1. Загрузить таблицу из предыдущей работы.

2. Сформировать три дополнительные графы справа и определить по каждому студенту максимальную, минимальную и среднюю оценки в сессию. При этом для распространения формул использовать опцию Заполнить меню Правка.

3. Поместить в свободную ячейку, например J1, значение базовой стипендии.

4. Поместить в свободную ячейку, например I1, плановую дату окончания сессии.

5. Сформировать новую графу «Стипендии», рассчитав стипендию для каждого по следующим правилам: если дата окончания сессии студентом превышает плановую дату окончания сессии, стипендия не назначается; иначе анализируются минимальный и средний баллы: если минимальный балл равен 2 или 3, стипендия не назначается; иначе если средний балл равен 5, назначается повышение стипендии на 50%; если средний балл в пределах от 4.5 до 5, стипендия повышается на 25%; в остальных случаях назначается базовая стипендия.

6. Ввести для размера стипендии денежный формат и две значащие цифры после запятой (см. работу 1).

7. Рассчитать общую сумму стипендии по всем студентам.

8. Рассчитать максимальный, средний и минимальный баллы по каждой дисциплине.

9. Добиться того, чтобы таблица размещалась на листе формата А4 (размер шрифта – 14 единиц).

10. Сохранить таблицу на диске.

11. Поименовать ячейки, участвующие в расчетах по назначению стипендии.

12. Внести изменения в формулы, по которым выполняются расчеты стипендии, с учетом поименованных ячеек, используя опцию Заменить.

13. Ввести примечания для заголовка столбца «Стипендия», куда поместить правила назначения стипендии.

14. Сформировать зависимости между ячейками.

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

16. Показать результаты преподавателю.

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

Графики и диаграммы

Цель работы - изучение средств графического представления данных.

Графическое представление данных выполняется в несколько этапов.

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

Открывается первое диалоговое окно Мастера диаграмм. В нем выбирается тип диаграммы и вид (рекомендуется вкладка Стандартные). Затем выбирается опция Далее.

Во втором шаге выводится диапазон данных, отображаемых на диаграмме, причем можно сделать требуемые корректировки. Здесь же указываются, как размещены данные в таблице для отображения их в диаграмме (как правило, в столбцах) – вкладка Диапазон данных. Вторая вкладка этого диалогового окна – Ряд – позволяет в строке Подписи оси Х ввести диапазон данных из таблицы, которые должны отображаться под осью Х. Иначе ось Х помечается порядковыми номерами данных, начиная с 1. Выбирается опция Далее.

В третьем шаге устанавливается формат диаграммы выбранного на предыдущем шаге типа:

- вкладка Заголовки позволяет ввести названия всей диаграммы и осей Х и У;

- вкладка Оси позволяет переключать каждую из осей;

- вкладка Линии сетки переключает координатную сетку на диаграмме;

- вкладка Легенда управляет комментарием к диаграмме: она позволяет, во-первых, различать, какие данные каким цветом выведены на диаграмме и, во-вторых, размещает легенду в нужной части экрана (или вообще отказывается от нее);

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

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

В четвертом шаге устанавливается, где нужно поместить диаграмму – на исходном листе или на отдельном. Заканчивается построение графика нажатием клавиши Готово.

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

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

Задание к работе

1. Загрузить таблицу из третьей работы.

2. Построить 2 диаграммы:

- у первой диаграммы по оси У помещены средний, минимальный и максимальный баллы студентов, по оси Х – фамилии студентов;

- у второй диаграммы по оси У помещены стипендии, по оси Х – фамилии студентов.

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

3. Сформировать название диаграммы и осей Х и У, легенду, подписи данных.

4. Поместить графики на отдельных листах.

5. Сохранить данные в файле (этот файл будет использоваться далее).

6. Показать результат преподавателю.

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

Фильтры и формы

Цель работы – ознакомление с возможностями по выборке данных в списках с помощью фильтров и форм и по модификации данных с помощью форм.

Фильтры

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

Можно указывать фильтр разными способами:

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

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

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

Поиск по сайту: