Использование Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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. Дважды нажмите мышью в любом месте диаграммы.

68. Команда Формат обозначения легенды устанавливает цвет линий, их стиль

и толщину.

69. Для форматирования объекта диаграммы нажмите на нем правую кнопку мыши

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

70. Для замены одного ряда данных в диаграмме другим примените команду

Формат ряда.

71. Изменяя линии графика, можно изменить данные на рабочем листе.

72. Команда Тип диаграмм позволяет изменять тип уже существующей

диаграммы.

73. Команда Автоформат меняет не только тип диаграммы, но и устанавливает

стандартные параметры диаграммы.

74. Команда Объемный вид изменяет пространственную ориентацию диаграммы.

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

Одна группа — гистограмма, а другая может быть графиком, с областями или XY-

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

данных любой тип плоской диаграммы.

Вы можете изменить формат диаграммы, которую Excel строит по

умолчанию.

Команда Автоформат создает пользовательский автоформат для построения

диаграмм и графиков. Для изменения формата построения стандартных диаграмм

в окне диалога “Параметры” выполните команду Сервис | Параметры и выберите

вкладку “Диаграмма”.

Работа со Списками и Базы данных[3] в Excel

В общем смысле термин база данных можно применить к любой

совокупности связанной упорядоченной информации, объединенной вместе по

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

транзакций, активов, пассивов и др. Основным назначением баз данных

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

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

списка. Одной из наиболее часто решаемых с помощью электронных таблиц

является обработка списков. Вследствие этого Microsoft Excel имеет богатый

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

данных. Ниже приведено несколько советов по работе со списками.

В Excel базы данных размещаются в таблицах. Каждая таблица состоит из

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

соответственно.

Информация в базах данных имеет постоянную структуру. Каждую строку

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

записи содержится в полях.

При работе с базами данных в Excel прежде всего нужно ввести

заголовки столбцов. После этого вы можете ввести информацию в базу данных.

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

Данные | Форма.

75. На рабочем листе можно обрабатывать несколько списков, но все же лучше

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

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

уникальные заголовки столбцов, но все-таки это лучше сделать.

Что касается терминов база данных и список, то это фактически

синонимы. Но однако, будем придерживаться принятого в Microsoft соглашения

и называть таблицы в документах Excel списками. Базой данных будут называть

файлы таблиц, созданные другими системами обработки данных, такими как

Microsoft Access, dBase или Microsoft FoxPro.

1 Формирование списка

Эффективность списка обусловлена следующими характеристиками:

77. Каждый столбец должен содержать однородную информацию. Например, в

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

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

приема на работу и т.д.

78. Одна или две верхние строки в списке должны содержать метки,

описывающие назначение соответствующего столбца.

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

80. В идеале на рабочем листе не должно быть ничего, кроме списка. Если

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

мере одной пустой строкой и одним пустым столбцом.

81. Если планируется фильтровать список, не помещайте какие-либо данные на

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

Чтобы подчеркнуть заголовки, воспользуйтесь вкладками Шрифт или Рамка

диалогового окна Формат ячеек. (Оно открывается по команде Формат | Ячейки)

Не вставляйте отдельную строку из дефисов или знаков равенства, так как

Excel может принять такое подчеркивание за данные.

Новые данные можно добавлять непосредственно в конец списка. Весьма

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

стандартной экранной формы. Для этого выделите какую-либо ячейку в списке и

выполните команду Данные | Форма.

Примечание Перед выполнением команды Данные | Форма должна быть выделена

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

неверные заголовки столбцов.

Совет. В Excel есть команда Текст по столбцам, которая дает

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

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

Мастером преобразований.

2 Работа с командой Форма

Команда Форма отображает на экране форму, которая представляет собой

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

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

того, с помощью формы вы можете осуществить поиск конкретных записей на

основании сложных критериев.

[pic]

Рис. 28. Форма

Каждому столбцу таблицы в форме соответствует поле, наименование

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

в форме могут присутствовать вычисляемые поля, с помощью которых вы можете

выполнять разнообразные вычисления. Значения этих полей автоматически

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

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

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

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

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

отображаться в форме в режиме редактирования.

Помимо полей форма содержит ряд кнопок и индикатор номера записи.

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

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

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

данных.

3 Поиск в базе данных

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

команды Правка | Найти.

4 Сортировка базы данных

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

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

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

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

образом происходит изменение базы данных. Вы должны иметь возможность

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

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

средствами Excel по восстановлению данных это полностью защитит вашу базу

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

Команда Данные | Сортировка устанавливает порядок строк в таблице в

соответствии с содержимым конкретных столбцов.

Сортировка по возрастанию предполагает следующий порядок:

82. Числа

83. Текст, включая текст с числами (почтовые индексы, номера автомашин)

84. Логические значения

85. Значения ошибок

86. Пустые ячейки

Сортировка по убыванию происходит в обратном порядке. Исключением

являются пустые ячейки, которые всегда располагаются в конце списка.

Самая быстрая и простая сортировка по возрастанию или убыванию

проводится с помощью соответствующих кнопок [pic].

При выборе Данные | Сортировка открывается окно диалога “Сортировка

диапазона”, которое позволяет вам указать поля для сортировки и определить

критерий сортировки. С помощью раскрывающегося списка Сортировать по вы

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

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

При сортировке по возрастанию текстовые данные упорядочиваются в

алфавитном порядке от А к Я. Числовые данные упорядочиваются по возрастанию

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

ранней даты к наиболее поздней. При выборе переключателя по убыванию

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

[pic]

Рис. 29. Окно сортировки

Два дополнительных раздела Затем и В последнюю очередь, по позволяют

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

совпадающие значения.

Переключатель Идентифицировать поля по указывает, имеется ли в

сортируемом списке строка заголовков, которую нужно исключить из

сортировки. При наличии в списке меток столбцов выберите переключатель

Подписям, в противном случае — Обозначениям столбцов листа.

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

открывается окно диалога “Параметры сортировки”. С помощью этого окна вы

можете:

87. Определить пользовательский порядок сортировки для столбца, указанного

в раскрывающемся списке Сортировать по

88. Сделать сортировку чувствительной к использованию прописных и строчных

букв

89. Изменить направление сортировки (вместо сортировки сверху вниз

установить сортировку слева направо)

5 Фильтрация данных в списке

Фильтрация списка позволяет находить и отбирать для обработки часть

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

на экран только те строки, которые содержат определенное значение или

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

скрытыми.

В Excel для фильтрации данных используются команды Автофильтр и

Расширенный фильтр. В случае простых критериев для выборки нужной

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

должны появиться новые элементы: [pic]. C их помощью можно устанавливать

критерии фильтрации данных.

[pic]

Рис. 30. Выбор критерия

[pic]

Рис. 31. Условия критерия

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

Расширенный фильтр.

Обе команды вызываются в результате выбора команды Данные | Фильтр.

6 Подведении итогов в базе данных

Один из способов обработки и анализа базы данных состоит в подведении

различных итогов. С помощью команды Данные | Итоги можно вставить строки

итогов в список, осуществив суммирование данные нужным способом. При

вставке строк итогов Excel автоматически помещает в конец списка данных

строку общих итогов.

После выполнения команды Данные | Итоги вы можете выполнить следующие

операции:

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

по этим группам

91. выбрать функцию для подведения итогов

92. выбрать данные, по которым нужно подвести итоги

93. Кроме подведения итогов по одному столбцу, автоматическое подведение

итогов позволяет:

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

95. выводить многоуровневые, вложенные строки итогов по нескольким

столбцам

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

вычисления для каждой строки

97. скрывать или показывать детальные данные в этом списке

Команда Итоги вставляет в базу данных новые строки, содержащие

специальную функцию.

Синтаксис: ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции; ссылка)

Номер_функции - это число от 1 до 11, которое указывает, какую функцию

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

|Номер функции |Функция |

|1 |СРЗНАЧ |

|2 |СЧЁТ |

|3 |СЧЁТЗ |

|4 |МАКС |

|5 |МИН |

|6 |ПРОИЗВЕД |

|7 |СТАНДОТКЛОН |

|8 |СТАНДОТКЛОНП |

|9 |СУММ |

|10 |ДИСП |

|11 |ДИСПР |

Ссылка - это интервал или ссылка, для которой подводятся итоги.

Если список с промежуточными итогами уже создан, его можно

модифицировать, редактируя формулу с функцией ПРОМЕЖУТОЧНЫЕ. ИТОГИ.

1 Вставка промежуточных итогов

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

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

значение поля Закупочная цена (руб).

Сначала заполняются поля, содержащие исходные данные: “Наименование”,

“Фирма-поставщик”, “цена (руб)” и “количество”. Вычисляемые поля таблицы

заполняются формулами. Для создания формулы в поле “цена ($)” и пользуется

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

валюты. Для создания формул в поле “Сумма реализации” и пользуется

частичный запрет модификации адреса ячеек (с помощью значка $ перед номером

строки), содержащих розничную цену для каждого из товаров.

Промежуточные итоги заполняются через меню Данные | Итоги... |

Промежуточные итоги:

[pic]

Рис. 32. Вывод Промежуточных итогов

Предварительно необходимо выделить область таблицы, в которой Вы

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

приведенном выше примере - это заголовок таблицы ("№ п/п";

"Наименование";..."Валовый доход") и все записи по товарам (запись - это

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

заканчивающаяся цифрой валового дохода).

1. В списке При каждом изменении в: установить значение “Наименование”.

2. В списке Операция установить значение Сумма

3. В списке Добавить итоги по: отметить элементы: "Сумма реализации",

"Сумма закупки", "Количество", "Валовый доход".

4. Взвести флажки: Заменить текущие итоги и Итоги под данными.

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

Таблица 5

| |A|B |C |D |E |F |G |H |I |

| |/|ан. |ик | | | | |. | |

| |п| | | | | | | | |

|6|1|Бана-|Frutis |2 |=D6/ |200 |=D6*F6 |=F6*I$2 |=H6-G6 |

| | |ны | | |kurs | | | | |

|7|2|Бана-|SUMP |2.32 |=D7/ |80 |=D7*F7 |=F7*I$2 |=H7-G7 |

| | |ны | | |kurs | | | | |

|8|3|Бана-|Forum |1.98 |=D8/ |165 |=D8*F8 |=F8*I$2 |=H8-G8 |

| | |ны | | |kurs | | | | |

|9|4|Бана-|Like |1.86 |=D9/ |320 |=D9*F9 |=F9*I$2 |=H9-G9 |

| | |ны | | |kurs | | | | |

|1|5|Бана-|UFO |2.05 |=D10/ |120 |=D10* |=F10*I$2|=H10-G1|

|0| |ны | | |kurs | |F10 | |0 |

| | | | |(1;D6: | |(9;F6: |(9;G6: |(9;H6: |(9;I6: |

| | | | |D10) | |F10) |G10) |H10) |I10) |

|1|6|Вино-|Frutis |4 |=D12/ |90 |=D12*F1|=F12*I$3|=H12-G1|

|2| |град | | |kurs | |2 | |2 |

| | | | | | | | | | |

| | | | | | |(9;F6: |(9;G6: |(9;H6: |(9;I6: |

| | | | | | |F22) |G22) |H22) |I22) |

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

Таблица 6

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

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

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

|п | |ик | | | | | | |

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

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

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

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

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

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

|на| | | | | |753.50руб |566.50руб | |

|ны| | | | | | | | |

| | | | | | | | | |

|24|Анана-с|Like |5.99руб|$ 4.61|60 |359.40руб |408.00руб |48.60руб |

| |ы | | | | | | | |

|25|Анана-с|UFO |6.60руб|$ 5.08|55 |363.00руб |374.00руб |11.00руб |

| |ы | | | | | | | |

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

|ан| | | | | |861.55руб |142.00руб | |

|ас| | | | | | | | |

|ы | | | | | | | | |

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

| | | | | |517|865.02руб |356.90руб | |

2 Создание диаграммы закупочных цен

С помощью Мастера диаграмм легко построить диаграмму анализа

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

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

“исходная таблица”. Так выглядят формулы в ячейках таблицы:

Таблица 7

| |B |C | |D | |E |F |G |H |

| | |таблица'!B| |ная | |таблица'!B1| |ы |ад |

| | |6 | |таблица| |2 | | | |

| | | | |'!B18 | | | | | |

| |ая |таблица'!D| |ная | |таблица'!D1|(C$5:C$|(D$5:D$|(E$5: |

| |таблица'|6 | |таблица| |8 |9) |9) |E$9) |

| |!C12 | | |'!D12 | | | | | |

| |ая |таблица'!D| |ная | |таблица'!D1|(C$5:C$|(D$5:D$|(E$5:E$|

| |таблица'|7 | |таблица| |9 |9) |9) |9) |

| |!C13 | | |'!D13 | | | | | |

| |ая |таблица'!D| |ная | |таблица'!D2|(C$5:C$|(D$5:D$|(E$5:E$|

| |таблица'|8 | |таблица| |0 |9) |9) |9) |

| |!C14 | | |'!D14 | | | | | |

| |ая |таблица'!D| |ная | |таблица'!D2|(C$5:C$|(D$5:D$|(E$5:E$|

| |таблица'|9 | |таблица| |1 |9) |9) |9) |

| |!C15 | | |'!D15 | | | | | |

| |ая |таблица'!D| |ная | |таблица'!D2|(C$5:C$|(D$5:D$|(E$5:E$|

| |таблица'|10 | |таблица| |2 |9) |9) |9) |

| |!C16 | | |'!D16 | | | | | |

|0| |9) | |5:D9) | |) | | | |

|1| |0) | |:D10) | |) | | | |

Таблица 8

| |F |G |H |

|3|средняя стоимость |

А так будет выглядеть результат этих формул:

Таблица 9

| |Закупочная стоимость |средняя стоимость |

|Frutis |2.00руб |4.00руб |6.25руб |2.04руб|4.07руб|6.04руб |

|SUMP |2.32руб |3.65руб |5.32руб |2.04руб|4.07руб|6.04руб |

|Forum |1.98руб |4.05руб |6.03руб |2.04руб|4.07руб|6.04руб |

|Like |1.86руб |4.66руб |5.99руб |2.04руб|4.07руб|6.04руб |

|UFO |2.05руб |3.98руб |6.60руб |2.04руб|4.07руб|6.04руб |

|максим. |2.32руб |4.66руб |6.60руб | | | |

|миним. |1.86руб |3.65руб |5.32руб | | | |

Обратите внимание: в таблице созданы поля средней стоимости, содержащие

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

прямую на графике.

После этого, легко построить диаграмму анализа закупочных цен:

[pic]

Рис. 33. Итоговая диаграмма

Линии средних цен нужно отформатировать иначе, чем это делает мастер.

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

и сделать пунктирными.

Некоторые специальные возможности

В Excel существуют некоторые интересные возможности позволяющие

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

из таких возможностей.

1 Автоформат

Команда Формат | Автоформат... позволяет быстро оформить готовую

таблицу воспользовавшись готовым Списком форматов.

[pic]

Рис. 34. Автоформат

2 Решения уравнений

Одной из интереснейших особенностей Excel является возможность

нахождения корней уравнений типа f(x)=a. Для этого необходимо в какой-либо

из ячеек ввести предполагаемое (начальное) значение корня уравнения, в

другой ячейке записать само уравнение f(x), выполнить команду Сервис |

Поиск решения.... В окне Поиск решения, в поле Установить целевую ячейку

записать адрес ячейки где записано уравнение f(x), в поле Равной: |

Значению ввести значение a, в поле Изменяя ячейки ввести адрес ячейки где

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

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

(начальное) значение корня уравнения, появится вычисленное значение корня

уравнения с точностью заданной в разделе Параметры....

Если уравнение имеет не один, а множество корней, то остальные корни

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

Замечание: Начальное значение необходимо подбирать с определенной

осторожностью, если функция, в которой ищутся корни, имеет

различные особенности, так как при поиске решения начальное

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

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

расходящейся.

[pic]

Рис. 35. Решение уравнения в Excel

3 Создание сценариев

Часто встречается необходимость одним и тем же ячейкам присвоить

разные значения, причем так, чтобы не потерять предыдущие значения. В Excel

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

через меню Сервис | Сценарии.... Откроется окно в котором будет список всех

имеющихся сценариев (вначале этот список пуст). Нажмите кнопку

"Добавить...", появится окно.

[pic]

Рис. 36. Окно создания сценария

В этом окне надо:

1. Дать имя сценарию;

2. Указать те ячейки, которые будут изменяться данным сценарием.

3. Написать комментарии к данному сценарию в поле Примечание

4. Нажать кнопку ОК

[pic]

Рис. 37. Диспетчер сценариев

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

указанных ячеек. Описанную операцию нужно повторить несколько раз для

создания нескольких.

Для того, чтобы заполнить ячейки значениями из конкретного сценария,

нужно выбрать его в списке и нажать кнопку Вывести.

4 Отчет по сценариям.

После того, как сценарии созданы, можно создать отчет по этим

сценариям. Для этого используется кнопка Отчет...

В окне Отчет по сценарию укажите: Тип отчета | Структура, а в поле

Ячейки результата - укажите адреса ячеек (разделенные знаком "точка с

запятой") в которых содержатся итоги (например: I13;I19;I25;I31;I38).

После этого будет автоматически создана новая страница с отчетом по

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

5 Создание макроса

Макрос - это запись определенной последовательности действий в Excel.

Такая запись выполняется на языке Visual Basic (или на языке

макрокоманд для ранних версий) на специальных листах рабочей

книги "Модуль#", которые являются редакторами этого языка.

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

зрения программирования – это подпрограмма.

Sub сценарии()

' сценарии Макрос

' Макрос записан 15.08.99 (bm) Создание отчета по сценариям

Sheets("Структура сценария").Select

ActiveWindow.SelectedSheets.Delete

Range("A1").Select

ActiveSheet.Scenarios.CreateSummary ReportType:=xlStandardSummary,

ResultCells:=Range("I13,I19,I25,I31,I38")

End Sub

Создавать макросы можно «вручную», или автоматически - заставить

Excel отслеживать все действия и записывать их в соответствующий модуль.

Для этого нужно:

1. Для начала перейдите на ту ячейку, с которой начнется запись.

2. Войти в меню Сервис | Макрос | Начать запись...

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

комментарий: для чего этот макрос, кнопка ОК С этого момента все

действия записываются в виде инструкций Visual Basic. На экране

появится дополнительная панель инструментов с кнопкой [pic] Остановить

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

закрытия окна!

4. Выполнить последовательность действий, определяющих макрос.

5. Нажать "Остановить запись". Запись действий в модуль прекратиться и

дополнительная панель исчезнет с экрана.

Выполнить макрос можно через меню Макрос | Макросы... - выбрать в

списке имя макроса - кнопкой Выполнить. Макросу также можно назначить

горячую клавишу, или кнопку на панели инструментов (это делается через

диалоговое окно "Настройка", вызов: меню Вид | Панели инструментов |

Настройка...).

6 Создание активной кнопки

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

элементы (элементы аналогичные тем, которые есть на диалоговых окнах) и

"заставить" эти элементы работать. Добавление новых управляющих элементов

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

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

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

инструмент "кнопка". Нажать этот инструмент и указать место и размер

будущей кнопки на листе рабочей книги. Дальнейшее выполнение зависит от

версии Excel:

1 Excel 95

Появится диалоговое окно "Назначить макрос" в котором нужно указать

имя созданного ранее макроса. Затем, щелкнув на самой кнопке, можно

изменить ее название.

2 Excel 97

Из контекстного меню на новой кнопке выбрать команду Свойства. В поле

Caption заменить стандартное название кнопки на необходимое.

Дважды щелкнуть на кнопке. Откроется окно редактора Visual Basic на

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

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

Private Sub CommandButton1_Click()

Call сценарии

End Sub

"Сценарии" - это то имя макроса, например приведенного выше.

Вернуться в Excel и на панели инструментов Элементы управления отжать

кнопку Конструктор - [pic]. Теперь можно закрыть всю эту панель. Кнопка

готова.

Некоторые особенности (для углубленного изучения)

1 Полезные советы при работе с Excel 95 и Excel 97

1 Создание Web-страницы

Чтобы создать HTML-страницу при помощи Microsoft Excel, нужно:

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

страницу.

1. Дать команду меню Файл | Сохранить в формате HTML.

1. Выполнить указания мастера Web-страниц.

Если в меню Файл отсутствует команда Сохранить в формате HTML, нужно

установить мастер Web-страниц. Для этого дайте команду меню Сервис |

Надстройки, отметьте “Мастер Web-страниц” и щелкните ОК.

Если в списке настроек отсутствует мастер Web-страниц, добавьте

средства создания Web страниц Office 97 при помощи программы установки

Microsoft Office 97.

2 Использование Web-запросов

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

Web-запросы, чтобы загружать информацию из Web прямо в Excel 97. Дайте

команду Данные | Внешние данные | Выполнить Web-запрос. В диалоговом окне

Выполнить запрос выберите список имеющихся файлов Web-запросов. Выберите

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

помощи запроса Get More Web Queries или на

http://www.microsoft.com/officefreestuff/excel.

3 Сохранение файла Microsoft Excel 97 сразу в двух форматах

При помощи Excel 97 можно сохранять рабочие книги в формате, который

подходит для Excel 95, и при этом позволяет пользователям Excel 97

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

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

1. Дайте команду меню Файл | Сохранить как.

1. В раскрывающемся списке Тип файла: окна Сохранить как... выберите тип

“Книга Microsoft Excel 97 и 5.0/95”.

1. Щелкните кнопку Сохранить.

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

формате, установить этот тип файлов по умолчанию:

1. Дайте команду меню Сервис | Параметры.

1. Выберите вкладку Переход.

1. В раскрывающемся списке Сохранять файлы Excel как выберите тип “Книга

Microsoft Excel 97 и 5.0/95”.

1. Щелкните ОК.

4 Связь надписи с ячейкой электронной таблицы

Чтобы создать такую связь:

1. На панели инструментов Рисование щелкните кнопку Надпись.

1. Нарисуйте в таблице прямоугольный фрагмент для будущей надписи.

1. Нажмите F2, чтобы поместить курсор в строку формулы.

1. Введите знак равенства (=).

1. Щелкните ячейку, содержащую данные или текст, которые вы хотите

поместить в текстовый блок.

1. Нажмите Enter.

5 Блокировка доступа к личным ячейкам электронной таблицы

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

таблицы Microsoft Excel 97, выполните одну из нижеописанных процедур — в

зависимости от того, необходимо ли заблокировать большую или меньшую часть

ячеек.

Чтобы запретить возможность редактирования большинства ячеек:

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

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

щелкать по ячейкам.

2. Дать команду меню Формат | Ячейки и открыть вкладку Защита.

3. Снить флажок Защищаемая ячейка и щелкнуть ОК.

4. Дать команду меню Сервис | Защита и выбрать Защитить лист. Ввести,

если нужно, пароль и щелкнуть ОК.

Чтобы защитить лишь некоторые ячейки:

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

верхнем углу таблицы или клавиши Ctrl+Shift+пробел.

2. Дать команду меню Формат | Ячейки и выбрать вкладку Защита. Снять

флажок Защищаемая ячейка и щелкнуть ОК.

3. Выделить ячейки, которые нужно заблокировать. Для выделения ячеек

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

4. Дать команду меню Формат | Ячейки и открыть вкладку Защита. Установть

флажок Защищаемая ячейка и щелкнуть ОК.

5. Дать команду меню Сервис | Защита и выбрать Защитить лист. Ввести,

если нужно, пароль и щелкнуть ОК.

6 "Повышение" точности вычислений формул

В следствии того что Excel отображает цифры в ячейках с меньшей

точностью чем их хранит (например 12.343 может отображаться как 12.34),

возможны визуальные эффекты "неточности" расчетов Excel в формулах.

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

этом становится "очевидным" для пользователя.

7 Перевод строки в ячейке таблицы

Для размещения текста на нескольких строках в одной ячейке таблицы

используйте Alt+Enter.

8 Локальное имя (использование одного и того же имени на разных листах)

Если Вы хотите использовать одно и тоже имя на разных листах таблицы

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

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

книги располагаются листы с одинаковыми формулами (например листы «отчет о

прибылях за июнь», «отчет о прибылях за июль» и т.п.). При этом формулы с

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

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

9 Отображение чисел с лидирующими нулями

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

необходимым количеством нулей. Это делается через меню Формат | Ячейки |

Пользовательский.

Замечание - можно ввести число как текст (например '001), но это не одно

и тоже.

10 Использование специальных символов в формате

Некоторые символы в строке формата являются специальными. Для того

чтобы их вывести можно использовать символ обратный слэш '\'.

11 Удалении ячеек рабочего листа без «порчи» ссылок на удаленные ячейки

Если Вы удаляете данные в листе (строки, столбцы), то все формулы

содержащие ссылки на удаленные данные будут скорректированы Excel как

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

только «очистить», «скопировать» и «вставить».

СпрАвочный материал

1 Функциональные и «горячие» клавиши

Некоторые действия в Microsoft Excel можно выполнить значительно

быстрее, если использовать функциональные клавиши и клавиатурные комбинации

(«горячие» клавиши), ниже приведены их списки.

1 Функциональные клавиши

|F11 |Создать | | | | |

2 «Горячие» клавиши

|Операция |Сочетание клавиш |

|Клавиши для правки содержимого ячеек или строки | |

|формул | |

|Ввести набранные данные в ячейку |ENTER |

|Удалить набранные данные |ESC |

|Повторить последнее действие |F4 или Ctrl+Y |

|Начать новый абзац в текущей ячейке |ALT+ENTER |

|Вставить в ячейку символ табуляции |CTRL+ALT+TAB |

|Удалить выделенные символы или символ слева от |BACKSPACE |

|курсора | |

|Удалить выделенные символы или символ справа от |DELETE |

|курсора | |

|Удалить символы справа от курсора до конца строки |CTRL+DELETE |

|Переместить курсор на один символ вверх, вниз, влево |Клавиши со стрелками|

|или вправо | |

|Переместить курсор в начало строки |HOME |

|Перейти к правке примечания ячейки |SHIFT+F2 |

|Создать имена по тексту ячеек |CTRL+SHIFT+F3 |

|Заполнить вниз |CTRL+В |

|Заполнить вправо |CTRL+К |

|Заполнить выделенные ячейки набранным значением |CTRL+ENTER |

|Ввести данные в ячейку и перейти к ячейке, |ENTER |

|расположенной снизу | |

|Ввести данные в ячейку и перейти к ячейке, |SHIFT+ENTER |

|расположенной сверху | |

|Ввести данные в ячейку и перейти к ячейке, |TAB |

|расположенной справа | |

|Ввести данные в ячейку и перейти к ячейке, |SHIFT+TAB |

|расположенной слева | |

|Начать формулу |= |

|Перейти в режим правки содержимого ячейки |F2 |

|Очистить строку формул после указания ячейки или |BACKSPACE |

|удалить в строке формул символ слева от курсора | |

|Вставить имя в формулу |F3 |

|Присвоить имя |CTRL+F3 |

|Пересчитать все листы во всех открытых книгах |F9 или CTRL+= |

|Пересчитать текущий лист |SHIFT+F9 |

|Выполнить автосуммирование |ALT+ = |

|Ввести текущую дату |CTRL+; |

|Ввести текущее время |CTRL+SHIFT+: |

|Отменить результаты правки ячейки или строки формул |ESC |

|Завершить правку ячейки |ENTER |

|Начать новый абзац |ALT+ENTER |

|Вставить символ табуляции |CTRL+ALT+TAB |

|Скопировать содержимое верхней ячейки в текущую |CTRL+SHIFT+" |

|ячейку или в строку формул | |

|Переключить режимы отображения значения ячейки и |CTRL+`(знак левой |

|формулы ячейки |кавычки) |

|Скопировать формулу верхней ячейки в текущую ячейку |CTRL+' (апостроф) |

|или в строку формул | |

|Ввести набранную формулу в качестве формулы массива |CTRL+SHIFT+ENTER |

|Перейти к шагу 2 мастера функций, после набора в |CTRL+Ф |

|формуле имени функции | |

|Вставить в круглых скобках список аргументов, после |CTRL+SHIFT+Ф |

|набора в формуле имени функции | |

|Отобразить список автоввода |ALT+СТРЕЛКА ВНИЗ |

|Клавиши для форматирования данных | |

|Выполнить команду Стиль (меню Формат) |ALT+' (апостроф) |

|Выполнить команду Ячейки (меню Формат) |CTRL+1 |

|Выполнить форматирование общим числовым форматом |CTRL+SHIFT+~ |

|Выполнить форматирование денежным форматом с двумя |CTRL+SHIFT+$ |

|десятичными знаками после точки (отрицательные числа | |

|отображаются в круглых скобках) | |

|Выполнить форматирование процентным форматом с |CTRL+SHIFT+% |

|отсутствующей дробной частью | |

|Выполнить форматирование научным форматом с двумя |CTRL+SHIFT+^ |

|десятичными знаками после запятой | |

|Выполнить форматирование форматом для дат с полями |CTRL+SHIFT+# |

|дня, месяца и года | |

|Выполнить форматирование форматом для времени с |CTRL+SHIFT+@ |

|полями часов и минут и индексами A.M. или P.M. | |

|Выполнить форматирование форматом с двумя десятичными|CTRL+SHIFT+! |

|знаками после запятой | |

|Вставить рамку структуры |CTRL+SHIFT+& |

|Удалить все границы |CTRL+SHIFT+_ |

|Выполнить или удалить форматирование жирным шрифтом |CTRL+И |

|Выполнить или отменить форматирование курсивом |CTRL+Ш |

|Подчеркнуть текст или удалить линию подчеркивания |CTRL+Г |

|Перечеркнуть текст или удалить линию перечеркивания |CTRL+5 |

|Скрыть строки |CTRL+9 |

|Показать строки |CTRL+SHIFT+( |

|Скрыть столбцы |CTRL+0 (ноль) |

|Показать столбцы |CTRL+SHIFT+) |

2 Некоторые функции в EXCEL

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

определенный интерес и не совсем понятны с первого взгляда. Более полный

список функций см. в Приложении к данному пособию «Функции Microsoft

Excel».

1 Математические функции

1 Тригонометрические функции и функции работающие с углами.

COS() и SIN() - соответствуют своему написанию.

ACOS() и ASIN() - соответственно arccos и arcsin

TAN(), ATAN() - tg и arctg

ATAN2() - arctg для заданных координат x и y.

ГРАДУСЫ() - преобразует радианы в градусы.

РАДИАНЫ() - преобразует градусы в радианы.

ПИ() - (

2 Логарифмы и степенные функции

LN(), LOG10() и LOG() - натуральный, десятичный и логарифм с произвольным

основанием.

EXP() и СТЕПЕНЬ() - в комментариях не нуждаются

COSH(), SINH() и TANH() - гиперболические косинус, синус и тангенс

ACOSH(), ASINH() и ATANH() - гиперболические арккосинус, арксинус и

арктангенс

3 Функции округления

ОКРВЕРХ(), ЧЕТН(), ОКРВНИЗ(), ЦЕЛОЕ(), ОКРУГЛТ(), НЕЧЁТ(), ОТБР(),

ОКРУГЛ(), ОКРУГЛВНИЗ(), ОКРУГЛВВЕРХ(),- функции округления с различными

возможностями.

4 Операции с матрицами

Для операциий над матрицами в Excel применяют следующие средства:

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

1. Встроенные функции: ТРАНСП, МУМНОЖ, МОПРЕД, МОБР

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

которая является ссылкой в указанных выше операциях. Формулы,

обрабатывающие массив отличаются от обычных следующим:

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

2. завершается ввод таких формул - нажатием Shift+Ctrl+Enter;

3. формула заключается в фигурные скобки и повторяется во всех клетках

области

5 Функции СУММ() и ЕСЛИ() могут оперировать с массивами

Использование массивов в функциях СУММ() и ЕСЛИ() позволяет

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

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

формулой найти сумму всех чисел в заданном диапазоне дат. Еще один пример.

Кстати также можно использовать функции типа БДСУМ(), но это не всегда

удобно.

2 Как к дате добавлять месяцы

В Excel можно добавлять к дате дни. Если необходимо добавлять

определенное количество месяцев (например получить последовательность 5

янв, 5 фев, 5 мар и т.п.) учитывая число дней в месяце, надо использовать

функцию ДАТА(). Excel сам будет отслеживать число дней в месяце.

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

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

группа BHV, 1997. - 480 с.

1. Руководство пользователя. Microsoft Windows 95. - Neunkirchen: Buhl-Data-

Service Microsoft Corporation, 1995.

1. Рассел Борланд. Эффективная работа с Microsoft Word 97. - СПб: Питер,

1998. - 960 с.

1. Материалам эхоконференции RU.EXCEL (за июль-сентябрь 1997 года)

Collected by Kirienko Andrew, 2:5020/239.21@fidonet

1. Англо-русский словарь по программированию и информатике (с

толкованиями). - М.: Рус. яз., 1990. - 335 с.

1. http://www.microsoft.ru/main.html

1. http://www.microsoft.com/officefreestuff/excel

1. http://nsvrv.ziet.zhitomir.ua/bmu/Sppp/index.html

1. http://members.aol.com/gjlinker/projects/oraxcel

1. http://www.infoart.ru:8000/it/book/russ_red/excel5/content.htm

1. http://www.infoart.ru:8000/it/book/russ_red/excel5/intro.htm

-----------------------

[1] Обычно для бухгалтерии и применялись табличные редакторы.

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

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

ячеек.

[3] В данном пособие отождествляется понятие список и база данных, для

более простого понимания, на начальных этапах.

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

Рефераты

Использование Excel