Создание информационной системы средствами МС Excel и VBA База данных Сборка изделий (стр. 2 из 3)
Например, чтобы поощрить сборщиков 3 и 4 разряда, количество деталей которых больше 1114 и меньше или равно 1633, нужно отфильтровать данные по полю данных «Итого» и «Разряд». Для этого выбираем команду Данные > Фильтр > Автофильтр. По команде Автофильтр в ячейках, содержащих заголовки полей появляются раскрывающиеся кнопки. Щелчок на такой кнопке в поле базы данных «Итого» открывает доступ к списку. Выбираем из списка команду «Условие», в появившемся диалоговом окне «Пользовательский автофильтр» выбираем условие больше 1114 и меньше или равно 1633. Останутся только данные по тем сборщикам которые собрали больше 1114 и меньше или равно 1633 изделий (Рис.3).
Рис.3. Фильтрация данных
Расширенный фильтр позволяет:
· сразу копировать отфильтрованные записи в другое место рабочего места рабочего листа;
· сохранять критерий отбора для дальнейшего использования;
· показывать в отфильтрованных записях не все столбцы, а только указанные (Рис.4).
Рис.4. Расширенная фильтрация данных
4. Скрытие столбцов
Если, для дальнейшей распечатки нам нужны только «Ф.И.О.» и «Итого», то остальные столбцы можно скрыть. Для этого курсором выделяем те столбцы, которые необходимо временно скрыть (дни неделли), выбираем команду Формат > Столбец > Скрыть. Останутся только те данные, которые необходимы (Рис.5).
Рис. 5. Скрытие столбцов
Для того чтобы вернуть скрытые столбцы, выделяем всю таблицу или те столбцы между которыми были скрыты столбцы, затем выбираем команду Формат > Столбец > Отобразить. То же можно производить со сточками, для этого нужно использовать команду Формат > Столбец > Скрыть / Отобразить.
5. Промежуточные итоги
Возвращает промежуточный итог в список или базу данных. Обычно проще создать список с промежуточными итогами, используя команду Промежуточный итог в группе Структура на вкладке Данные. Но если список с промежуточными итогами уже создан, его можно модифицировать, отредактировав формулу с функцией ПРОМЕЖУТОЧНЫЕ ИТОГИ. (Рис.6)
Рис. 6. Подведение промежуточных итогов
Замечания:
Если уже имеются формулы подведения итогов внутри аргументов «ссылка1;ссылка2;...» (вложенные итоги), то эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.
Для диапазона констант «номер_функции» от 1 до 11 функция ПРОМЕЖУТОЧНЫЕ ИТОГИ включает значения строк, скрытых при помощи команды Скрыть строки (меню Формат подменю Скрыть/Показать) в группе Ячейки на вкладке Лист. Эти константы используются для получения промежуточных итогов для скрытых и не скрытых чисел списка. Для диапазона констант «номер_функции» от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ ИТОГИ исключает значения строк, скрытых при помощи команды Скрыть строки. Эти константы используются для получения промежуточных итогов только для не скрытых чисел списка.
Функция ПРОМЕЖУТОЧНЫЕ ИТОГИ исключает все строки, не включенные в результат фильтра, независимо от используемого значения константы «номер_функции».
Функция ПРОМЕЖУТОЧНЫЕ ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы «номер_функции» от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ ИТОГИ(109;B2:G2)), скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.
Если среди ссылок есть трехмерные ссылки, функция ПРОМЕЖУТОЧНЫЕ ИТОГИ возвращает значение ошибки #ЗНАЧ!.
6. Функции Базы Данных
Функции базы данных имеют обобщенное название - Дфункция (база_данных;поле;критерий):
База_данных — диапазон ячеек, составляющих список или базу данных. База данных представляет собой список связанных данных, в котором строки данных являются записями, а столбцы — полями. Верхняя строка списка содержит заголовки всех столбцов.
Поле — столбец, используемый функцией. Вводится заголовок столбца в кавычках, например "Возраст" или "Разряд", или число (без кавычек), задающее положение столбца в списке: 1 — для первого столбца, 2 — для второго столбца и т. д.
Критерий — диапазон ячеек, который содержит задаваемые условия. В качестве аргумента «условия» можно использовать любой диапазон, который содержит по крайней мере один заголовок столбца и по крайней мере одну ячейку с условием, расположенную под заголовком столбца; (Рис.7):
Рис.7. Функции для анализа
Существуют следующие функции (Таб.1):
Таб.1. Функции для работы с базами данных
| Функция | Описание |
| ДСРЗНАЧ | Возвращает среднее значение выбранных записей базы данных. |
| БСЧЁТ | Подсчитывает количество числовых ячеек в базе данных. |
| БСЧЁТА | Подсчитывает количество непустых ячеек в базе данных. |
| БИЗВЛЕЧЬ | Извлекает из базы данных одну запись, удовлетворяющую заданному условию. |
| ДМАКС | Возвращает максимальное значение среди выделенных записей базы данных. |
| ДМИН | Возвращает минимальное значение среди выделенных записей базы данных. |
| БДПРОИЗВЕД | Перемножает значения определенного поля в записях базы данных, удовлетворяющих условию. |
| ДСТАНДОТКЛ | Оценивает стандартное отклонение по выборке для выделенных записей базы данных. |
| ДСТАНДОТКЛП | Вычисляет стандартное отклонение по генеральной совокупности для выделенных записей базы данных |
| БДСУММ | Суммирует числа в поле для записей базы данных, удовлетворяющих условию. |
| БДДИСП | Оценивает дисперсию по выборке из выделенных записей базы данных |
| БДДИСПП | >Вычисляет дисперсию по генеральной совокупности для выделенных записей базы данных |
7. Функция ВПР()
Функция ВПР() ищет значение в первом столбце массива таблицы и возвращает значение в той же строке из другого столбца массива таблицы.
Буква В в названии функции ВПР означает «вертикально». Функция ВПР используется вместо функции ГПР, если сравниваемые значения расположены в столбце слева от искомых данных.
Синтаксис:
ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)
Искомое_значение — значение, которое должно быть найдено в первом столбце табличногомассива (Массив. Объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.). Этот аргумент может быть значением или ссылкой. Если искомое_значение меньше, чем наименьшее значение в первом столбце табличного массива, функция ВПР возвращает значение ошибки #Н/Д.
Таблица — два или более столбцов данных. Можно использовать ссылку на диапазон или имя диапазона. Значения в первом столбце аргумента «таблица» — это значения, в которых выполняется поиск аргумента «искомое_значение». Эти значения могут быть текстовыми, числовыми или логическими. Текстовые значения в нижнем и верхнем регистре считаются эквивалентными.
Номер_столбца — номер столбца в аргументе «таблица», из которого возвращается соответствующее значение. Если номер_столбца = 1, то возвращается значение из первого столбца таблицы; если номер_столбца = 2 — значение из второго столбца таблицы и т. д. Если значение аргумента «номер_столбца» (Рис.8):
Рис.8. Функция ВПР()
Замечания:
- при поиске текстовых значений в первом столбце аргумента «таблица» убедитесь, что данные в этом столбце не содержат начальных пробелов, конечных пробелов, используемых не по правилам прямых ('или ") и фигурных (‘или“) кавычек или непечатаемых знаков. В этих случаях функция ВПР может возвратить неправильное или непредвиденное значение. Дополнительные сведения см. в описании функции ПЕЧСИМВ и СЖПРОБЕЛЫ;
- при поиске числовых значений или значений дат убедитесь, что формат данных в первом столбце таблицы не является текстовым. В этом случае функция ВПР может возвратить неправильное или непредвиденное значение. Дополнительные сведения см. в разделе Преобразование чисел из текстового формата в числовой;