Графическое оформление данных

Шрифт, граница, вид

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

Средства форматирования с использованием шрифтов в Excel аналогичны средствам Word. Рамки могут применяться для ячейки или выделенной области. Опции рамок могут быть установлены при использовании закладки Граница (рис.11).

Рис. 11. Формат ячеек

Некоторые из доступных стилей рамок представлены в поле Тип линии.

1. Выделите ячейки для форматирования.

2. Откройте панель диалога Формат, Ячейки и выберите закладку Граница.

3. Выберите стороны для ячеек, в которых будет установлена рамка.

4. Выберите Тип и цвет линии.

5. Нажмите ОК.

Внешний вид ячеек может быть улучшен при заполнении их цветом и/или узором. Цвета и узоры (включая цвет узора) могут быть установлены при использовании закладки Вид окна диалога Формат, Ячейки. Выбранная заливка и узор показываются в поле Образец.

 

Автоформат, условное форматирование

Автоматические форматы Excel - это заранее определенные сочетания числового формата, шрифта, выравнивания, границ, узора, ширины столбца и высоты строки (рис.12).

Рис. 12. Автоформат, условное форматирование

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

1. Введите нужные данные в таблицу.

2. Выделите диапазон ячеек, которые необходимо форматировать.

3. В меню "Формат" выберите команду "Автоформат". При этом откроется окно диалога.

4. В окне диалога "Автоформат" нажмите кнопку "Параметры", чтобы отобразить область "Изменить".

5. Выберите подходящий автоформат и нажмите кнопку "Ок".

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

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

В открывшемся окне выбрать тип оформления таблицы и нажать кнопку ОК.

Ввод формул

Выполнение расчетов в таблицах Excel осуществляется при помощи формул.

Рис. 13. Создание формулы

Для создания формулы, которая будет вычисляться для каждой строки таблицы, необходимо в пустой ячейке ввести знак = после этого нажать на ячейку первого числа (вместо чисел появляется адрес ячейки!), после этого ввести на клавиатуре действие (+,-,*,/) и нажать на вторую ячейку (рис.13).

 

После нажатия на клавишу [Enter] или [Tab] в ячейке появится вычисленное значение.

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

Рис. 14. Создание формулы при помощи автопродолжения

Для некоторых вычислений требуется использовать фиксированную строку или столбец, для этого в формуле используется знак $. Например для таблицы умножения в ячейке F4 необходимо исправить формулу на =$B4*F$2, т.е. зафиксировать первый столбец B и первую строку таблицы 2 (рис.14).

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

(рис. 15).

Рис. 15. Пример расчетов

 

 

Суммирование и другие функции

Для суммирования по столбцу или строке достаточно нажать кнопку:

при этом появляется пунктирная рамка, показывающая ячейки, которые будут просуммированы. Для изменения области суммирования достаточно выделить нужные ячейки и нажать клавишу [Enter]

Для вызова других функций нажмите кнопку , расположенную рядом с

При этом появится окно выбора функций (рис.16).

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

Все функции разбиты по категориям.

Наиболее полезными являются:

- «10 недавно использовавшихся» - это те функции, которые Вы используете;

- «Математические» - это функция СУММ(), ОКРУГЛ(), ЦЕЛОЕ(), SIN(), COS()…;

- «Статистические» - это среднее СРЗНАЧ(), отклонение от среднего СТАНДОТКЛОН();

- «Дата и время» - работа с датами, функции ТДАТА() – выдают текущую дату или текущее время в зависимости от формата ячейки.

Функция реализуется с помощью специального зарезервированного набора символов. Общая структура функции выглядит так:

=ФУНКЦИЯ(АРГУМЕНТ1;АРГУМЕНТ2;…;АРГУМЕНТn)

Не понятно? Тогда более конкретный пример, функция суммирования: =СУММ(1;2;A1)

Расшифруем: эта функция сложит числа «1» и «2» с содержимым ячейки А1.

Функции могут иметь немного более сложную структуру, например функция условия:

=ЕСЛИ(B14>1;"Да";"Нет")

Или сложного условия: =ЕСЛИ(B14>1;ЕСЛИ(B14>5;"Да";"Не знаю");"Нет")

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

=СУММ(А1:А1000).

 

Автосуммирование

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

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

2. Щелкните кнопку Автосумма - ∑ или нажмите комбинацию клавиш Alt +=. Excel примет решение, какую область включить в диапазон суммирования, и выделит ее пунктирной движущейся рамкой, называемой границей.

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

 

Относительные и абсолютные адреса ячеек

Относительные ссылки

Большинство ссылок в формулах записываются в относительной форме - например, С3 (столбец)(строка)

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

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

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

При копировании формулы с относительной ссылкой (столбец)(строка) на n строк ниже и на m столбцов правее ссылка изменяется на (столец+m)(строка+n).

Абсолютные ссылки

Некоторые ссылки в формулах записываются в абсолютной форме - например, $С$3

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

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

Изменение типа ссылки

Для того, чтобы превратить относительную ссылку в абсолютную, достаточно в режиме редактирования формулы установить курсор непосредственно за ссылкой и нажать клавишу <F4>

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

При помощи символа абсолютной адресации Вы можете гибко варьировать способ адресации ячеек. Например $B11 обозначает, что при копировании формул будет изменяться только адресация строки ячейки, а при обозначении B$11 - только столбца. Такая адресация называется смешанной.

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

 

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

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

Например назначив ячейки B11 имени курс можно ввести следующую формулу

=F14*курс.

Рис. 17. Присвоение имени

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

Для того, чтобы назначить имя ячейки необходимо

1. Выделить ячейку

2. Выполнить команду меню Вставка - Имя – Присвоить

3.Введите имя в стоке имя ячейки, например курс

4. Нажмите кнопку OK

5. Введите формулу (результат можно увидеть на рис.18)

Рис. 18. Абсолютная адресация

Маркер автозаполнения

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

Обратите внимание, что если нажата клавиша (Ctrl) при движении маркера экранная подсказка будет содержать одно и то же копируемое значение. Этим свойством удобно пользоваться для нумерации строк базы данных. Введите первое числовое значение (1 или 100, не имеет значения) в первую строку, нажмите и удерживайте (Ctrl) во время перетаскивания маркера заполнения. Excel пронумерует остальные строки (поместив в ячейки значения 2, 3,4 и т. д. или 102, 103 и т. д.). Если во время автозаполнения клавиша (Ctrl) нажата не была, щелкните на кнопке Параметры автозаполнения, чтобы открыть меню, и затем установите в нем переключатель Заполнить (Fill series). При использовании автозаполнения для ввода в рабочую таблицу последовательности данных не обязательно начинать с первого элемента списка. Например, чтобы ввести в качестве названий столбцов последние шесть месяцев года (с Июля по Декабрь), введите в ячейку слово Июль и перетащите маркер заполнения вправо, пока в экранной подсказке не появится слово Декабрь. Можно использовать и обратный ввод последовательности данных, для этого переместите маркер автозаполнения влево (программа введет значения Июнь,...

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

 

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

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

 

а) выбираем функцию	б) выбираем интервалы
Рис. 19. График функции

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

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

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

2. Забиваем в столбец с аргументом x (столбец B) значения x так, чтобы вас устраивал выбранный отрезок, на котором вы будете рассматривать график функции. В ячейку C3 забьём формулу функции, которую вы собираетесь строить. Для примера рассмотрим функцию y = x3.

 

Формулы в Excel всегда начинаются со знака "=". В нашей формуле (=B3^3) происходит возведение числа из ячейки B3 в степень 3 (оператор ^). То же самое можно реализовать с помощью функции "=B3*B3*B3".

Рис. 20. Интервалы функции

 

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

 

 

3. Перейдём непосредственно к построению графика. Меню «Вставка» → «Диаграмма» (рис.21):

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

5. В появившемся окне нажимаем вкладку «Ряд». Добавляем ряд нажатием кнопки «Добавить» (рис.22).

Рис. 21. Построение графика	Рис. 22. Исходные данные

 

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

После этого нужно выделить те ячейки, откуда будут взяты значения для x и y.

6. Вот что получилось. Последний шаг — нажимаем «готово» (рис. 23).

Рис. 23. График

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

 

2.5. Построение диаграмм

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

Диаграмма строится в некоторой системе координат. По одной из осей отсчитываются категории, по второй — значения. Диапазон значений определяется автоматически на основе имеющихся данных.

В зависимости от типа диаграммы данные отображаются на ней разным способом. Можно отметить несколько стандартных типов (рис.24):

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

2. Круговая диаграмма. В этом типе диаграмм величины отражаются секторами круга. Чем больше величина, тем большую долю круга занимает ее отображение. Объемный вариант такой диаграммы предусматривает “нарезку” некоторого диска.

3. График. В этих диаграммах значения величин отображаются высотой точки графика. Точки соединяются линиями.

4. Поверхность. Диаграмма, в которой ряды становятся линиями для некоторой поверхности в объеме.

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

Рис. 24. Графики и поверхности в Excel

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

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

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

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

Для того, чтобы создавать графики необходимо выделить нужные столбцы (выделить первый столбец, затем, удерживая клавишу [Ctrl] выделить следующий столбец). После этого достаточно нажать кнопку или главное меню Вставка – Диаграмма. При этом открывается мастер создания диаграмм (рис.25, 26), который состоит из четырех шагов. На первом шаге определяется тип диаграммы: обычно это Гистограмма, График или Круговая. После выбора типа диаграммы нажмите Далее>>

 

а) мастер диаграмм (шаг 1)	б) мастер диаграмм (шаг 2)
Рис. 25. Построение диаграмм

На втором шаге показывает предварительный вид Вашей диаграммы.

Если на этом шаге ничего не показывает, значит Вы не сделали выделение нужных столбцов. Нажмите кнопку в конце стоки Диапазон и сделайте выделение столбцов.

 

а) мастер диаграмм (шаг 3)	б) мастер диаграмм (шаг 4)
Рис. 26. Построение диаграмм

 

На третьем шаге определяются основные параметры диаграммы: Заголовок, Оси, Линии сетки, Легенда (подписи при наличии нескольких рядов), подписи данных.

Выберите нужные параметры и нажмите Далее>>

На последнем шаге выберите размещение диаграммы (на отдельном листе или на имеющемся, радом с таблицей) нажмите Готово

Построение поверхностей

Рассмотрим ход построения поверхности на функции z=3x+y-5 (рис.27).

1. Добавьте ещё один лист в рабочий файл (Вставка - Лист). Назовите его «Исходные данные поверхности z=3x+y-5». На этом листе заполните таблицу исходных данных для построения графика функции.

2. Заполним строку значений аргумента Х, начиная с ячейки В9 (хє [-3;0], h=0,2 → автозаполнение).

3. Заполним столбец значений аргумента У, начиная с ячейки А10 (ує [-2;0], h=0,2 → автозаполнение по столбцу).

4. В ячейку В10 запишем формулу: =3*B$9+$A10-5 (знак $ нужен для того, чтобы зафиксировать строку). Скопируем эту формулу на все ячейки диапазона B10:Q20 (автозаполнение по столбцу и по строке).

5. Не снимая выделение с диапазона, вызовем Мастер диаграмм.

6. Далее выполним все этапы построения диаграммы аналогично рассмотренным выше.

Дополнительные указания:

а) первое диалоговое окно мастера диаграмм – Тип диаграммы → Стандартные выберем Поверхность.

б) четвёртое диалоговое окно мастера диаграмм - Размещение диаграммы → Выберем на отдельном.

 

а) таблица значений	б) поверхность
Рис. 27. Построение поверхности