Смекни!
smekni.com

Использование Excel (стр. 10 из 14)

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

· Числа

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

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

· Значения ошибок

· Пустые ячейки

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

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

.

При выборе Данные | Сортировка открывается окно диалога “Сортировка диапазона”, которое позволяет вам указать поля для сортировки и определить критерий сортировки. С помощью раскрывающегося списка Сортировать по вы можете выбрать столбец для сортировки. Порядок сортировки устанавливается переключателями по возрастанию или по убыванию.

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

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

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

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

Окно диалога содержит кнопку Параметры, в результате нажатия которой открывается окно диалога “Параметры сортировки”. С помощью этого окна вы можете:

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

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

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

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

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

В Excel для фильтрации данных используются команды Автофильтр и Расширенный фильтр. В случае простых критериев для выборки нужной информации достаточно команды Автофильтр в ячейках выделенных заголовков должны появиться новые элементы:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Номер функции

Функция

1

СРЗНАЧ

2

СЧЁТ

3

СЧЁТЗ

4

МАКС

5

МИН

6

ПРОИЗВЕД

7

СТАНДОТКЛОН

8

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

9

СУММ

10

ДИСП

11

ДИСПР

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

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

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

Для каждого товара можно ввести в таблицу промежуточные итоги - суммы по полям: Количество, Сумма закупки, Сумма реализации, Прибыль и среднее значение поля Закупочная цена (руб).

Сначала заполняются поля, содержащие исходные данные: “Наименование”, “Фирма-поставщик”, “цена (руб)” и “количество”. Вычисляемые поля таблицы заполняются формулами. Для создания формулы в поле “цена ($)” и пользуется имя, присвоенное ячейке, содержащей числовое значение текущего курса валюты. Для создания формул в поле “Сумма реализации” и пользуется частичный запрет модификации адреса ячеек (с помощью значка $ перед номером строки), содержащих розничную цену для каждого из товаров.

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

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

Предварительно необходимо выделить область таблицы, в которой Вы собираетесь подводить итоги, включив в нее строку с названиями полей. В приведенном выше примере ‑ это заголовок таблицы ("№ п/п"; "Наименование";..."Валовый доход") и все записи по товарам (запись ‑ это одна строка в таблице, начинающаяся с порядкового номера записи и заканчивающаяся цифрой валового дохода).

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

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

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

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

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

Таблица 5

A

B

C

D

E

F

G

H

I

5 № п/п Наи-мено-ван. Фирма поставщик цена' (руб) цена' ($) Кол-во Сумма закупки Сумма реализац. Валовый доход
6 1 Бана-ны Frutis 2 =D6/
kurs
200 =D6*F6 =F6*I$2 =H6-G6
7 2 Бана-ны SUMP 2.32 =D7/
kurs
80 =D7*F7 =F7*I$2 =H7-G7
8 3 Бана-ны Forum 1.98 =D8/
kurs
165 =D8*F8 =F8*I$2 =H8-G8
9 4 Бана-ны Like 1.86 =D9/
kurs
320 =D9*F9 =F9*I$2 =H9-G9
10 5 Бана-ны UFO 2.05 =D10/
kurs
120 =D10*
F10
=F10*I$2 =H10-G10
11 средн: =ПРОМЕЖУТОЧНЫE.ИТОГИ (1;D6:
D10)
Итого: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ (9;F6:
F10)
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ (9;G6:
G10)
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ (9;H6:
H10)
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ (9;I6:
I10)
12 6 Вино-град Frutis 4 =D12/
kurs
90 =D12*F12 =F12*I$3 =H12-G12
25 Итог: Итог: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ (9;F6:
F22)
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ (9;G6:
G22)
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ (9;H6:
H22)
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ (9;I6:
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 753.50руб 2 566.50руб 813.00руб
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 861.55руб 2 142.00руб 280.45руб
Итого: Итог: 1 517 4 865.02руб 6 356.90руб 1 491.88руб

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

С помощью Мастера диаграмм легко построить диаграмму анализа закупочных цен. Дополнительная таблица создается на другом листе, поэтому в ней присутствуют ссылки на первый лист который в данном примере имеет имя: “исходная таблица”. Так выглядят формулы в ячейках таблицы: