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