Создание информационной системы средствами МС Excel и VBA (База данных «Сборка изделий»)

       Например, чтобы поощрить сборщиков 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. Функция ВПР()

       Замечания:

       - при поиске текстовых значений в первом столбце аргумента «таблица» убедитесь, что данные в этом столбце не содержат начальных  пробелов, конечных пробелов, используемых не по правилам прямых ('или ") и фигурных (‘или“) кавычек или непечатаемых знаков. В этих случаях функция ВПР может возвратить неправильное или непредвиденное значение. Дополнительные сведения см. в описании функции ПЕЧСИМВ и СЖПРОБЕЛЫ;

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

       - если значение аргумента «интервальный_просмотр» — ЛОЖЬ, а аргумент «искомое_значение» представляет собой текст, то в аргументе «искомое_значение» допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому знаку; звездочка — любой последовательности знаков. Чтобы найти сами эти знаки, следует поставить перед ними знак тильды (~).

8. Сводная таблица

 

       Сводные таблицы позволяют объединить данные базы данных и представить в различных вариантах. Например, нам нужно узнать количество сборщиков по каждому разряду. Для этого выбираем команду Данные > Сводная таблица. Затем с помощью «Мастера сводных таблиц и диаграмм» указываем диапазон содержащий исходные данные и выбираем место расположения сводной таблицы. Из «Списка полей сводной таблицы», выбираем «Разряд», по количеству также берем «Разряд», затем поочередно курсором перетаскиваем их в сводную таблицу (Рис.9). 

 

 

       Рис.9. Сводная таблица 
 
 
 

 

9. Запись макросов

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

       - макросы, работающие только в  данной рабочей книге, при этом  текст макроса хранится в этом  же файле; 

       - макросы, которые работают во  всех открытых рабочих книгах, текст таких макросов хранится в специальном файле с именем Personal.xls.

       Порядок создания макросов:

       1. Выберите в главном меню программы  команду Сервис – Макрос –  Начать запись. На экране появится  окно для определения параметров  данного макроса.

       2. Введите в соответствующие поля имя макроса, назначьте макросу комбинацию клавиш для быстрого запуска (буква должна быть латинской), в поле описания можно кратко указать назначение данного макроса. Определите место сохранения макроса – данный файл или “Личная книга макросов” (файл Personal.xls).

       3. Далее выполняйте последовательность  действий, которые вы хотите записать  в макрос. По окончании работы  нажмите кнопку конца записи  на панели инструментов макроса  или выберите команду Сервис  – Макрос – Остановить запись.