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

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

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

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

можете:

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 |

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

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

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

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

|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| | |средн: |=ПРОМЕЖ|Итого:|=ПРОМЕЖ|=ПРОМЕЖ|=ПРОМЕЖУ|=ПРОМЕЖ|

|1| | | |УТОЧНЫE| |УТОЧНЫЕ|УТОЧНЫЕ|ТОЧНЫЕ.И|УТОЧНЫЕ|

| | | | |.ИТОГИ | |.ИТОГИ |.ИТОГИ |ТОГИ |.ИТОГИ |

| | | | |(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 |

| | | | | | | | | | |

|2| |Итог:| | |Итог: |=ПРОМЕЖ|=ПРОМЕЖ|=ПРОМЕЖУ|=ПРОМЕЖ|

|5| | | | | |УТОЧНЫЕ|УТОЧНЫЕ|ТОЧНЫЕ.И|УТОЧНЫЕ|

| | | | | | |.ИТОГИ |.ИТОГИ |ТОГИ |.ИТОГИ |

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

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

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

Таблица 6

| |Сегодня курс $= |1.3 | | | |Розничная цена |

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

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

| | | | | | | |ананас |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 |

|4| |='исходная| |='исход| |='исходная |Бана-ны|Анана-с|Вино-гр|

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

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

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

|5|='исходн|='исходная| |='исход| |='исходная |=СРЗНАЧ|=СРЗНАЧ|=СРЗНАЧ|

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

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

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

|6|='исходн|='исходная| |='исход| |='исходная |=СРЗНАЧ|=СРЗНАЧ|=СРЗНАЧ|

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

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

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

|7|='исходн|='исходная| |='исход| |=исходная |=СРЗНАЧ|=СРЗНАЧ|=СРЗНАЧ|

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

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

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

|8|='исходн|='исходная| |='исход| |='исходная |=СРЗНАЧ|=СРЗНАЧ|=СРЗНАЧ|

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

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

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

|9|='исходн|='исходная| |='исход| |='исходная |=СРЗНАЧ|=СРЗНАЧ|=СРЗНАЧ|

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

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

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

|1|максим. |=МАКС(C5:C| |=МАКС(D| |=МАКС(E5:E9| | | |

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

|1|миним. |=МИН(C5:C1| |=МИН(D5| |=МИН(E5:E10| | | |

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

Таблица 8

| |F |G |H |

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

|4|бананы |ананасы |виноград |

|5|=СРЗНАЧ(C$5:C$9) |=СРЗНАЧ(D$5:D$9) |=СРЗНАЧ(E$5:E$9) |

|6|=СРЗНАЧ(C$5:C$9) |=СРЗНАЧ(D$5:D$9) |=СРЗНАЧ(E$5:E$9) |

|7|=СРЗНАЧ(C$5:C$9) |=СРЗНАЧ(D$5:D$9) |=СРЗНАЧ(E$5:E$9) |

|8|=СРЗНАЧ(C$5:C$9) |=СРЗНАЧ(D$5:D$9) |=СРЗНАЧ(E$5:E$9) |

|9|=СРЗНАЧ(C$5:C$9) |=СРЗНАЧ(D$5:D$9) |=СРЗНАЧ(E$5:E$9) |

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

Таблица 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 (или на языке

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

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

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

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