Работе в программе в Microsoft Access
Создание вычисляемого поля
Вычисляемые поля предназначены для отображения в отчетах значений выражений на основе исходных данных.
Вычисляемые поля в отчетах можно использовать для получения итоговых значений или проведения специальных вычислений, например, чтобы отобразить налог с оборота на стоимость, нужно умножить стоимость на величину налога с оборота, т.е. на 18%.
Рассмотрим создание вычисляемых полей на примере отчета «Kursused«, созданного ранее.
Чтобы добавить к отчету вычисляемое поле «Кaibemaks / Налог с оборота «:
= [Maksumus]* 0,18.
5. Откройте окно свойств поля, дважды щелкнув по нему, и на вкладке Format (Макет) в поле со списком свойства Format (Формат поля) выберите значение Currency (Денежный).
6. Нажмите кнопку Вид (View) на панели инструментов и выберите Report View (Просмотр отчёта), чтобы оценить результаты своей работы. Отчет будет выглядеть так, как показано на рисунке ниже. К прежним данным добавился крайний правый столбец со значениями налога с оборота для каждого курса, у которого имеется стоимость.
Замечание
Если при переходе в режим Просмотра отчета появляется диалоговое окно Enter Parameter Value (Введите значение параметра), это означает, что в выражениях неправильно заданы названия одного или нескольких полей. Нажмите кнопку Отмена (Cancel) и проверьте свойства, установленные на шагах 4 и 5.
Работа с вычисляемыми полями
Для вычисляемого поля можно определить заголовок, ограничение доступности, выражение представления, выражения упорядочивания, тип значения, доступные значения и оформление также как для полей наборов данных. Кроме того, вычисляемое поле может быть определено как ресурс, для этого необходимо добавить данное поле в список ресурсов и указать выражение, по которому будет вычисляться итог.
Пример
В приведенном запросе получаются данные из регистра продаж и регистра планов продаж. Результатом запроса будет таблица, где для каждого подразделения и каждой номенклатуры будет получено плановое и фактическое количество продаж.
Определим вычисляемое поле. Для этого перейдем в конструкторе схемы компоновки данных на вкладку Вычисляемые поля и добавим поле.
Результат отчета будет выглядеть так:
| Номенклатура | Подразделение | Стоимость план | Стоимость факт | Перевыполнение плана |
| Вентилятор настольный | Отдел оптовой продажи | 238,32 | 737,55 | 209,48 |
| Мужские сапоги с искусственным мехом | Отдел оптовой продажи | 234,24 | -100,00 | |
| Мишка (конфеты) | Отдел розничной продажи | 114,91 | Не запланировано |
Т.е. для каждой строчки было рассчитано значение вычисляемого поля.
Теперь в итог отчета будет выводиться сумма по полям, в том числе и по полю ПеревыполнениеПлана :
| Номенклатура | Подразделение | Стоимость план | Стоимость факт | Перевыполнение плана |
| Вентилятор настольный | Отдел оптовой продажи | 238,32 | 737,55 | 209,48 |
| Мужские сапоги с искусственным мехом | Отдел оптовой продажи | 234,24 | -100,00 | |
| Мишка (конфеты) | Отдел розничной продажи | 114,91 | Не запланировано | |
| Итого | 472,56 | 852,46 | 109,48 | |
Для того чтобы получить правильный итог по выполнению плана изменим формулу расчета итога по полю Выполнение плана :
В этой формуле уже получается не сумма отношений факта к плану, а отношения суммы факта к сумме плана. Кроме того, в итоге учитываются только те записи, для которых был установлен план.
Введем данную формулу в выражение ресурса:
Теперь результат отчета будет выглядеть так:
Создание вычисляемых элементов управления
Для отображения результатов вычислений в формах и отчетах в базах данных Access можно использовать вычисляемые элементы управления. Например, если в отчете отображается количество проданных товаров и цена каждого товара, можно добавить вычисляемую текстовое поле, в которое перемножатся эти два поля, чтобы отобразить общую цену. Свойство «Данные» вычисляемого текстового поля содержит выражение, в которое умножаются два поля (количество элементов, умноженное на цену за единицу), чтобы получить результат.
В этой статье
Создание вычисляемых элементов управления
Эта процедура позволяет создать вычисляемую дес.в., не используя мастер.
В области навигации щелкните форму или отчет правой кнопкой мыши и выберите «Конструктор».
На вкладке «Конструктор» в группе «Элементы управления» выберите нужный тип элементов управления.
Список типов элементов управления, которые можно использовать в качестве вычисляемого, см. в разделе «Узнайте, какие типы элементов управления можно использовать в качестве вычисляемого.»
Наведите указатель мыши на форму или отчет и щелкните форму или отчет, чтобы вставить его.
Если запускается мастер управления, нажмите кнопку «Отмена», чтобы закрыть его.
Выберите его, нажмите F4, чтобы отобразить окно свойств, а затем введите выражение в поле свойства «Control Source». Чтобы использовать Построитель выражений для создания выражения, щелкните 
рядом с полем свойства «Control Source».
Переключиться в представление формы или отчета и убедитесь, что вычисляемая система управления работает так, как вы ожидаете.
Перед каждым выражением должен быть оператор =. Например: =[UnitPrice]*.75.
Дополнительные сведения о создании выражений см. в статье Создание выражений.
Если вам нужно больше места для ввести выражение в поле свойства «Control Source», нажмите shift+F2, чтобы открыть окно «Масштаб».
Если форма или отчет основаны на запросе, вы можете поместить выражение в запрос, а не в вычисляемый элемент управления. Это повышает производительность и, если вы собираетесь вычислять итоги для групп записей, проще использовать имя записи вычисляемое поле в агрегатная функция.
При сортировке вычисляемого объекта управления в форме или отчете убедитесь, что свойство Format (Формат) этого объекта задано соответствующим образом. В противном случае вычисляемая числовая или дата может отсортироваться по алфавиту, а не по числу.
Изменение связанного управления на вычисляемой
Чтобы создать вычисляемую рамку, можно сначала создать связанный (например, перетащив поле из области «Список полей» в форму или отчет), а затем отредактировать его свойство «Источник», чтобы создать выражение. Это работает хорошо, если имя этого управления не конфликтует ни с одним из имен полей, содержащихся в выражении. Дополнительные сведения о создании связанного управления путем перетаскиния поля из области «Список полей» см. в статье «Добавление поля в форму или отчет».
Если связанный контроль уже создан, воспользуйтесь процедурой ниже, чтобы сделать его вычисляемой и избежать конфликтов имен.
В области навигации щелкните форму или отчет правой кнопкой мыши и выберите «Конструктор».
Щелкните поле, который вы хотите изменить, и нажмите F4, чтобы открыть лист свойств.
Если свойство «Имя» на вкладке «Все» в окне свойств соответствует свойству «Источник управления», отредактируем его так, чтобы оба свойства не совпадали. Убедитесь, что имя не является зарезервированным словом или именем другого поля базы данных. Дополнительные сведения о зарезервированных словах см. в статье о зарезервированных словах и символах Access.
Отредактируем строку в поле свойства «Данные», чтобы она содержала нужное выражение.
Чтобы сохранить изменения, нажмите сочетание клавиш CTRL+S.
Переключение в представление макета или формы и убедитесь, что вычисляемая система управления работает так, как вы ожидаете.
Если в средстве управления не отображаются нужные данные (например, если Access отображает #Name? в этом средстве), проверьте источник записей формы или отчета, чтобы убедиться, что все поля, используемые в выражении, доступны. Если источником записей является запрос, для работы выражения может потребоваться добавить в запрос одно или несколько полей.
Перед каждым выражением должен быть оператор =. Например: =[UnitPrice]*.75.
Если вам нужно больше места для ввести выражение в поле свойства «Control Source», нажмите shift+F2, чтобы открыть окно «Масштаб».
Если форма или отчет основаны на запросе, вы можете поместить выражение в запрос, а не в вычисляемый элемент управления. Это повышает производительность и, если вы собираетесь вычислять итоги для групп записей, проще использовать имя записи вычисляемое поле в агрегатная функция.
При сортировке вычисляемого объекта управления в форме или отчете убедитесь, что свойство Format (Формат) этого объекта задано соответствующим образом. В противном случае вычисляемая числовая или дата может отсортироваться по алфавиту, а не по числу.
Узнайте, какие типы элементов управления можно использовать в качестве вычисляемого
Текстовые поля являются наиболее популярным вариантом для вычисляемого управления, так как в них может отображаться большое количество различных типов данных. Тем не менее любой объект управления, который имеет свойство «Исходный объект», можно использовать в качестве вычисляемой. Во многих случаях не имеет смысла использовать определенный тип управления в качестве вычисляемого, так как его нельзя обновить так, как вы можете обновить связанный или связанный. Например, если вы поместите в форму поле «поле с квадратиконом» и введите выражение в его свойство «Источник управления», то его больше нельзя будет выбрать или очистить, щелкнув его. На основе результатов выражения этот поле будет выбрано или очищено. Если вы щелкнули этот поле, в панели состояния Access отобразилось оповещение «Не может быть изменено» —оно привязано к >. Однако в отчете может быть полезно использовать элемент управления «квадрат» на основе результатов вычисления, так как элементы управления в отчетах используются только для отображения данных.
Вычисляемое поле в Сводных таблицах в MS Excel
Как создать сводную таблицу.
Многие думают, что создание отчетов при помощи сводных таблиц для «чайников» является сложным и трудоемким процессом. Но это не так! Microsoft много лет совершенствовала эту технологию, и в современных версиях Эксель они очень удобны и невероятно быстры.
Фактически, вы можете сделать это всего за пару минут. Для вас – небольшой самоучитель в виде пошаговой инструкции:
1. Организуйте свои исходные данные
Перед созданием сводного отчета организуйте свои данные в строки и столбцы, а затем преобразуйте диапазон данных в таблицу. Для этого выделите все используемые ячейки, перейдите на вкладку меню «Главная» и нажмите «Форматировать как таблицу».
Использование «умной» таблицы в качестве исходных данных дает вам очень хорошее преимущество – ваш диапазон данных становится «динамическим». Это означает, что он будет автоматически расширяться или уменьшаться при добавлении или удалении записей. Поэтому вам не придется беспокоиться о том, что в свод не попала самая свежая информация.
Полезные советы:
2. Создаем и размещаем макет
Откроется окно «Создание ….. ». Убедитесь, что в поле Диапазон указан правильный источник данных. Затем выберите местоположение для свода:
Нажатие ОК создает пустой макет без цифр в целевом местоположении, который будет выглядеть примерно так:
Полезные советы:
Область, в которой вы работаете с полями макета, называется списком полей. Он расположен в правой части рабочего листа и разделен на заголовок и основной раздел:
Изменения, которые вы вносите в этих разделах, немедленно применяются в вашей таблице.
3. Как добавить поле
Чтобы иметь возможность добавить поле в нужную область, установите флажок рядом с его именем.
По умолчанию Microsoft Excel добавляет поля в раздел «Макет» следующим образом:
4. Как удалить поле из сводной таблицы?
Чтобы удалить любое поле, вы можете выполнить следующее:
И еще один простой и наглядный способ удаления поля. Перейдите в макет таблицы, зацепите мышкой ненужный вам элемент и перетащите его за пределы макета. Как только вы вытащите его за рамки, рядом со значком появится хатактерный крестик. Отпускайте кнопку мыши и наблюдайте, как внешний вид вашей таблицы сразу же изменится.
5. Как упорядочить поля?
Вы можете изменить расположение показателей тремя способами:
Все внесенные вами изменения применяются немедленно.
Ну а ежели спохватились, что сделали что-то не так, не забывайте, что есть «волшебная» комбинация клавиш CTRL+Z, которая отменяет сделанные вами изменения (если вы не сохранили их, нажав соответствующую клавишу).
6. Выберите функцию для значений (необязательно)
По умолчанию Microsoft Excel использует функцию «Сумма» для числовых показателей, которые вы помещаете в область «Значения». Когда вы помещаете нечисловые (текст, дата или логическое значение) или пустые значения в эту область, к ним применяется функция «Количество».
Но, конечно, вы можете выбрать другой метод расчёта. Щелкните правой кнопкой мыши поле значения, которое вы хотите изменить, выберите Параметры поля значений и затем – нужную функцию.
Думаю, названия операций говорят сами за себя, и дополнительные пояснения здесь не нужны. В крайнем случае, попробуйте различные варианты сами.
Здесь же вы можете изменить имя его на более приятное и понятное для вас. Ведь оно отображается в таблице, и поэтому должно выглядеть соответственно.
В Excel 2010 и ниже опция «Суммировать значения по» также доступна на ленте – на вкладке «Параметры» в группе «Расчеты».
7. Используем различные вычисления в полях значения (необязательно)
Еще одна полезная функция позволяет представлять значения различными способами, например, отображать итоговые значения в процентах или значениях ранга от наименьшего к наибольшему и наоборот.
Это называется «Дополнительные вычисления». Доступ к ним можно получить, открыв вкладку «Параметры …», как это описано чуть выше.
Подсказка. Функция «Дополнительные вычисления» может оказаться особенно полезной, когда вы добавляете одно и то же поле более одного раза и показываете, как в нашем примере, общий объем продаж и объем продаж в процентах от общего количества одновременно. Согласитесь, обычными формулами делать такую таблицу придется долго. А тут – пара минут работы!
Итак, процесс создания завершен. Теперь пришло время немного поэкспериментировать, чтобы выбрать макет, наиболее подходящий для вашего набора данных.
Советы
Предупреждения
Работа со сводными таблицами в Excel
Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.
Заменим выручку на прибыль.
Товары и области меняются местами также перетягиванием мыши.
Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.
На все про все ушло несколько секунд. Вот, как работать со сводными таблицами. Конечно, не все задачи столь тривиальные. Бывают и такие, что необходимо использовать более замысловатый способ агрегации, добавлять вычисляемые поля, условное форматирование и т.д. Но об этом в другой раз.
Источник данных сводной таблицы Excel
Для успешной работы со сводными таблицами исходные данные должны отвечать ряду требований. Обязательным условием является наличие названий над каждым полем (столбцом), по которым эти поля будут идентифицироваться. Теперь полезные советы.
1. Лучший формат для данных – это Таблица Excel. Она хороша тем, что у каждого поля есть наименование и при добавлении новых строк они автоматически включаются в сводную таблицу.
2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты по месяцам в отдельных столбцах.
3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.
4. Применяйте правильное форматирование к полям. Числа должны быть в числовом формате, даты должны быть датой. Иначе возникнут проблемы при группировке и математической обработке. Но здесь эксель вам поможет, т.к. сам неплохо определяет формат данных.
В целом требований немного, но их следует знать.
Вычисляемое поле. Алгоритм расчета
Для каждого месяца у нас есть только одно значение фактических продаж (столбец Продажи) и плана. Вычисляемое поле ПроцентВыполнения возвращает значение равное их отношению. Например, для января 2012 года – это 50,19% (продано было 36992,22, а план был 73697,76). 36992,22/73697,76=0,5019 (см. строку 10 на листе Исходная таблица).
Теперь проверим итоги по месяцам. За январь итоговым значением является 93,00%. Как это значение получилось?
Сначала программа вычислила СУММУ продаж за январь по всем годам, затем, вычислила СУММУ всех плановых значений. Разделив одно на другое, было получено 93,00%. В этом можно убедиться проделав вычисления самостоятельно (см. строку 10 на листе Сводная таблица, столбцы H:J).
В этом состоит одно из ограничений Вычисляемого поля – итоговые значения вычисляются только на основании суммирования.
Аналогично расчет ведется и для итогов по столбцам: находится сумма продаж и плана по годам, затем вычисляется их отношение.
Если бы для каждого месяца в исходной таблице было бы несколько сумм продаж и плановых значений, то расчет был бы аналогичен подсчету итоговых значений.
Чтобы обойти данное ограничение и вычислить, например, средний % выполнения плана для всех январских месяцев, придется отказаться от Вычисляемого поля. Создайте в исходной таблице новый столбец – отношение продажи к плану для каждого месяца (см. лист Исходная таблица2). Затем, создайте на ее основе другую сводную таблицу. В окне параметров полей значений установите Среднее.
В итоговом столбце теперь будет отображаться средний процент выполнения плана.
Изменяем и удаляем Вычисляемое поле
Вызовите тоже диалоговое окно, которое мы использовали для создания Вычисляемого поля. В выпадающем списке выберите нужное поле. Появится его формула, которую можно отредактировать, также как и название этого Вычисляемого поля.
Там же можно удалить это поле.
Подготовка исходной таблицы
Начнем с требований к исходной таблице.
Вместо того, чтобы плодить повторяющиеся столбцы ( регион 1, регион 2, … ), в которых будут в изобилии незаполненные ячейки, переосмыслите структуру таблицы, как показано на рисунке выше (Все значения объемов продаж должны быть в одном столбце, а не размазаны по нескольким столбцам. Для того, чтобы это реализовать, возможно, потребуется вести более подробные записи (см. рисунок выше), а не указывать для каждого региона суммарные продажи).
Создание таблицы в формате EXCEL 2007 добавляет новые возможности:
В качестве исходной будем использовать таблицу в формате EXCEL 2007 содержащую информацию о продажах партий продуктов. В строках таблицы приведены данные о поставке партии продукта и его сбыте.
В таблице имеются столбцы:
Через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен ) откорректируем Имя таблицы на « Исходная_таблица ».
Создание Сводной таблицы
Сводную таблицу будем создавать для решения следующей задачи: «Подсчитать суммарные объемы продаж по каждому Товару».
В появившемся окне нажмем ОК, согласившись с тем, что Сводная таблица будет размещена на отдельном листе.
На отдельном листе появится заготовка Сводной таблицы и Список полей, размещенный справа от листа (отображается только когда активная ячейка находится в диапазоне ячеек Сводной таблицы).
Структура Сводной таблицы в общем виде может быть представлена так:
Т.к. ячейки столбца Товар имеют текстовый формат, то они автоматически попадут в область Названия строк Списка полей. Разумеется, поле Товар можно при необходимости переместить в другую область Списка полей. Заметьте, что названия Товаров будут автоматически отсортированы от А до Я (об изменении порядка сортировки читайте ниже ).
Теперь поставим галочку в Списке полей у поля Продажи.
Т.к. ячейки столбца Продажи имеют числовой формат, то они автоматически попадут в раздел Списка полей Значения.
Несколькими кликами мыши (точнее шестью) мы создали отчет о Продажах по каждому Товару. Того же результата можно было достичь с использованием формул (см. статью Отбор уникальных значений с суммированием по соседнему столбцу ). Если требуется, например, определить объемы продаж по каждому Поставщику, то для этого снимем галочку в Списке полей у поля Товар и поставим галочку у поля Поставщик.
Детализация данных Сводной таблицы
Обновление Сводной таблицы
Удаление Сводной таблицы
Изменение функции итогов
Изменение порядка сортировки
Теперь предположим, что Товар Баранки – наиболее важный товар, поэтому его нужно выводить в первой строке. Для этого выделите ячейку со значением Баранки и установите курсор на границу ячейки (курсор должен принять вид креста со стрелками).
Затем, нажав левую клавишу мыши, перетащите ячейку на самую верхнюю позицию в списке прямо под заголовок столбца.
После того как будет отпущена клавиша мыши, значение Баранки будет перемещено на самую верхнюю позицию в списке.
Изменение формата числовых значений
Теперь добавим разделитель групп разрядов у числовых значений (поле Продажи). Для этого выделите любое значение в поле Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт меню Числовой формат …
Добавление новых полей
Предположим, что необходимо подготовить отчет о продажах Товаров, но с разбивкой по Регионам продажи. Для этого добавим поле Регион продажи, поставив соответствующую галочку в Списке полей. Поле Регион продажи будет добавлено в область Названия строк Списка полей (к полю Товар). Поменяв в области Названия строк Списка полей порядок следования полей Товар и Регион продажи, получим следующий результат.
