Microsoft Excel

Microsoft Excel

Электронные таблицы Microsoft Excel предназначены для создания и редактирования табличных (бухгалтерских) документов, создания диаграмм, простых баз данных, анализа данных.

Рабочее окно Excel

Создание таблицы

В клетки на рабочем листе Excel можно вводить:

• Текст;
• Формулы.

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

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

Пример таблицы

Автоматизация при создании таблиц. Автозаполнение

Автозаполнение применяется если в строке или столбце содержатся:

• Одни и те же числа или текст;
• Числовой ряд (арифметическая или геометрическая прогрессия);
• Стандартные списки (например, названия дней недели).

Автозаполнение строки одинаковыми числами

Шаг 1. Ввод числа в

первую клетку.

Шаг 2. Буксировка курсора автозаполнения вдоль строки.

Автозаполнение. Арифметическая прогрессия

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

Автозаполнение. Геометрическая прогрессия

• Заполнить первую клетку;
• Пометить ряд клеток в строке или столбце, в которых должна быть прогрессия;
• [Правка] - [Заполнить] - [Прогрессия…];
• Выбрать тип прогрессии «геометрическая», задать шаг, конечное значение и щелкнуть левой кнопкой мыши на кнопке «OK» всплывающей панели «Прогрессия».

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

Позволяет автоматически вычислять строку (столбец) с итоговыми суммами.

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

• Пометить строку для сумм

2. Выбрать пиктограмму Автосумма

Форматирование таблицы

Форматирование таблицы включает:

• Форматирование ячеек;
• Форматирование строк;
• Форматирование таблицы в целом

Форматирование ячеек

Для форматирования выбрать Format/Font

Форматирование ячеек включает задание:

• Формата числа;
• Выравнивание текста в ячейке;
• Задание параметров шрифта;
• Задание границ ячейки;
• Заливку ячеек (цвет);
• Введение защиты ячейки;

Параметры форматирования ячеек таблицы

100). Для этого:" width="640"

Условное форматирование ячеек таблицы

Можно задать особые параметры формата ячеек (например, другой цвет ячейки) в таблице при выполнении какого-либо условия (например, 100). Для этого:

• Пометить ячейки таблицы;
• [Формат]–[Условное форматирование…];
• Задать условия и вид форматирования.

Примечания к ячейкам

К любой ячейке таблицы можно создать примечание (комментарий). Для этого:

• Пометить ячейку.
• [Вставка] – [Примечание].
• Ввести текст примечания.

Форматирование строк/столбцов

Форматирование строк(столбцов) включает:

• Настройку высоты строк (ширины столбцов);
• Автоподбор высоты строк (ширины столбцов).

Автоформат таблицы

При форматировании таблицы в целом выбирается один из стандартных видов внешнего оформления таблицы. Для этого:

• Пометить форматируемую область таблицы;
• [Формат] - [Автоформат] - [].

Автоформат таблицы. Пример.

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

• Пометить строку с подписями для оси Х и строки с данными

TVS:

При такой структуре таблицы

TVS:

TVS:

TVS:

TVS:

TVS:

TVS:

TVS:

TVS:

хорошо показывать приемы

TVS:

При такой структуре таблицы

При такой структуре таблицы (такой тип таблицы называют анкетой) можно производить подбор параметра (функция Goal Seek)

Таблица этого вида хорошо подходит для решения задачи оптимизации (функция Solver).

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

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

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

В этом столбце фиксируется наименование фирмы- покупателя товара

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

Таблица данного вида хорошо подходит для выполнения прогнозирования.

Автозаполнения,

хорошо выполнять сортировку и фильтрацию данных

Автосуммирования,

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

TVS:

Таблица такого вида

может использоваться как "плоская" база данных

TVS:

Ячейки В10:F10 могут быть изменяемыми при некоторых ограничениях, например:

F10

B10:E10=0

TVS:

Таблица отражает данные за 5 лет, с 1990 по 1994 год.

Используя данные в этой таблице и функцию GROWS можно прогнозировать структуру экспорта на ближайшие годы

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

TVS:

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

TVS:

Ячейки В10:F10 могут быть изменяемыми при некоторых ограничениях, например:

F10

B10:E10=0

TVS:

Цель оптимизации может заключаться в получении в этой ячейке максимального значения.

TVS:

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

???

???

00:00:00

00:00:00

???

СПбГТУ Конфиденциально

00/00/0000

???

СПбГТУ Конфиденциально

00/00/0000

Страница 1

Страница 1

• [Insert] – [Chart];
• Задать атрибуты диаграммы (подписи осей, название диаграммы, легенду и т. д.) в ходе диалога.

Базы данных

Можно использовать таблицу как базу данных, так как в Excel предусмотрены средства сортировки и поиска данных в таблице по критериям, задаваемым пользователем, имеются средства интерфейса пользователя (формы) для работы с данными в таблицах.

Базы данных. Сортировка записей

• Пометить таблицу базы данных;
• [Фильтр] – [Автофильтр];
• Открыть меню в столбце;
• Выбрать пункт «Сортировка по возрастанию (убыванию)»

Базы данных. Поиск записей

• Выбрать пункт подменю «Условие»
• На всплывающей панели «Пользовательский автофильтр» устанавливаем условия отбора записей

Анализ данных

Количество строк и столбцов таблицы и количество поставщиков или потребителей может быть очень велико (несколько сотен). Это затрудняет оценку итогов деятельности непосредственно по данным, приведенным в таблице.

Анализ данных

В результате анализа часто требуется получить в сжатом виде данные по потребителям (фирмам):

• Сколько каждой фирмой закуплено товаров за месяц по видам продукции;
• Какая сумма фирмой уже выплачена;
• Сколько фирма еще должна выплатить.

Анализ данных. Исходная таблица данных.

Цена

Остаток

Оплата

Дата

Всего

Сумма

Кол. 1.0

Сумма

Цена

Кол.0.5

Наим.организ.

Дата

70

1400

15.янв

1470

0

ВИЛАРД

1470

2,45

50

01.янв

2304

2304

0

КАРАТ

2304

2,4

80

01.янв

549,2

1000

05.янв

1549,2

79,2

4,4

20

1470

2,45

50

КОНДАС

01.янв

600

0

600

2,5

20

ЛИГА

01.янв

600

4,3

3000

07.янв

3158,7

38,7

158,7

10

3120

2,6

100

ЛОРА

02.янв

1380

1380

0

1380

2,3

50

СОЛО

02.янв

1500

1500

0

50

2,5

ТИГ

02.янв

1500

1500

1500

0

2,5

50

АЛЬЯНС

03.янв

1500

5760

0

200

5760

2,4

АННА-МАРИЯ

03.янв

5760

Результат анализа

Создание сводной таблицы

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

Пример сводной таблицы

Дата

09.янв

08.янв

10.янв

Общий итог

06.янв

07.янв

Наим.орг.

05.янв

Данные

01.янв

02.янв

03.янв

04.янв

125

АЛЬЯНС

Сумма по полю Всего

125

Сумма по полю Оплата

125

Сумма по полю Остаток

125

1092.5

1092.5

АННА-МАРИЯ

Сумма по полю Всего

Сумма по полю Оплата

1092.5

1092.5

Сумма по полю Остаток

820

850

492.5

390

567

230

499

Итог Сумма по полю Всего

575

122.5

543

2165

125

Итог Сумма по полю Оплата

120

200

820

850

492.5

230

390

567

499

122.5

2045

343

450

Итог Сумма по полю Остаток

Решение задач. Подбор параметра.

Excel включает средства, позволяющие автоматически подбирать значения параметров, при которых достигается заданное значение какого-либо показателя, связанного функционально с этим параметром (например, достигать заданную прибыль при подборе значения накладных расходов)

Пример таблицы

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

Решение задач. Подбор параметра.

Решение задач. Подбор параметра

Пометить целевую ячейку;

• [Сервис] – [Подбор параметра...];
• В появившейся диалоговой панели «Подбор параметра» внести:

- в окно «Установить в ячейке:»- адрес целевой ячейки;

- в окно «Значение:» - желаемое значение в целевой ячейке;

- в окно «Изменяя значение ячейки:» - адрес изменяемой ячейки;

Решение задачи оптимизации

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

Решение задачи оптимизации

Для решения задачи оптимизации нужно...

1. Пометить целевую ячейку;

2. [Сервис] - [Поиск решения...].

В результате появится диалоговая панель «Поиск решения»

Вид диалогового окна «Поиск решения»

В окнах диалоговой панели нужно задать...

- в окно «Установить целевую ячейку:» ввести адрес целевой ячейки:

- активизировать один из элементов (максимальному значению, минимальному значению) в группе «Равной:» или задать фиксированное значение, достигаемое в целевой ячейке в результате оптимизации в окно «значению»;

- указать диапазон изменяемых ячеек в окне «Изменяя ячейки:»;

- ввести ограничения в окно «Ограничения». Вызов панели ввода ограничений - щелчком левой кнопки мыши на кнопке «Добавить».

Для получения автоотчета по результатам оптимизации нужно...

Во всплывающей после активизации кнопки Solver панели Solver Results нужно выбрать:

[Answer] – [OK]

Создание и использование макросов

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

[Сервис] – [Макрос] – [Безопасность...] – [Низкая].

Создание макроса с помощью макрорекордера

• [Сервис] - [Макрос] - [Начать запись];
• Вввести имя макроса в окно ввода “Имя макроса” панели “Запись макроса” и нажать кнопку [OK];
• Выполнить действия с таблицей или диаграммой;
• Остановить запись макроса щелчком левой кнопки мыши на кнопке “OK” всплывающей панели «Остановить запись макроса».

Запуск макроса

• [Сервис] - [Макрос] – [Макросы...] ;
• Выбрать из меню нужный макрос;
• [Выполнить].

Создание средств запуска макросов

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

• кнопки, которые могут быть добавлены в имеющиеся пиктографические меню Excel;
• кнопки управления “Кнопка” из панели инструментов “Формы”;
• графические объекты и объекты WordArt.

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

• [Вид] - [Панели инструментов] - [Настройка...];
• [Команды] – [Макросы];
• Отбуксировать кнопку “Кнопка” на имеющуюся панель инструментов, например, на панель “Стандартная”;
• [Закрыть].

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

• [Вид] – [Панели инструментов] – [Формы];
• Выбрать объект “Кнопка” и установить его в нужном месте листа рабочей книги Excel и нужного размера;
• Связать кнопку “Кнопка” с макросом для чего вызвать контекстное меню правой кнопкой мыши и выбрать пункт “Назначить макрос”, затем указать требуемый макрос в списке.

Для создания средства запуска макроса в форме графического объекта или объекта WordArt нужно...

• Создать такой объект;
• Вызвать контекстное меню правой кнопкой мыши и выбрать пункт “Назначить макрос”, затем указать требуемый макрос в списке.