Как сделать сводную в access?

Хитрости » 19 Январь 2015       Дмитрий       22424 просмотров

Предположим, что у вас есть большая база данных. Назовем её «products». И под большая я подразумеваю порядка 3млн записей(строк) или больше. Непосредственно на один лист Excel такое количество данных точно не поместится. Можно, конечно, хранить и на разных листах.

Тогда можно воспользоваться статьей Сводная таблица из нескольких листов. Но во-первых, данный метод работает не очень стабильно и может требовать изменений в зависимости от версии Excel и так же требует разрешения выполнения макросов, а во-вторых, для такого количества записей это не лучшее решение, т.к.

хранить такое количество данных в книгах Excel не совсем правильно.

Создание БД в Access из нескольких диапазонов
Для того, чтобы правильно и безболезненно собрать данные нескольких таблиц из Excel в Access необходимо эти таблицы подготовить. Что не так уж сложно. Для этого надо соблюсти следующие правила:

• все таблицы должны содержать одинаковое количество столбцов с полностью идентичными заголовками
• заголовки не должны содержать переносов строк, тире, дефисов, точек, запятых. Лучше вообще отказаться от любых знаков препинания и сомнительных символов — оставьте только пробелы между словами(и то даже их лучше заменить нижним подчеркиванием)
• если в таблицах присутствуют числовые данные, которые впоследствии необходимо будет суммировать — убедитесь, что все данные именно числовые и нет текстовых. Это поможет избежать ошибок импорта
• необходимо убедиться, что таблицы не содержат пустых строк и столбцов, а так же объединенных ячеек

Теперь, когда все таблицы готовы можно приступить к импорту данных в Access. Открываете Access и выбираете Создать (New) — Новая база данных (Blank database).

Указываете имя базы и месторасположение(папку):

После создания базы проходим несколько шагов:

1. переходим на вкладку Внешние данные (External Data)-группа Импорт и связи (Import & Link)—Excel.
2. Выбираем файл, данные из которого необходимо перенести в Access
3. Указываем Импортировать данные источника в новую таблицу в текущей базе данных (Import the source data into a new table in the current database):
   
4. на следующем шаге будет предложено выбрать лист или именованный диапазон для импорта (Show worksheets, Show named ranges):
   
   я выбрал лист «products», т.к. именно так у меня называется лист с данными. Жмем Далее (Next)
5. на этом шаге просто убеждаемся, что галка Первая строка содержит заголовки столбцов (First Row Contains Column Heading) установлена. Если нет — устанавливаем
6. жмем Готово (Finish)

Первая часть базы наполнена. Теперь необходимо дополнить созданную в Access таблицу данными других листов или книг.

Для этого повторяем все описанные выше шаги, но на 2-м шаге выбираем Добавить копию записей в конец таблицы (Append a copy of the record to the table).

Тогда данные будут дополнены в уже созданную нами таблицу из первого листа, а не будут записаны в новую(чего нам не надо).
Все, теперь можно приступать к созданию сводной таблицы.

Создание сводной на основе базы данных Access
Прежде чем создавать сводную на основе внешних источников необходимо добавить на панель быстрого доступа кнопку, которая позволит это сделать:

• Excel 2010— Файл (File)— Параметры (Options)— Панель быстрого доступа (Quick Access Toolbar)
• Excel 2007— Кнопка офис— Параметры Excel (Excel options)— Панель быстрого доступа (Quick Access Toolbar)

или непосредственно с панели быстрого доступа:

Выбрать команды из: Все команды (All Commands). Ищем там Мастер сводных таблиц и диаграмм (PivotTable and PivotChart Wizard) и переносим на панель быстрого доступа:

Теперь жмем на эту кнопку и на первом шаге появившегося окна Мастера выбираем во внешнем источнике данных (external data source):

на втором шаге жмем кнопку Получить данные (Get Data):

В появившемся окне необходимо выбрать MS Access Database или База данных MS Access.

Тут есть важный момент. Галочка Использовать мастер запросов (Use the Query Wizard to create/edit queries) должна быть включена. Жмем ОК.

Далее выбираем в правом окне папку, в которой расположена наша база данных. В левом окне выбираем сам файл Базы данных:

Подтверждаем выбор нажатием кнопки ОК.
Далее необходимо создать запрос выборки. По сути можно просто нажать на имя таблицы Базы данных и после этого на значек «>»

Но если вам необходимо будет работать только с некоторыми столбцами из всей таблицы — можно последовательно перенести их в правое поле(предварительно в левом развернув плюсик рядом с именем таблицы). Убрать лишние столбцы из правого поля можно кнопочкой «

Источник: https://www.excel-vba.ru/chto-umeet-excel/svodnaya-iz-bazy-dannyx-access/

Управление данными с помощью Access или Excel. Сводная из базы данных Access

Современная цивилизация остро нуждается в постоянной обработке и сохранении больших объемов различных данных. Также постоянно возникает необходимость в отслеживании влияния их изменений на другие информационные массивы. Удобнее всего организовать все эти процессы в виде базы данных. Удобным инструментом для такой организации может стать популярная программа СУБД Access.

Что такое СУБД

Различные данные принято представлять и хранить в виде таблиц. Часто бывает так, что в нескольких таблицах находится информация, которая связана между собой каким-то образом. Совокупность таких взаимозависимых таблиц представляет собой базу данных.

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

Для автоматизации процесса в некоторых случаях приходится дополнительно применять макросы. Совокупность и дополнительных инструментов образует систему, называемую СУБД — система управления базой данных.

Примером могут быть Foxpro, Oracle, СУБД Microsoft Access.

• Основные функции СУБД
  
• СУБД Access выполняет следующие важные функции:
• Осуществление необходимых и формулами;
• Налаживание связей между таблицами и массивами данных;
• Возможность добавления запросов.

Весь набор инструментальных средств, который имеется в арсенале СУБД предназначается для полноценного обслуживания двух основных направлений в ходе функционирования информационной системы. Это организация проектирования и модификации структуры и различные необходимые манипуляции с данными.

Основные отличия СУБД Access

От многих других которые используются программистами для автоматизации работы с базами данных, СУБД Access отличается простотой в освоении. Поскольку данная платформа русифицирована, а также имеет стандартный для всех программ MS Office интерфейс, даже неопытные пользователи не ощутят никаких неудобств во время ее освоения.

В то же время Access является полноценной программой управления массивами данных со всеми необходимыми атрибутами. Созданную систему можно использовать не только на отдельном компьютере, но и в разветвленной локальной сети.

Возможности Access

Эта программа дает возможность создавать исходные объекты в виде таблиц с полями разнообразных типов, например, числовыми, текстовыми, графическими, даже полями OLE.

Более того, обеспечивается связь между таблицами, при этом поддерживается каскадное обновление данных и каскадное удаление при необходимости.

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

1. При заполнении полей могут использоваться вычисления с помощью различных формул.
2. СУБД Access — удобная и понятная программа, которая обладает всеми возможностями подобных профессиональных приложений.
3. Баги и глюки Excel (3)

Работа с VB проектом (12) Условное форматирование (5) Списки и диапазоны (5) Макросы(VBA процедуры) (63) Разное (39)

Предположим, что у вас есть большая база данных. Назовем её «products». И под большая я подразумеваю порядка 3млн записей(строк) или больше. Непосредственно на один лист Excel такое количество данных точно не поместится. Можно, конечно, хранить и на разных листах.

Тогда можно воспользоваться статьей Сводная таблица из нескольких листов
. Но во-первых, данный метод работает не очень стабильно и может требовать изменений в зависимости от версии Excel и так же требует разрешения выполнения макросов, а во-вторых, для такого количества записей это не лучшее решение, т.к.

хранить такое количество данных в книгах Excel не совсем правильно.

Создание БД в Access из нескольких диапазонов
Для того, чтобы правильно и безболезненно собрать данные нескольких таблиц из Excel в Access необходимо эти таблицы подготовить. Что не так уж сложно. Для этого надо соблюсти следующие правила:

• все таблицы должны содержать одинаковое количество столбцов с полностью идентичными заголовками
• заголовки не должны содержать переносов строк, тире, дефисов, точек, запятых. Лучше вообще отказаться от любых знаков препинания и сомнительных символов — оставьте только пробелы между словами(и то даже их лучше заменить нижним подчеркиванием)
• если в таблицах присутствуют числовые данные, которые впоследствии необходимо будет суммировать — убедитесь, что все данные именно числовые и нет текстовых. Это поможет избежать ошибок импорта
• необходимо убедиться, что таблицы не содержат пустых строк и столбцов, а так же объединенных ячеек

Теперь, когда все таблицы готовы можно приступить к импорту данных в Access. Открываете Access и выбираете Создать (New)
— Новая база данных (Blank database)
. Указываете имя базы и месторасположение(папку):

После создания базы проходим несколько шагов:

Первая часть базы наполнена. Теперь необходимо дополнить созданную в Access таблицу данными других листов или книг.

Для этого повторяем все описанные выше шаги, но на 2-м шаге выбираем Добавить копию записей в конец таблицы (Append a copy of the record to the table)
.

Тогда данные будут дополнены в уже созданную нами таблицу из первого листа, а не будут записаны в новую(чего нам не надо).
Все, теперь можно приступать к созданию сводной таблицы.

Создание сводной на основе базы данных Access
Прежде чем создавать сводную на основе внешних источников необходимо добавить на панель быстрого доступа кнопку, которая позволит это сделать:

• Excel 2010 — Файл (File)
  — Параметры (Options)
  —
• Excel 2007 — Кнопка офис
  — Параметры Excel (Excel options)
  — Панель быстрого доступа (Quick Access Toolbar)
  

или непосредственно с панели быстрого доступа:

Выбрать команды из: Все команды (All Commands)
. Ищем там Мастер сводных таблиц и диаграмм (PivotTable and PivotChart Wizard)
и переносим на панель быстрого доступа:

Теперь жмем на эту кнопку и на первом шаге появившегося окна Мастера выбираем во внешнем источнике данных (external data source)
:

на втором шаге жмем кнопку Получить данные (Get Data)
:
В появившемся окне необходимо выбрать MS Access Database
или База данных MS Access
.
Тут есть важный момент. Галочка Использовать мастер запросов (Use the Query Wizard to create/edit queries)
должна быть включена. Жмем ОК
.

Далее выбираем в правом окне папку, в которой расположена наша база данных. В левом окне выбираем сам файл Базы данных:

Подтверждаем выбор нажатием кнопки ОК
.
Далее необходимо создать запрос выборки. По сути можно просто нажать на имя таблицы Базы данных и после этого на значек «>»
Но если вам необходимо будет работать только с некоторыми столбцами из всей таблицы — можно последовательно перенести их в правое поле(предварительно в левом развернув плюсик рядом с именем таблицы). Убрать лишние столбцы из правого поля можно кнопочкой «Далее. В следующем окне будет предложено сделать выборку на основании условий(правил):
Если вы не очень понимаете что это и оно вам не надо(а в данном случае оно вам действительно не надо) — просто пропускаем этот шаг и жмем опять Далее
.
Так же пропускаем данный шаг(сортировку) — он нам совершенно ни к чему, т.к. данные мы будем все равно обрабатывать сводной. Поэтому опять жмем Далее
. И завершающий шаг мастера запросов:
выбираем Вернуть данные в Microsoft Excel (Return Data to Microsoft Excel)
и жмем Готово
. При этом в окне второго шага мастера сводных таблиц и диаграмм правее кнопки Получить данные
должна появиться надпись Данные получены (Data fields have been retrieved)
:
Если у вас данная надпись появилась, то смело жмем Далее (Next)
и на последнем шаге мастера выбираем ячейку и лист, в которые необходимо поместить сводную таблицу:
Либо на имеющийся лист(придется указать ячейку, в которой будет размещаться первая строка данных), либо на новый лист.

Жмем Готово (Finish)
.

Все, наша сводная готова к работе и теперь вы можете обрабатывать свои миллионы записей быстро и легко в привычном интерфейсе самой сводной.
Статья помогла? Поделись ссылкой с друзьями!

Источник: https://lab-music.ru/upravlenie-dannymi-s-pomoshchyu-access-ili-excel-svodnaya-iz-bazy-dannyh/

Создание сводной таблицы Excel из нескольких листов

Сводная таблица применяется для быстрого анализа большого объема данных. Она позволяет объединять информацию из разных таблиц и листов, подсчитать общий результат. Этот универсальный аналитический инструмент существенно расширяет возможности программы Excel.

Можно сформировать новые итоги по исходным параметрам, поменяв строки и столбцы местами. Можно произвести фильтрацию данных, показав разные элементы. А также наглядно детализировать область.

Для примера используем таблицу реализации товара в разных торговых филиалах.

Из таблички видно, в каком отделе, что, когда и на какую сумму было продано. Чтобы найти величину продаж по каждому отделу, придется посчитать вручную на калькуляторе. Либо сделать еще одну таблицу Excel, где посредством формул показать итоги. Такими методами анализировать информацию непродуктивно. Недолго и ошибиться.

Самое рациональное решение – это создание сводной таблицы в Excel:

1. Выделяем ячейку А1, чтобы Excel знал, с какой информацией придется работать.
2. В меню «Вставка» выбираем «Сводная таблица».



3. Откроется меню «Создание сводной таблицы», где выбираем диапазон и указываем место. Так как мы установили курсор в ячейку с данными, поле диапазона заполнится автоматически. Если курсор стоит в пустой ячейке, необходимо прописать диапазон вручную. Сводную таблицу можно сделать на этом же листе или на другом. Если мы хотим, чтобы сводные данные были на существующей странице, не забывайте указывать для них место. На странице появляется следующая форма:




4. Сформируем табличку, которая покажет сумму продаж по отделам. В списке полей сводной таблицы выбираем названия столбцов, которые нас интересуют. Получаем итоги по каждому отделу.

Просто, быстро и качественно.

Важные нюансы:

• Первая строка заданного для сведения данных диапазона должна быть заполнена.
• В базовой табличке каждый столбец должен иметь свой заголовок – проще настроить сводный отчет.
• В Excel в качестве источника информации можно использовать таблицы Access, SQL Server и др.



Часто требуется создавать сводные отчеты из нескольких таблиц. Есть пара табличек с информацией. Нужно объединить их в одну общую. Для науки придумаем остатки на складах в двух магазинах.

Порядок создания сводной таблицы из нескольких листов такой же.

Создадим отчет с помощью мастера сводных таблиц:

1. Вызываем меню «Мастер сводных таблиц и диаграмм». Для этого щелкаем кнопку настройки панели быстрого доступа и нажимаем «Другие команды». Здесь на вкладке «Настройка» находим «Мастер сводных таблиц». Добавляем инструмент в панель быстрого доступа. После добавления:





2. Ставим курсор на первую табличку и нажимаем инструмент «Мастера». В открывшемся окне отмечаем, что создать таблицу хотим в «нескольких диапазонах консолидации». То есть нам нужно объединить несколько мест с информацией. Вид отчета – «сводная таблица». «Далее».



3. Следующий этап – «создать поля». «Далее».
4. Прописываем диапазон данных, по которым будем формировать сводный отчет. Выделяем первый диапазон вместе с шапкой – «добавить». Второй диапазон вместе с названием столбцов – снова «добавить».
5. Теперь в списке выбираем первый диапазон. Ставим птичку у единицы. Это первое поле сводного отчета. Даем ему имя – «Магазин 1». Выделяем второй диапазон данных – ставим птичку у цифры «2». Название поля – «Магазин 2». Нажимаем «Далее».
6. Выбираем, где расположить сводную таблицу. На существующем листе или новом. Лучше выбрать новый лист, чтобы не было наложений и смещений. У нас получилось так:

Как видите всего в несколько кликов можно создавать сложные отчеты из нескольких листов или таблиц разного объема информации.

Как работать со сводными таблицами в Excel

Начнем с простейшего: добавления и удаления столбцов. Для примера рассмотрим сводную табличку продаж по разным отделам (см. выше).

Справа от сводной таблицы у нас была область задач, где мы выбирали столбцы в списке полей. Если она исчезла, просто щелкаем мышью по табличке.

Добавим в сводную таблицу еще одно поле для отчета. Для этого установим галочку напротив «Даты» (или напротив «Товара»). Отчет сразу меняется – появляется динамика продаж по дням в каждом отделе.

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

А вот что получится, если мы уберем «дату» и добавим «отдел»:

А вот такой отчет можно сделать, если перетащить поля между разными областями:

Чтобы название строки сделать названием столбца, выбираем это название, щелкаем по всплывающему меню. Нажимаем «переместить в название столбцов». Таким способом мы переместили дату в столбцы.

Поле «Отдел» мы проставили перед наименованиями товаров. Воспользовавшись разделом меню «переместить в начало».

Покажем детали по конкретному продукту. На примере второй сводной таблицы, где отображены остатки на складах. Выделяем ячейку. Щелкаем правой кнопкой мыши – «развернуть».

В открывшемся меню выбираем поле с данными, которые необходимо показать.

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

Проверка правильности выставленных коммунальных счетов

С помощью сводных таблиц Excel легко проверить, насколько правильно обслуживающие организации начисляют квартплату. Другой положительный момент – экономия. Если мы будем ежемесячно контролировать, сколько расходуется света, газа, то сможем найти резерв для экономии средств на оплату квартиры.

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

Для примера мы сделали сводную табличку тарифов для Москвы:

Для учебных целей возьмем семью из 4 человек, которые проживают в квартире 60 кв. м. Чтобы контролировать коммунальные платежи, необходимо создать таблицы для расчета на каждый месяц.

• Первый столбец = первому столбцу из сводной таблицы. Второй – формула для расчета вида:
• = тариф * количество человек / показания счетчика / площадь
• Для удобства рекомендуем сделать промежуточный столбец, в который будут заноситься показания по счетчикам (переменная составляющая).
• Наши формулы ссылаются на лист, где расположена сводная таблица с тарифами.
• Скачать все примеры сводной таблицы

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

Источник: https://exceltable.com/svodnye-tablicy/sozdanie-svodnoi-tablici-v-excel

Сводные таблицы в Excel

Жизнь человека из мира технологий — это невероятная комбинация цифр, показателей, которые периодически требуют систематизации. Чтобы осуществить последнее, приходится использовать особые технические инструменты. Наша статья докажет, что сводные таблицы в Excel доступны даже для чайников.

Немного теории

Сводные таблицы Excel (для чайника) — это разновидность реестра, которая содержит конкретную часть данных из источника для анализа и изображена так, чтобы можно было проследить между ними логические связи. Основа для ее проектирования — определенный список значений.

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

Сводные таблицы в Excel незаменимы для тех, кто по роду занятости имеет дело с большим количеством цифр, которые периодически необходимо систематизировать и формировать отчетность. Программа Excel поможет подсчитать и проанализировать большое количество значений, сэкономив время.

Плюсы использования такого вида группировки данных:

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

Обучение работе со сводными таблицами в Excel не займет много времени и может основываться на видео.

Пример создания сводной таблицы Excel – алгоритм для чайников

Ознакомившись с базовыми теоретическими нюансами про сводные таблицы в Excel, давайте перейдем к применению их на деле. Для старта создания сводной таблицы в Excel 2016, 2010 или 2007 необходимо установить программное обеспечение.

Как правило, если вы пользуетесь программами системы Microsoft Office, то Excel уже есть на вашем компьютере. Запустив его, перед вами откроется обширное поле, разделенное на большое количество ячеек. Более детально о том, как делать сводные таблицы в Excel, вам подскажет видеоурок выше.

С помощью следующего алгоритма мы детально рассмотрим пример, как построить сводную таблицу в Excel. На панели вверху окна переходим на вкладку «Вставка», где слева в углу выбираем «Сводная таблица».

Далее на экране открывается диалоговое окошко, где требуется указать соответствующие параметры. На этом этапе создания сводных таблиц в Excel есть несколько важных моментов. Если перед тем, как начать формирование реестра, вы установили значок курсора на листе, то заполнение пустых строчек окна произойдёт автоматически. Иначе адрес диапазона данных нужно обозначить самостоятельно.

Рассмотрим детальней самостоятельное заполнение пунктов диалогового окна.

Первую строку не оставляем пустой, иначе программа выдаст ошибку. Если есть источник, с которого планируете переноситься данные, то выберите его в пункте «Использовать внешний источник данных». Под внешним источником подразумевается другая книга Excel или набор моделей данных из СУБД.

Заранее озаглавьте каждый столбик

Выберите место, где будет располагаться будущая рамка с ячейками. Это может быть новое окно или же этот лист, рекомендуем использовать другой лист. Закрепив все настройки, получаем готовую основу. По левую сторону располагается область, где размещена основа будущей рамки. В правой части есть окно с настройками, которое помогает управлять реестром.

• 
• 
• 

Теперь необходимо разобраться, как строится вся эта конструкция. В окне настроек «Поля свободной таблицы», вы обозначаете данные, которые будут присутствовать. Вся структура строится таким образом, чтобы текстовые данные играли роль объединяющих элементов, а числовые показывали консолидированные значения. Например, объединим все поступления по отделам и узнаем на какую сумму получил товары каждый. Для этого поставим галочку у двух заголовков: отдел и стоимость товара в партии. Обратите внимание, как расположились эти данные в нижней области панели настройки. Отдел автоматически ушел в строки, а числовые данные в значения. Если попробовать щелкнуть по любому столбцу с числами, они будут появляться в этой области. А в самой таблице добавится новый столбец. При этом происходит суммирование. Сейчас мы можем узнать из нашего отчета, сколько товаров поступило в каждый отдел и их общую стоимость.

Вы можете свободно корректировать ширину столбиков для оптимального расположения данных. Просто раздвигайте ширину столбцов или высоту строк так, как вы привыкли в Excel. Если вам не нравится такая ориентация, вы можете перетащить мышкой названия строк в область столбцов — просто наведите мышь, нажмите левую кнопку и тяните.

Что касается подсчета результатов, то сумма – далеко не единственная функция. Чтобы посмотреть, какие Excel предлагает еще, нажмите на название в области значений и выберите последнюю команду. В параметрах полей значений вы найдете множество вариантов для анализа. Для каждого значения можно выбрать свою функцию. Например, добавим поле «Цена» и найдем максимальную цену товара в каждом отделе. Фактически, узнаем сколько стоит самый дорогой. Теперь мы видим, что в отдел «Аксессуары» поступило товаров на сумму 267660 рублей, при этом самый дорогостоящий имеет цену 2700 рублей. Область «Фильтры» позволяет установить критерий отбора записей. Добавим поле «Дата поступление», просто поставив около него галочку. Сейчас сводная таблица в Excel выглядит неудобно, если нам необходимо провести анализ по дате. Поэтому переместим дату из строк в фильтры — просто перетяните, как было указано выше. Итогом этих действий стало появление еще одного поля сверху. Чтобы выбрать дату, нажмем на стрелочку около слова «Все». Теперь нам доступен выбор конкретного дня, чтобы открыть список, щелкайте по треугольнику в правом углу. Также можно выбрать и значения для отдела. Снимите галочки с тех, которые вас не интересуют, и вы получите только нужную информацию.

Во время работы вы можете столкнуться с подобным сообщением «недопустимое имя сводной таблицы Excel». Это означает, что первая строка диапазона, откуда пытаются извлечь информацию, осталась с незаполненными ячейками. Чтобы решить эту проблему, вы должны заполнить пустоты колонки.

Обновление данных в сводной таблице в Excel

Важным вопросом является то, как сделать и обновить сводную таблицу в Excel 2010 или другой версии. Это актуально тогда, когда вы собираетесь добавить новые данные.

Если обновление будет проходить только для одного столбца, то необходимо на любом её месте щелкнуть правой кнопкой мыши. В появившемся окне нужно нажать «Обновить».

Если же подобное действие необходимо провести сразу с несколькими столбцами и строками, то выделите любую зону и на верхней панели откройте вкладку «Анализ» и кликните на значок «Обновить». Дальше выбирайте желаемое действие.

Если сводная таблица в Excel не нужна, то стоит разобраться, как её удалить. Это не составит большого труда. Выделите все составляющие вручную, или используя сочетание клавиш «CTRL+A». Далее нажмите клавишу «DELETE» и поле будет очищено.

Как в сводную таблицу Excel добавить столбец или таблицу

Чтобы добавить дополнительный столбец, вам необходимо добавить его в исходные данные и расширить диапазон для нашего реестра. Перейдите на вкладку «Анализ» и откройте источник данных.

Excel сам все предложит. Обновите и вы получите новый перечень полей в области настройки. Добавить таблицу вы сможете только если «склеите» ее с исходной. Можно заменить диапазон в уже существующей, но добавить «на лету» другой диапазон нельзя. Зато можно создать новую сводную таблицу на основе нескольких исходных, даже расположенных на разных листах.

Как сделать сводную таблицу в Excel из нескольких листов

Для этого нам понадобится мастер сводных таблиц. Добавим его на панель быстрого доступа (самый верх окна – слева). Нажмите выпадающую стрелочку и выберите «Другие команды». Выберите все команды.

И найдите мастер сводных таблиц Excel, кликните по нему, затем на «Добавить» и ОК. Значок появится сверху. У вас должны быть две идентичные по полям таблицы на разных листах. У нас это данные о поступлениях в отделы за май и июнь. Нажимайте на ярлык мастера сводных таблиц и выбирайте консолидацию диапазонов. Нам нужно несколько полей, а не одно. На следующем этапе выделите первый диапазон и нажмите кнопку «Добавить». Затем переключитесь на другой лист ( щелкните по его названию внизу) и снова «Добавить». У вас будут созданы два диапазона. Не стоит выделять всю таблицу целиком. Нам нужна информация о поступлениях в отделы, поэтому мы выделили диапазон, начиная со столбца «Отдел». Дайте имя каждому. Кликайте кружочек 1, затем в поле вписывайте «май», кликайте кружочек 2 и вписывайте в поле 2 «июнь». Не забывайте менять диапазоны в области. Должен быть выделен тот, который именуем. Щелкайте «Далее» и создавайте на новом листе. После нажатия на «Готово» получим результат. Это многомерная таблица, так что управлять ей довольно сложно. Поэтому мы и выбрали диапазон меньше, чтобы не запутаться в измерениях. Обратите внимание, что у нас уже нет четких названий полей. Их можно вытащить, нажав на пункты в верхней области. Снимая или устанавливая галочки, вы регулируете значения, которые вам необходимо увидеть. Неудобно и то, что расчет проводится для всех значений одинаковый. Как видите, у нас одно значение в соответствующей области.

Изменение структуры отчёта

Мы поэтапно разобрали пример, как создать сводную таблицу Exce, а как получить данные другого вида расскажем далее. Для этого мы изменим макет отчета. Установив курсор на любой ячейке, переходим во вкладку «Конструктор», а следом «Макет отчета».

Вам откроются на выбор три типа для структуризации информации:

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

Все показатели подаются иерархично: от малого к большому.

Информация представляется под видом реестра. Это позволяет легко переносить ячейки на новые листы.

Остановив выбор на подходящем макете, вы закрепляете внесенные коррективы.

Источник: https://WindowsTips.ru/svodnye-tablicy-v-excel

Создание отчётов в Microsoft Access

Access обладает обширным набором возможностей и инструментов для работы с базами данных. Перед пользователями, использующими эту программу, могут ставиться самые разные задачи, которые нужно уметь решать. В этой статье разберёмся, как создать отчёт в Access, что это такое и для чего он нужен. Давайте же начнём. Поехали!

Отчёты в Microsoft Access служат для вывода на экран либо печати информации из базы данных. Любой отчёт можно создать на основе таблицы или на основе запроса. Делается это очень просто, всего в пару кликов.

Для начала у вас должна быть готовая таблица, с данными которой вы будете работать. Затем выделите нужную таблицу в окне «Все объекты Access», расположенном слева, перейдите на вкладку «Создание» и нажмите кнопку «Отчёт» в одноимённом разделе. Всё готово. Перед вами будет сформированный программой отчёт на основе информации, содержащейся в таблице.

После этого вы можете перейти на вкладку «Конструктор», чтобы отредактировать поля, разделы и прочее. Чтобы выйти из режима конструктора, щёлкните правой клавишей мыши по вкладке таблицы и выберите пункт «Представление отчёта». Не забудьте сохранить перед выходом.

Для более сложных случаев используйте «Мастер отчётов», соответствующая кнопка находится в той же вкладке «Создание», этот инструмент пригодится в том случае, если необходимо оперировать с данными из нескольких таблиц сразу.

В открывшемся окне выберите из списка те таблицы и запросы, из которых будут взяты поля (их также необходимо указывать). Закончив, нажмите кнопку «Далее». В следующем окне вам будет предложено выбрать подходящий вид представления данных. Затем вы можете сгруппировать выбранные поля по своему усмотрению. Просто выберите нужное поле и нажмите на кнопку со стрелкой.

Далее, откроется окно сортировки записей. Делать сортировку необязательно, поэтому вы можете пропустить этот этап. В следующем окне выберите «Макет» (ступенчатый, блок, структура) и «Ориентацию» (книжная, альбомная). Оставьте отмеченным пункт «Настроить ширину полей для размещения на одной странице». После этого остаётся ввести название и нажать кнопку «Готово».

Чтобы исправить общий вид и некорректно отображаемые данные, перейдите в режим конструктора. Там вы можете выделять отдельные поля и перемещать их при помощи стрелок на клавиатуре или мышкой.

Убрать большие промежутки между столбцами, можно не переходя в «Конструктор». Достаточно передвинуть границы курсором мыши. В случае, если часть данных у вас отображаются в виде решёток «#», просто увеличьте эти поля и информация будет отображена корректно.

В режиме конструктора можно изменять цвет текста, подложки, заголовки.

Существует возможность создания определённых ячеек, в которых будет рассчитываться сумма, разница или произведение на основе данных из таблиц или запросов.

Для этого выделите нужное поле и нажмите кнопку «Итоги», расположенную на панели инструментов в разделе «Группировка и итоги».

В появившемся меню выберите «Число записей» или «Количество значений» в зависимости от того, какие перед вами стоят задачи.

Ещё один способ создания — «Конструктор отчётов». Нажав эту кнопку, перед вами появится область, в которую нужно добавить поля при помощи соответствующей кнопки в ленте инструментов.

Теперь вы будете знать, как сделать отчёт в Microsoft Access. Это позволит вам лучше работать с базами данных. Оставляйте в х своё мнение об этой статье: оказалась ли она полезной, и спрашивайте, если что-то осталось непонятным.

Источник: https://nastroyvse.ru/programs/review/kak-sozdat-otcet-access.html

Создание сводной таблицы на основе внешнего источника данных (на примере MS Access)

Прежде всего, следует разобраться, как создать подключение к внешнему источнику данных. 

Это можно сделать несколькими способами

Раз речь идет о сводных таблицах, то логично использовать Мастер сводных таблиц. В Excel 2003 Мастер запускается сразу при попытке создания сводной таблицы через меню Данные — Сводная таблица. Начиная с Excel 2007 для использования Мастера сводных таблиц, нужно вынести на Панель быстрого доступа одноимённую кнопку.

На первом шаге Мастера сводных таблиц выбираем вариант Во внешнем источнике данных — Далее

На втором шаге Мастера для создания нового подключения нажимаем кнопку Получить данные…

После нажатия кнопки Получить данные… в открывшемся диалоговом окне предлагается выбрать источник данных для будущей сводной таблицы. Дальнейшая работа будет рассмотрена на примере файла Access.

 Если Вы импортируете данные из файла .mdb, то следует выбрать вариант База данных MS Access, а если более поздних версий .accdb, — то MS Access Database.

 Флажок Использовать Мастер запросов должен быть установлен

• 
• После нажатия ОК в раскрывшемся диалоговом окне выбираем файл-источник данных
• 
• В качестве примера используем простую базу данных Отпуск товара со склада, которая имеет следующую схему данных
• 
• После выбора файла и нажатия ОК получаем следующее окно, в котором предлагается выбрать таблицы, запросы и, соответственно, поля, которые нужны для будущей сводной таблицы
• 

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

Причём, если слева выделена таблица или запрос, то при нажатии кнопки > перенесутся все поля из этой таблицы.

Если все поля не нужны, нажимаем кнопку со значком + слева от названия таблицы, раскрывается список полей, и мы можем переносить поля по одному.

Кнопки

Источник: http://www.excelworld.ru/publ/hacks/pivot_table/pivot_table_from_outer_source_access/66-1-0-140

Конструирование форм базы данных средствами ms Access и работа с ними

25

Microsoft
Office Access или
просто Microsoft
Access — реляционная
СУБД
( Система
управления реляционными базами данных
) корпорации Microsoft.
Имеет широкий спектр функций, включая
связанные запросы, связь с внешними
таблицами и базами
данных.
Access предоставляет возможность вводить
данные как непосредственно в таблицу,
так и с помощью форм. 

Форма
— это объект базы данных, который можно
использовать для создания интерфейса
пользователя для приложения базы данных,
структурированное окно, которое можно
представить так, чтобы оно повторяло
форму бланка. Формы создаются из набора
отдельных элементов управления.
Формы в Access служат для удобного
представления данных БД пользователю.

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

• ввод и просмотр информации базы данных;
• добавление;
• редактировпние;
• удаление данных БД;
• печать;
• создание сообщений.

• Источники
  данных для форм
• Источниками
  данных для форм могут служить таблицы
  и запросы.
• Режим
  отображения форм

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

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

Таким образом, данные отображаются
практически так же, как при реальном
использовании формы.

Поскольку при
изменении формы отображаются реальные
данные, в режиме макета удобно задавать
размер элементов управления и выполнять
иные задачи, влияющие на вид и удобство
использования форм.

Режим
конструктора
— более подробное редактирование формы.
Отображает полную структуру отчета,
т.е. колонтитулы элементы управления и
т.д. В этом режиме данные не отображаются, поэтому заранее нельзя увидеть какой будут иметь вид данные в этом режиме.
Позволяет выполнять такие задачи:

• добавление в форму дополнительных элементов управления, таких как границы привязанных объектов, разрывы страниц и диаграммы;
• изменение источников элемента управления «текстовое поле» непосредственно в самом поле, без использования окна свойств;
• изменение размеров разделов формы, таких как «Заголовок формы» или «Область данных»;
• изменение определенных свойств формы, которые нельзя изменить в режиме макета.

Элементы
управления форм

Элементы
управления предназначены для просмотра
и работы с данными. Выделяют такие
элементы управления:

• Связанные элементы управления     это элементы управления, которые связаны с источником данных (таблица, запроса).
• Свободные элементы управления     это элементы управления, которые не связаны с источником данных (например, линии, рисунки, надписи заголовков форм).

1. Вычисляемые
   элементы управления     это элементы управления, источником данных
   у которых является выражение.
2. Существуют
   такие виды форм:
3. «Свободная»
   форма,
   которая не связана напрямую с источником
   данных, но которая все равно может
   содержать кнопки, надписи и другие
   элементы управления, необходимые для
   работы приложения.
4. Подчиненная
   форма
   — это форма, которая является составным
   элементом главной формы, отображает
   данные траблицы или запроса связанных
   отношением один-ко-многим.
5. Свойства
   подчиненной формы:

• Свойство «Объект источник» — отображает информацию об объекте, который отображается в форме.
• Свойство «Подчиненные поля» — свойство элемента управления подчиненной формы, определяющее поля по которым будут связаны главная и подчиненная форма.
• Свойство «Основные поля» — свойство элемента управления подчиненной формы, определяющие поля главной формы, которые будут связывать ее с полями подчиненной формы.

• Инструменты
  создания форм
• Access
  включает такие инструменты для создания
  форм: Форма, Конструктор форм, Пустая
  форма, Мастер форм, Навигация и Другие
  формы (Несколько элементов, Таблица,
  Разделенная форма, Модальное диалоговое
  окно, Сводная диаграмма, Сводная таблица).
• Способы
  создания форм:

• Конструктор форм (предназначен для создания формы любой сложности);
• Мастер форм (позволяет создавать формы различные как по стилю, так и по содержанию);
• Автоформа: в столбец (многостраничная – поля для записи выводятся в один столбец, в форме одновременно отображаются данные для одной записи);
• Автоформа: ленточная (все поля записи выводятся в одну строку, в форме отображаются все записи);
• Автоформа: табличная (отображение записей осуществляется в режиме таблица);
• Автоформа: сводная таблица;
• Автоформа: сводная диаграмма;
• Диаграмма (создается форма с диаграммой, построенной Microsoft Graph);
• Сводная таблица (создается форма Access, отображаемая в режиме сводной таблицы Excel);

Создание
формы с помощью инструмента «Форма»

Режим
форма позволяет пользователю быстро
создавать формы. С помощью инструмента
«Форма» можно создать форму одним
щелчком мыши. При использовании этого
средства все поля базового источника
данных размещаются в форме. Можно сразу
же начать использование новой формы
либо при необходимости изменить ее в
режиме макета или конструктора.

Для
этого необходимо:

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

2. На вкладке Создать в группе Формы нажмите кнопку Форма.

Будет
создана новая форма и отображена в
режиме макета. В режиме макета можно
внести изменения в структуру формы при
одновременном отображении данных.
Например, при необходимости можно
настроить размер полей в соответствии
с данными.

Создание
формы с помощью инструмента «Конструктор
форм»

Для
того, чтобы создать форму с помощью
инструмента Конструктор форм, выполните
такие действия.

1.
Нажмите на кнопку Конструктор
форм
в группе Формы
на вкладке Создание.
В результате выполненных действий в
рабочей области окна появится бланкпустой
формы, который не связан ни с одним
источником данных.

1. ПКМ -> Колонтитулы (Указываете авторскую метку)-> заголовок/примечание (название формы, текущее время и дата)

3.
Свяжите форму с источником данных. Для
этого на панели инструментов нажмите
на кнопку Свойства и выберите источник
данных для формы.

4.
Перетащите необходимые поля для формы.

Источник: https://studfile.net/preview/1756097/

Иллюстрированный самоучитель по Microsoft Access 2002

Одним из ключевых новшеств Access 2002 является появление новых представлений данных: теперь таблицы и запросы баз данных Access (файлов MDB) и таблицы, представления, хранимые процедуры и функции проектов (файлов ADP), а также формы можно просмотреть в режиме Сводная таблица или Сводная диаграмма. Эта возможность позволяет быстро и легко реализовать комплексные решения для анализа данных. О ней рассказано в гл. 8.

Сводные таблицы и сводные диаграммы можно сохранить в виде страниц доступа к данным, чтобы предоставить информацию всем, кто имеет программу просмотра Microsoft Internet Explorer 5 или выше.

Теперь формы Access в представлении сводной таблицы и сводной диаграммы можно использовать в качестве вложенной формы в составных формах, точно таким же способом, как раньше можно было использовать для этого формы в представлении режим Таблицы.

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

Новые возможности работы с формами и отчетами

В формах Access появились новые события, для которых разработчики могут задать процедуры обработки, чтобы расширить функциональность своего приложения.

• Событие Отмена (OnUndo) уровня формы возникает, когда пользователь отменяет все изменения, сделанные в форме.
• Событие Выход из записи (OnRecordExit) уровня формы возникает, когда пользователь переходит от текущей записи к следующей. Это событие может существенно упростить процесс обновления подчиненных форм в зависимости от состояния основной формы.
• События Внесены изменения (OnDirty) и Отмена (OnUndo) уровня элемента управления полностью аналогичны одноименным событиям уровня формы.

У форм и отчетов Access появилось несколько новых свойств.

• Добавился параметр, позволяющий установить использование заданного значка приложения для отображения также в формах и отчетах. Этот параметр устанавливается в диалоговом окне, отображающемся после выполнения команды Сервис › Параметры запуска (Tools › Start up).
• У отчетов Access теперь появились новые свойства: Модальное окно (Modal), Всплывающее окно (PopUp), Тип границы (BorderStyle), Автоматический размер (AutoResize), Выравнивание по центру (AutoCenter), Кнопки размеров окна (MinMaxButtons), Кнопка закрытия (CloseButton), Кнопка оконного меню (ControlBox), полностью аналогичные одноименным свойствам форм Access.
• К методу OpenReport (Открыть отчет) объекта DoCmd (Выполнить команду) теперь добавлен аргумент view (режим), позволяющий задать представление для открываемого отчета, так же, как это можно было раньше сделать только для формы.
• У метода OpenForm (Открыть форму) объекта DoCmd (Выполнить команду) появился новый необязательный аргумент OpenArgs (Аргументы открытия) типа Variant. Этот аргумент позволяет в момент открытия формы передать в программу управления формой произвольную строку, содержащую некоторую полезную информацию, которая может быть впоследствии обработана, например в обработчике события Open (Открытие) или одноименном макросе.
• У форм, отчетов и элементов управления теперь есть свойство Допускается перемещение (Moveable), позволяющее разрешить или запретить перемещение соответствующего объекта по экрану.

У форм и отчетов Access появился новый метод Move, позволяющий программным способом перемещать их по экрану.

В новой версии усовершенствовано отображение подчиненных форм и отчетов в режиме Конструктора внутри основной формы или отчета. Теперь работать с полосами прокрутки стало удобнее.

Кроме того, вы можете полностью просмотреть подчиненную форму/отчет, отображенную в режиме Конструктора внутри основной формы/отчета, открыв ее в отдельном окне: для этого выделите подчиненную форму/отчет и выберите команду Вид › Подчиненная форма в новом окне/Подчиненный отчет в новом окне (View › Subform/Subreport in New Window) или одноименную команду контекстного меню.

Источник: http://samoychiteli.ru/document18179.html