Учебное пособие ''Табличный редактор Microsoft Excel'' (версий 95, 97), КАЗАНСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ, Индейкин В.В., 1999 год.

означает, что в ячейках A1, B1 и C1 записаны числа 1, 5 и -6), то в ячейках

A2 и A3, где записаны формулы, вы получите числа 2 и -3. Если вы измените

число в ячейке A1 на -1, то в ячейках с формулами вы получите

числа -6 и 1.

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

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

Ссылки указывают в каких ячейках находятся значения, которые

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

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

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

одной и той же ячейки в нескольких формулах.

Можно также ссылаться на ячейки, находящиеся на других листах рабочей

книги, в другой рабочей книге, или даже на данные другого приложения.

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

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

Имя - это легко запоминающийся идентификатор, который можно использовать

для ссылки на ячейку, группу ячеек, значение или формулу.

Создать имя для ячейки можно в поле имени, или через меню

Вставка | Имя | Присвоить... Использование имен обеспечивает

следующие преимущества:

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

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

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

D6”.

43. При изменении структуры рабочего листа достаточно обновить ссылки лишь

в одном месте — в определении имен, и все формулы, использующие эти

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

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

рабочей книги. Доступ ко всем именам из любого рабочего листа можно

получить с помощью окна имени в левой части строки формул.

45. Вы можете также определить специальные имена, диапазон действия

которых ограничивается текущим рабочим листом. Это означает, что эти

имена можно использовать лишь на том рабочем листе, на котором они

определены. Такие имена не отображаются в окне имени строки формул или

окне диалога “Присвоить имя”, если активен другой рабочий лист книги.

46. Excel автоматическое создает имена на основе заголовков строк и

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

содержится в главе “Базы данных”.

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

48. Заменить все соответствующие ссылки этим именем во всех местах

рабочего листа.

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

на ячейку $F$12 именем “Прибыль”.

49. Быстро перейти на поименованную ссылку, заменить ссылки, вставить

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

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

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

скопировать или распространить на блок ячеек.

При перемещении формулы в новое место таблицы ссылки в формуле не

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

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

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

можно распространить на блок ячеек.

При копировании формул возникает необходимость управлять изменением

адресов ячеек или ссылок. Для этого перед символами адреса ячейки или

ссылки устанавливаются символы “$”. Изменяться только те атрибуты адреса

ячейки, перед которыми не стоит символ “$”. Если перед всеми атрибутами

адреса ячейки поставить символ “$”, то при копировании формулы ссылка не

изменится.

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

$D7, то при перемещении формулы будет изменяться только номер строки

“7”. Запись D$7 означает, что при перемещении будет изменяться только

символ столбца “D”. Если же записать адрес в виде $D$7, то ссылка при

перемещении формулы на этот адрес не изменится и в расчетах будут

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

G3:L9, то управлять можно каждым из четырех символов: “G”, “3”, “L” и

“9”, помещая перед ними символ “$”.

Если в ссылке используются символы $, то она называется абсолютной,

если символов $ в ссылке нет — относительной. Адреса таких ссылок

называются абсолютными и относительными, соответственно.

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

относительных адресах происходит смещение на величину переноса.

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

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

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

заносится в отдельную ячейку D1.

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

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

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

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

Таблица 2

| | | | | | | | | |

| | | | | | | |бананы |2.90руб |

| |Расчет валового дохода от реализации товаров |виноград|5.20руб |

| | | | | | | |ананас |6.80руб |

|№ |Наимено|Фирма |цена' |цена'|Коли|Сумма |Сумма |Валовой |

|п/|вание |поставщ|(руб) |($) |чест|закупки |реализац|доход |

|п | |ик | | |во | |ии | |

|1 |бананы |Frutis |2.00руб|$ |200 |400.00ру|580.00ру|180.00руб |

| | | | |1.54 | |б |б | |

|2 |бананы |SUMP |2.32руб|$ |80 |185.60ру|232.00ру|46.40руб |

| | | | |1.78 | |б |б | |

|3 |бананы |Forum |1.98руб|$ |165 |326.70ру|478.50ру|151.80руб |

| | | | |1.52 | |б |б | |

|4 |бананы |Like |1.86руб|$ |320 |595.20ру|928.00ру|332.80руб |

| | | | |1.43 | |б |б | |

|5 |бананы |UFO |2.05руб|$ |120 |246.00ру|348.00ру|102.00руб |

| | | | |1.58 | |б |б | |

|Бананы |Средняя|2.04руб|Итого|885 |1 |2 |813.00руб |

| |: | |: | |753.50ру|566.50ру| |

| | | | | |б |б | |

| | | | | | | | | |

|24|ананасы|Like |5.99руб|$ |60 |359.40ру|408.00ру|48.60руб |

| | | | |4.61 | |б |б | |

|25|ананасы|UFO |6.60руб|$ |55 |363.00ру|374.00ру|11.00руб |

| | | | |5.08 | |б |б | |

|Ананасы |Средн: |6.04руб|Итог:|315 |1 |2 |280.45руб |

| | | | | |861.55ру|142.00ру| |

| | | | | |б |б | |

| |Итого: | | |Итог:|1517|4 |6 |1 491.88руб|

| | | | | | |865.02ру|356.90ру| |

| | | | | | |б |б | |

Того же самого результата получения суммы столбца или строки можно

было достигнуть используя Автосуммирование, для этого необходимо выделить

нужный диапазон ячеек и нажать кнопку[pic].

Серии и диаграммы

1 Серии

В Excel разработан механизм ввода рядов данных (серий). Под сериями

подразумеваются ряды данных, построенные по определенному закону. При этом

данные не обязательно должны быть числовыми, они могут быть и

формализованными текстовыми.

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

создания серий является Прогрессия.

1 Прогрессия

Для построения произвольных серий выполните команду Правка |

Заполнить | Прогрессия. Появится окно диалога Прогрессия, в котором можно

установить параметры серий.

[pic]

Рис. 18. Создание Прогрессии

В окне можно выбрать тип прогрессии Арифметическую, Геометрическую,

Даты и Автозаполнение и как она будет заполняться По строкам или По

столбцам.

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

значение, выбрать Расположение и Тип прогрессии, указать Шаг и Предельное

значение и нажать OK.

Ряды дат и времени дня могут использовать приращения по дням,

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

повторяемость последовательности: дни, недели, месяцы или годы.

Автозаполнение. В этом режиме можно продолжать различные типы данных.

Возможность Автозаполнения логически продолжает некоторые заданные

последовательности, например, Кв.3, Кв.4, Кв.5. Вы можете использовать

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

заполнения на рабочем листе или с помощью окна диалога Прогрессия.

Отметим, что в поле ввода Предельное Значение можно ввести значение,

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

достижения этого значения, то построение ряда прекращается. Поле ввода

Предельное Значение может оставаться пустым. Тогда заполняется вся

выделенная область.

Наиболее интересным параметром в окне диалога Прогрессия является

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

значение шага, опираясь на значения в верхних или левых ячейках выделения,

для создания наиболее близкой к ряду прямой (для линейного тренда) или

экспоненциальной линии (для экспоненциального тренда). При установке этого

флажка значение из поля ввода Шаг игнорируется.

2 Создание серий

Кроме прогрессии серии можно создавать еще несколькими способами без

помощи меню, они приведены ниже.

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

В ячейку вводится первый член ряда. Указатель мыши подвести к черному

квадрату в правом нижнем углу выделенной ячейки (в этот момент белый

крестик переходит в черный) и нажать левую кнопку мыши. Удерживая нажатой

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

заполнится данными.

[pic] [pic]

Рис. 19. Первый способ создания серий

Такой простой процесс ввода возможен только тогда, когда в ячейку

введен один из элементов имеющегося списка. Списки создаются в диалоговом

окне Файл | Параметры на закладке Списки.

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

Можно построить серию данных и другим способом, если указать шаг

построения. Для этого нужно ввести вручную второй член будущего ряда,

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

выделения, продолжить выделение до нужной области. Две первых ячейки,

введенные вручную, задают шаг серии данных.

[pic]

Рис. 20. Второй способ создания серий

Данный способ является самым удобным для создания простых серий (типа

арифметической прогрессии).

3 Третий способ

Третий способ является самым универсальным для создания серий любого

цифрового типа арифметической и геометрические прогрессии, степенные ряды и

другие сложные серии.

[pic] [pic]

Рис. 21. Третий способ создания серий

Для этого в первой ячейки серии вводится ее начальное значение, во

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

ENTER. Затем, воспользовавшись правым-нижним углом выделения, продолжить

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

Как уже говорилось выше третий способ является самым универсальным.

2 Диаграммы и графики

Представление данных в графическом виде позволяет решать самые

разнообразные задачи. Основное достоинство такого представления —

наглядность. На графиках легко просматривается тенденция к изменению. Можно

даже определять скорость изменения тенденции. Различные соотношения,

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

графиках.

1 Типы диаграмм

Microsoft Excel для Windows предлагает несколько типов плоских и

объемных диаграмм. Эти типы включают разные форматы. Если их не достаточно,

возможно создать собственный пользовательский формат диаграммы.

[pic]

Рис. 22. Типы диаграмм

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

Для построения диаграммы выполните команду Вставка | Диаграмма или

нажмите кнопку Мастер диаграмм[pic].

Метками строк и столбцов называются заголовки строк и столбцов. Если вы

не включаете метки строк в область построения диаграммы, то на

4 шаге построения диаграммы нужно указать, что под метки строк

отводится 0 строк.

Метки столбцов являются текстом легенды. Легенда представляет собой

прямоугольник, в котором указывается каким цветом или типом

линий выводятся на графике или диаграмме данные из той или иной

строки.

XY-точечная диаграмма является наиболее подходящим типом диаграммы

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

Линии тренда можно проводить на гистограммах, графиках, линейчатых и

XY-точечных диаграммах.

В Excel для отображения диаграмм можно использовать не только

столбцы, линии и точки, но и произвольные рисунки. При построении графиков

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

гладкие кривые.

Excel поддерживает при построении графиков логарифмическую шкалу как

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

можете ввести логарифмическую шкалу, а для другой — линейную.

Рассмотрим построение диаграмм на примере данных приведенных в

таблице 3 и 4. Любая диаграмма строиться на основании таблиц. Построим

круговую диаграмму «Валового дохода». Круговая диаграмма строится на одном

ряде данных, значения элементов ряда отображаются как сектора диаграммы

(величина сектора зависит от величины элемента ряда). В нашем примере нужно

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

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

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

зависимости от установленного на таблице фильтра.

Процедура создания диаграммы следующая:

54. Запустить Мастер диаграмм [pic]

55. Указать место и размер будущей диаграммы, с помощью курсора имеющего

вид[pic]

56. В первом шаге мастера указать диапазоны ячеек: $B$5:$C$23; $I$5:$I$23,

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

будет указываться в запросе на ввод диапазона ячеек[2]

57. На последующих шагах выбирается круговой тип диаграммы и ее подтип

[pic]

Рис. 23. Подтипы Круговой диаграммы

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

будет отображена как данные, а какая - как метки сегментов и названия.

Порядок построения диаграмм в разных версиях Excel немного отличается

(смысл остается тот же, диалоги разные)

1 Excel 7.0/95

59. На последующих шагах выбирается круговой тип диаграммы и ее подтип

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

будет отображена как данные, а какая - как метки сегментов и названия.

[pic]

Рис. 24. Определение меток

[pic]

2 Excel 8.0/97

61. Выделить столбец таблицы "Валовый доход" (включая название столбца)

62. Запустить Мастер диаграмм

63. На 1-м шаге мастера выбрать круговой тип диаграммы.

[pic]

Рис. 25. Шаг 2

64. На 2-м шаге мастера выбрать закладку "Ряд" и установить следующие

значения в 3 полях ввода: "Имя" - адрес ячейки содержащей текст

"Валовый доход" (уже должно стоять) "Значения" - адрес блока ячеек со

значениями валового дохода (уже должно стоять) "Подписи категорий" -

адреса ячеек таблицы с наименованиями товаров и фирм-поставщиков (это

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

в конце поля. По окончании заполнения, снова нажать эту кнопку для

возврата к окну мастера).

65. На третьем шаге - выбрать закладку "Подписи данных" и установить

переключатель в положение "доля"

[pic]

Рис. 26. Шаг 3

66. Последний шаг мастера позволяет выбрать один из вариантов расположения

графика: на отдельном листе рабочей книги (новом) или на текущем

листе.

Диаграмма из нашего примера - это как бы заготовка для отображения

различной информации в зависимости от устанавливаемого фильтра или

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

товару "Стол", получим следующий вид диаграммы:

[pic]

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

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

Для редактирования диаграммы:

67. Дважды нажмите мышью в любом месте диаграммы.

Страницы: 1, 2, 3, 4, 5, 6