Работа в excel с базой данных – Работа с таблицей как с базой данных в среде Excel (сортировка, фильтрация, использование форм, получение итогов).

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

• таблица должна обязательно содержать заголовки столбцов. Эти заголовки должны располагаться строго в одной строке, не должны содержать объединенных и пустых ячеек.
• таблица должна быть неделимая, т.е. не должна содержать полностью пустых строк и столбцов, а так же объединенных ячеек
• в каждом столбце должна содержаться однотипная информация: если в столбце должны содержаться даты, значит кроме дат там не должно быть ничего другого; если в столбце числа(суммы, кол-во) — значит должны быть только числа. Не следует при отсутствии чисел оставлять ячейку пустой или ставить пробел. Вместо этого необходимо ставить 0.

Для разбора функции баз данных возьмем следующий пример таблицы:

Данная таблица отвечает всем требованиям для работы с функциями баз данных, однако, чтобы более удобно и гибко работать с функциями баз данных лучше сместить таблицу данных на несколько строк вниз, а выше добавить таблицу критериев, где будут формироваться условия отбора данных из основной таблицы:

Именно для этой таблицы будут приведены все примеры описаний функций. И критерии заданы следующие: отбирать из поля «Дерево» Яблони с высотой больше 3 и меньше 6 и Вишни, со значением в поле «Возраст» больше 8. Если посмотреть на таблицу данных(из которой будут отбираться данные и производится расчеты функциями), то этим критериям отвечают только две строки: строки 9 и 10 листа.

Все функции из категории баз данных имеют три одинаковых аргумента:
Функция(база_данных; поле; критерий)
база_данных — ссылка на ячейки данных таблицы, включая заголовок(A6:E12).
поле — в данном аргументе можно записать как непосредственно текст с названием столбца в кавычках («Дерево», «Возраст» или «Урожай»), так и число, задающее положение столбца в таблице: 1 — для первого поля(столбца) в таблице «Дерево», 2 — для второго поля «Высота», 3 — для третьего поля «Возраст» и так далее.
критерий — ссылка на диапазон ячеек с условиями отбора(A1:F3). Функция отберет данные из таблицы, которые удовлетворяют условиям, указанным в ячейках критериев. В ссылке на критерии обязательно должны быть включены названия столбцов, для которых выполняется отбор данных.

• ДСРЗНАЧ(DAVERAGE) — Вычисляет среднее значение выбранных записей базы данных:
  =ДСРЗНАЧ(A6:E12;5;A1:F3)
  =ДСРЗНАЧ(A6:E12;»Прибыль»;A1:F3)
  =DAVERAGE(A6:E12,5,A1:F3)вернет значение 90 000р., т.к. сумма прибыли отобранных записей равна 180 000р., а всего отобрано 2 записи. 180 000/2 = 90 000.
• БСЧЁТ(DCOUNT) — Подсчитывает количество числовых ячеек в базе данных:
  =БСЧЁТ(A6:E12;5;A1:F3)
  =БСЧЁТ(A6:E12;»Прибыль»;A1:F3)
  =DCOUNT(A6:E12,5,A1:F3)вернет число 2, т.к. только две строки в таблице отвечают критериям
• БСЧЁТА(DCOUNTA) — Подсчитывает количество непустых ячеек в базе данных:
  =БСЧЁТА(A6:E12;4;A1:F3)
  =БСЧЁТА(A6:E12;»Прибыль»;A1:F3)
  =DCOUNTA(A6:E12,4,A1:F3)вернет 2, т.е. подсчитает в отвечающих критериям строках количество непустых ячеек в столбце «Прибыль»
• БИЗВЛЕЧЬ(DGET)
  — Извлекает из базы данных одну запись, удовлетворяющую заданному условию:
  =БИЗВЛЕЧЬ(A6:E12;5;A1:F3)
  =БИЗВЛЕЧЬ(A6:E12;»Прибыль»;A1:F3)
  =DGET(A6:E12,5,A1:F3)для заданных условий вернет значение ошибки #ЧИСЛО!(#NUM!), т.к. этим условиям отвечает более одной записи. Если же указать диапазон для критерия как:
  =БИЗВЛЕЧЬ(A6:E12;5;A1:F2)то функция вернет значение 75 000р., т.е. единственную запись о прибыли для Яблонь с высотой больше 3 и меньше 6 (в данный промежуток попадает лишь строка 10 — Яблона, высота 5)
• ДМАКС(DMAX) — Находит максимальное значение среди выделенных записей базы данных:
  =ДМАКС(A6:E12;5;A1:F3)
  =ДМАКС(A6:E12;»Прибыль»;A1:F3)
  =DMAX(A6:E12,5,A1:F3)вернет сумму 105 000р., т.к. это максимальная прибыль из всех отвечающих критериям строк.
• ДМИН(DMIN) — Находит минимальное значение среди выделенных записей базы данных:
  =ДМИН(A6:E12;5;A1:F3)
  =ДМИН(A6:E12;»Прибыль»;A1:F3)
  =DMIN(A6:E12,5,A1:F3)вернет сумму 75 000р., т.к. это минимальная прибыль из всех строк, отвечающих критериям
• БДПРОИЗВЕД(DPRODUCT) — Перемножает значения определенного поля в записях базы данных, удовлетворяющих условию:
  =БДПРОИЗВЕД(A6:E12;3;A1:F3)
  =БДПРОИЗВЕД(A6:E12;»Возраст»;A1:F3)
  =DPRODUCT(A6:E12,3,A1:F3)вернет 210, т.к. будут перемножены все значения столбца «Возраст», отвечающие критериям(14*15=210)
• ДСТАНДОТКЛ(DSTDEV) — Оценивает стандартное отклонение по выборке из выделенных записей базы данных:
  =ДСТАНДОТКЛ(A6:E12;4;A1:F3)
  =ДСТАНДОТКЛ(A6:E12;»Урожайность»;A1:F3)
  =DSTDEV(A6:E12,4,A1:F3)вернет 0,707107, т.е. оценку стандартного отклонения урожайности по указанным критериям.
• ДСТАНДОТКЛП(DSTDEVP) — Вычисляет стандартное отклонение по генеральной совокупности из выделенных записей базы данных:
  =ДСТАНДОТКЛП(A6:E12;4;A1:F3)
  =ДСТАНДОТКЛП(A6:E12;»Урожайность»;A1:F3)
  =DSTDEVP(A6:E12,4,A1:F3)вернет 0,5, т.е. точное стандартное отклонение урожайности по указанным критериям, если считать, что данные в базе данных описывают генеральную совокупность всех деревьев в саду.
• БДСУММ(DSUM) — Суммирует числа в поле для записей базы данных, удовлетворяющих условию:
  =БДСУММ(A6:E12;5;A1:F3)
  =БДСУММ(A6:E12;»Прибыль»;A1:F3)
  =DSUM(A6:E12,5,A1:F3)вернет сумму прибыли всех строк, отвечающих критериям, т.е. 180 000р.
  =БДСУММ(A6:E12;5;A1:A2)
  =DSUM(A6:E12,5,A1:A2)вернет сумму прибыли от всех Яблонь, т.е. 225 000р.
• БДДИСП(DVAR) — Оценивает дисперсию по выборке из выделенных записей базы данных:
  =БДДИСП(A6:E12;4;A1:A2)
  =БДДИСП(A6:E12;»Урожайность»;A1:A2)
  =DVAR(A6:E12,4,A1:A2)вернет 0,5, что будет оценкой дисперсии урожайности по указанным критериям, если считать, что данные в таблице являются выборкой из генеральной совокупности всех деревьев в саду
• БДДИСПП(DVARP) — Вычисляет дисперсию по генеральной совокупности из выделенных записей базы данных:
  
  =БДДИСПП(A6:E12;4;A1:A2)
  =БДДИСПП(A6:E12;»Урожайность»;A1:A2)
  =DVARP(A6:E12,4,A1:A2)вернет 10,66667, т.е. точную дисперсию урожайности Яблонь и Вишень, если считать, что данные в базе данных описывают генеральную совокупность всех деревьев в саду

В excel работа с базой данных

База данных в Excel: особенности создания, примеры и рекомендации

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

Что такое база данных?

​ регионам, клиентам или​ регион (адрес, ИНН,​ Если хотим спрятать​ данных структурирует информацию​ горячих клавиш CTRL+SHIFT+=.​ строке будет самый​ формирования простых баз​ Access, Excel оптимизирована​ на листе для​ профессионально регулярно диаграммы,​ Хранения адресов отдельно​ДСТАНДОТКЛ​ постоянно придется тратить​ в таком состоянии,​ желание пользователя. Это​ базу данных собранных​ совмещающим в себе​ категориям. В старых​ банковские реквизиты и​ какие-то элементы, сбрасываем​ в строки и​Далее скопируйте все заголовки​ дешевый продукт, в​ данных и удобной​ для вычисления и​ предотвращения случайного изменения​ следует использовать отчетов​

​ от имен имеет​Оценивает стандартное отклонение по​ время на пролистывание​ как она представлена​ можно сделать несколькими​ средств с родителей​

Создание хранилища данных в Excel

​ большинство полезных и​ версиях Excel для​ т.п.) каждого из​ птички напротив их.​ столбцы. Несмотря на​ столбцов базы данных​ последней – самый​ навигации по ним.​ анализ данных. Гибкие​ важных данных другими​ сводной таблицы или​

​ смысл, так как​ выборке из выделенных​ листа, чтобы посмотреть,​ сейчас, но многие​ способами:​ в фонд школы.​ нужных пользователям функций.​ этого потребовалось бы​ них.​Жмем «ОК». В примере​ то что стандартный​

​ и вставьте их​ дорогой. Выделяем столбец​Как сделать базу данных​ программу можно использовать​ пользователями.​ сводной таблице диаграмм​ каждый клиент может​ записей базы данных.​ какой строке или​ возможности при этом​• Можно выделить всю​

Особенности формата ячеек

​ Размер суммы не​ К ним относятся​ использовать несколько функций​Таблица​ мы скроем клиентов,​ пакет MS Office​ в первую строку​ с ценой и​ в Excel, чтобы​ для построения модели​Защита на уровне файла​ в Excel вместо​ иметь несколько адресов,​ДСТАНДОТКЛП​ столбцу соответствует определенное​ будут урезаны. Ниже​ информацию, содержащуюся на​ ограничен и индивидуален​ графики, таблицы, диаграммы,​ВПР (VLOOKUP)​Продажи​ с которыми заключали​ имеет отдельное приложение​ листа для вспомогательной​ на вкладке ГЛАВНАЯ​ не было удобно​

​ для анализа данных,​    На уровне файла можно​ похожих функций в​ и вы хотите​Вычисляет стандартное отклонение по​ значение.​ мы разберем, как​ листе Excel, скопировать​ для каждого человека.​ ведение учета, составление​для подстановки цен,​будет использоваться нами​ договоры в прошлом​ для создания и​ таблицы критериев.​ выбираем СОРТИРОВКА И​ не только хранить,​ писать простые и​ использовать шифрование в​ Access.​

Что такое автоформа в «Эксель» и зачем она требуется?

​ иметь возможность ввести​ генеральной совокупности из​Выделяем ячейку, области сверху​ сделать БД более​ ее и перенести​ Пусть в классе​ расчетов, вычисления различных​ категорий, клиентов, городов​ впоследствии для занесения​ и текущем году.​ ведения баз данных​Пространство для заполнения критериев​ ФИЛЬТР.​ но и обрабатывать​ сложные формулы для​ обеих программах можно​Access и Excel можно​ несколько адресов для​

Фиксация «шапки» базы данных

​ выделенных записей базы​ и слева от​ функциональной.​ в другую программу.​ учится 25 детей,​ функций и так​ и т.д. в​ в нее совершенных​Чтобы задать условие для​ – Microsoft Access,​ запросов выше данных​Т.к. мы решили, что​ данные: формировать отчеты,​ выполнения вычислений с​ предотвратить несанкционированное могли​ использовать в среде​ каждого клиента без​ данных.​ которой нужно закрепить.​Работа с базами данных,​ Для этого выделите​ значит, и родителей​

​ далее. Из этой​ таблицу​ сделок.​ фильтрации поля типа​ пользователи активно используют​ базы.​

Продолжение работы над проектом

​ сверху будет меньшая​ строить графики, диаграммы​ этими данными любым​ видеть данные. Можно​ совместной работы, таких​ необходимости повторного ввода​БДСУММ​

​ Она будет располагаться​ прежде всего, предусматривает​ данные, предназначенные для​ будет соответствующее количество.​ статьи мы узнаем,​Продажи​Само-собой, можно вводить данные​ «больше», «меньше», «равно»​ Microsoft Excel для​Сначала попытаемся получить номер​ цена, выбираем ОТ​ и т.д.​ способом, который вы​ также указать, что​ как Windows SharePoint​ имя клиента для​Суммирует числа в поле​ сразу под шапкой​ возможность упорядочивания, отбора​ копирования, и щелкните​

Как создать раскрывающиеся списки?

​ Чтобы не нагромождать​ как создать базу​. Это требует времени​ о продажах непосредственно​ и т.п. числа,​ этих же целей.​ фактуры по фамилии​ МИНИМАЛЬНОГО К МАКСИМАЛЬНОМУ.​Для начала научимся создавать​ хотите данных сводной​ пароль вводиться для​ Services и общие​

​ каждого адреса.​ для записей базы​ и справа от​ и сортировки записей.​ правой кнопкой мышки.​ базу данных большим​ данных в Excel,​ и сил от​ в зеленую таблицу​ в списке фильтра​ Ведь возможности программы​ клиента:​ Появится еще одно​ БД с помощью​ таблицы и представления​ открытия файла базы​ папки, но существует​Локальные сравнении внешних данных​ данных, удовлетворяющих условию.​ наименований записей.​ Подключим эти функции​ В контекстном меню​ числом записей, стоит​ для чего она​ нас, а также​Продажи​

Диапазон значений в Excel

​ нужно выбрать команду​ позволяют: сортировать; форматировать;​В ячейке D2 введите​ окно, где в​ инструментов Excel. Пусть​ данных в различных​ данных или в​ ряд различий в​    Access можно использовать для​БДДИСП​Находясь во вкладке​ к нашей БД.​

​ нажимайте «Копировать». Затем​ сделать раскрывающиеся списки,​ нужна, и какие​ «кушает» немало ресурсов​, но это не​ «Числовые фильтры».​ фильтровать; редактировать; систематизировать​ фамилию Антонова.​ качестве предполагаемого действия​ мы – магазин.​ профессиональ

Работа в Excel с базами данных (списками)

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

• Организация встроенных, расположенных непосредственно на рабочем листе, баз данных с помощью списков

• Работа с внешними данными с помощью специальных запросов

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

Чтобы достичь максимальной эффективности при работе со списками, необходимо следовать следующим правилам:

• Каждый столбец должен содержать информацию одного типа

• Одна или две верхние строки списка должны содержать уникальные заголовки, каждый из которых описывает содержимое расположенного ниже столбца

• Список не должен включать пустые строки и столбцы

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

• Не следует размещать данные слева и справа от списка, поскольку они могут быть скрыты в процессе фильтрации списка

Над списками можно производить следующие действия:

• Сортировать список

• Добавлять или изменять данные с помощью формы

• Фильтровать список таким образом, чтобы были выделены только те строки, которые удовлетворяют заданному критерию

• Автоматически вычислять промежуточные итоги

• Создавать для списка структуру

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

• Проверять вводимые в ячейку данные

Все основные возможности работы со списками включены в пункт меню Данные.

Сортировка списков и диапазонов

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

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

Фильтрация списков Анализ табличных данных с помощью фильтров.

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

Применение Автофильтра

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

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

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

Работа в Excel с базами данных (списками)

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

• Организация встроенных, расположенных непосредственно на рабочем листе, баз данных с помощью списков

• Работа с внешними данными с помощью специальных запросов

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

Чтобы достичь максимальной эффективности при работе со списками, необходимо следовать следующим правилам:

• Каждый столбец должен содержать информацию одного типа

• Одна или две верхние строки списка должны содержать уникальные заголовки, каждый из которых описывает содержимое расположенного ниже столбца

• Список не должен включать пустые строки и столбцы

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

• Не следует размещать данные слева и справа от списка, поскольку они могут быть скрыты в процессе фильтрации списка

Над списками можно производить следующие действия:

• Сортировать список

• Добавлять или изменять данные с помощью формы

• Фильтровать список таким образом, чтобы были выделены только те строки, которые удовлетворяют заданному критерию

• Автоматически вычислять промежуточные итоги

• Создавать для списка структуру

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

• Проверять вводимые в ячейку данные

Все основные возможности работы со списками включены в пункт меню Данные.

Сортировка списков и диапазонов

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

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

Фильтрация списков Анализ табличных данных с помощью фильтров.

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

Применение Автофильтра

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

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

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

Работа в excel как с базой данных

26.1. Сортировка и фильтрация данных

    Электронная таблица “Excel” позволяет работать в режиме базы данных. Конечно, ее возможности уступают специализированным базам данных, однако эти возможности достаточно большие Мы с Вами посмотрим, как осуществлять поиск информации в базе данных по различным критериям и производить сортировку информации. Для начала заполним таблицу, содержащую информацию о планетах Солнечной системы  и сохраним ее под именем Ex6.xls.     Единицы измерения, используемые в таблице:         — период обращения по орбите — в земных годах;         — среднее расстояние от Солнца — в млн. км;         — экваториальный диаметр — в тыс. км;         — масса — в кг.

26.1.1. Основные понятия баз данных

    Область таблицы A2:FI2 можно рассматривать как базу данных. Столбцы А, В, С, D, Е, F этой таблицы называются полями, а строки с 3 по 12 — записями. Область A2:F2 содержит имена полей. Существуют ограничения, накладываемые на структуру базы данных:         — первый ряд базы данных должен содержать неповторяющиеся имена полей;         — остальные ряды базы данных должны содержать записи, которые не являются пустыми рядами;         — информация по полям (столбцам) должна быть однородной, т. е. только числа или только текст.     Основная работа с любой базой данных заключается в поиске информации по определенным критериям. С увеличением количества записей поиск информации затрудняется. Excel позволяет упростить этот процесс путем фильтрации данных.     Фильтрация данных. Команда Данные=Фильтр позволяет выделять (фильтровать) нужные записи. Фильтрация возможна как через автоматический фильтр — Автофильтр, так и через ручной — Усиленный.     Автофильтр. При использовании Автофильтра необходимо переместить курсор в область, содержащую базу данных, и выделить ее. Затем нужно выполнить команды Данные=Фильтр=Автофилътр. На именах полей появятся кнопки с изображением стрелок вниз. Нажимая на кнопки, можно задавать критерии фильтрации. В появляющемся подменю пункт Все отключает фильтрацию, а пункт Настройка вызывает диалоговое окно, в котором можно установить параметры фильтрации. Для одного поля могут быть заданы два условия одновременно, связанные логическим И или ИЛИ. С использованием Автофильтра можно, в нашем примере, осуществить поиск планет, начинающихся на букву С или букву Ю с массой менее 600-1024 кг. Для этого выполним команды Данные=Фильтр:=Автофильтр. На полях появились кнопки. Нажмем на кнопку в поле Планета. Выберите пункт УсловиеНастройка. В диалоговом окне надо задать критерии согласно выбранным нами критериям и нажать на кнопку ОК. В базе данных остались только планеты: Солнце, Юпитер, Сатурн. Если мы нажмем на кнопку а поле Масса, выберем пункт Условие=Настройка и в диалоговом окне зададим критерий: Масса<600, то в результате у нас останется только Сатурн. Для просмотра исходной базы, надо выполнить команды меню Данные=Фильтр=Показать все.  

Рабочая книга excel. Связь таблиц

27.1. Листы рабочей книги

    До сих пор вы работали только с одним листом рабочей книги. Часто бывает полезно использовать несколько рабочих листов. В нижней части экрана видны ярлычки листов. Если щелкнуть на ярлычке левой клавишей мыши, то указанный лист становится активным и перемещается наверх. Щелчок правой кнопкой на ярлычке вызовет меню для таких действий с листом, как перемещение, удаление, переименование и т.д. В левом нижнем углу окна рабочей книги находятся кнопки прокрутки ярлычков, с помощью которых можно переходить от одного рабочего листа к другому. Щелкнув правой кнопкой мыши на кнопках прокрутки ярлычков, можно открыть контекстно-зависимое меню для выбора нужного рабочего листа. Попробуем создать массный журнал. Для простоты ограничимся тремя предметами: литературой, алгеброй, геометрией. Для каждого предмета отводится отдельный лист рабочей книги, который содержит список класса (ограничимся пятью учениками), текущие оценки и итоговую оценку за 1-ю четверть. На отдельном листе должна быть представлена ведомость итоговых оценок за 1-ю четверть, заполненная оценками с использованием ссылок на соответствующие листы по предметам.

Управление данными с помощью Access или Excel

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

Microsoft Access и Microsoft Excel обладают сходства, которые могут затруднить решить, какое приложение следует использовать. Например обе программы можно хранить большие объемы данных запустить мощные средства запросов и инструменты анализа срез и поперечные срезы, данные и выполнение сложных вычислений, которые возвращают данные, которые необходимо.

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

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

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

Примечание: Включить все наборы Microsoft Office Excel, но не все наборы Access.

Сравнение преимущества каждой программы

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

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

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

Локальные сравнении внешних данных    Access можно использовать для подключения к данным из разнообразных внешних источников данных, так что можно просмотреть запрос и изменять данные без необходимости импортировать его. Например в Access предусмотрена команды для подключения к существующие данные в базе данных Microsoft SQL Server, dBASE файла или папки Outlook, а также других источников данных. Excel можно использовать для подключения к различным источникам данных, включая Access, SQL Server и баз данных служб Analysis Services, текста и XML-файлы и источников данных ODBC и OLE DB. Тем не менее не может изменять данные, чтобы изменить исходные данные через интерфейс пользователя Microsoft Excel.

Access и Excel предоставляют команды для подключения к данным в списках Windows SharePoint Services. Тем не менее, в программе Excel существует только только для чтения подключения к спискам SharePoint; в то время как Access позволяет чтения и записи данных со списками SharePoint.

Целостность данных и гибкости    Уникальные идентификаторы сохраняют целостность данных и их убедитесь, что нет две строки (или записей) содержат именно те же данные. Уникальные идентификаторы также предоставляют самый быстрый способ получения данных при поиска или сортировки данных. В приложении Access чтобы автоматически создать уникальный идентификатор для каждой записи можно использовать тип данных «Счетчик». Затем можно использовать эти идентификаторы для записей из одной таблицы связаны с одной или нескольких записей в другой таблице.

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

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

Выполнение запросов    Если часто приходится просматривать данные различными способами, в зависимости от того, изменяя условия или события, Access может быть лучший выбор для хранения и работе с данными. Access позволяет быстро восстановить только строк и столбцов данных, который вы хотите ли данные содержатся в таблице одной или несколькими таблицами с помощью запросов язык SQL (Structured Query). Можно также использовать выражения в запросах создавать вычисляемые поля. Использование выражений в Access аналогична процесс с помощью формул в Excel для вычисления значений. Запросы Access также можно использовать для подведения итогов данных и представлять статистических значений, таких как суммы, средние значения и счетчики.

Моделирование    В Excel можно использовать средства анализ «что если» для прогнозирования результат модель листа. Анализ «что если» позволяет выполнить различных сценариях для своих данных, например Лучший случай и худшим сценариев и сравнение результирующие данные из нескольких сценариев в сводный отчет. Не так же, функция доступна в Access.

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

Access и Excel можно использовать в среде совместной работы, таких как Windows SharePoint Services и общие папки, но существует ряд различий в нужным данных может осуществляться несколькими пользователями.

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

С помощью служб Windows SharePoint Services для совместной работы    Интегрируйте обе программы с помощью технологий Microsoft Windows SharePoint Services, например списки и библиотеки документов SharePoint.

Access предоставляет широкий спектр способов совместной работы с несколькими пользователями на сайте SharePoint. Например, можно отправить всей базы данных в библиотеке документов Windows SharePoint Services, внести формы и отчеты, доступные в виде представлений служб Windows SharePoint Services и связывание базы данных с данными, которые хранятся в списках SharePoint.

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

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

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

Защита от потери данных    В приложении Access работы постоянно сохранить, чтобы в случае сбоя вряд ли потеряете оставшейся работы (если таковые имеются). Тем не менее, так как Access постоянно сохраняет вашу работу, можно также можно внести изменения, которые вы позже решите, что вы не хотите зафиксировать. Чтобы убедиться, что нужным образом можно восстановить базу данных, следует создавать резервную копию файла базы данных по расписанию, который соответствует требованиям. Всю базу данных можно восстановить из резервной копии или вы можете восстановить только таблицу или другого объекта базы данных, которая может потребоваться. При использовании средства архивации файловой системы копию базы данных из резервной копии файловой системы также можно использовать для восстановления данных. При обновлении данных в Excel, можно сохранить Автосохранение через заданные промежутки времени.

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

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

Ограниченный доступ к данным    В Excel можно указать на основе пользователя разрешения на доступ к данным или задать права только для чтения, другие пользователи могли вносить изменения в данные, которые они имеют доступ к. Access не предоставляет возможности безопасности на уровне пользователя, но Access поддерживает модель безопасности пользователя любого сервера базы данных, который подключается к. Например при связывании со списком SharePoint доступа heeds разрешениями пользователей для списка SharePoint. Если вы хотите оставить несанкционированное доступа к данным, можно зашифровать базу данных с помощью паролей. Пользователям необходимо ввести пароль на чтение данных из базы данных, даже если они доступ к нему с помощью другой программы, такой как Microsoft Excel.

Дополнительные сведения о том, как защитить данные просмотреть Задание или изменение защиты на уровне пользователя Access 2003 в Access 2007 или более поздней версии и защиты и безопасности в Excel.

Условия использования программы Microsoft Access

В очень общие термины Access лучше всего подходит при регулярно, имеют для отслеживания и записи данных и затем отобразить, экспорт и печать подмножества данных. Формы Microsoft Access обеспечивают более удобный интерфейс чем лист Excel для работы с данными. Access можно использовать для автоматизации часто выполняемых действий и отчетов Access позволяют сведение данных в печати или в электронной форме. Microsoft Access предоставляет дополнительные структуры для ваших данных; Например, можно задать типы данных, которые могут быть введены, какие значения могут быть добавлены и вы можете указать, как связанных данных в одной таблице данных из других таблиц. Эта структура поможет вам убедиться, что введены только нужные типы данных.

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

Программу Microsoft Access следует использовать при следующих условиях.

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

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

• Выполняются сложные запросы.

• Хотите создавать разнообразные отчеты или почтовых наклеек.

• Управление контактами    Управление контактами и почтовые адреса и затем создания отчетов в Access или объединить данные с Microsoft Office Word для печати писем, конверты или наклейки.

• Запасы и основные фонды    Создание учета запасов для дома или офиса и сохраняйте фотографии или другие связанные документы вместе с данными.

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

• Отслеживание задач    Отслеживание задач для совместной работы пользователей и введите новые задачи одновременно с другими пользователями обновляете их существующей задачи в той же базы данных.

• Организация библиотек функций    Access можно использовать для хранения данных о книг и компакт-дисков и отслеживайте известных кого аренду их.

• Планирование событий    Введите сведения о событии дат, расположения и участников и печать расписаний или сводные данные о событиях.

• Отслеживание питания    Рецептами и войдите журнал режима питания и Испытайте действий.

Условия использования программы Microsoft Excel

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

Программу Microsoft Excel следует использовать при следующих условиях.

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

• Часто выполнять вычисления и статистические сравнения данных.

• Хотите использовать отчеты сводных таблиц для просмотра иерархических данных в компактном и гибком представлении.

• Планирование регулярно создавать диаграммы и хотите использовать новые форматы построения диаграмм, доступных в Excel.

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

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

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

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

• Составление бюджета    Ли вашим потребностям личных или деловых связаны, можно создать бюджета в Excel — например, маркетингового плана бюджета, Бюджет мероприятия или пенсионных отчислений.

• Выставление счетов «и» продажи    Excel также может оказаться полезным для управления выставления счетов и данных о продажах и можно без труда создавать формы, которые требуется — пример счета продажи отборочные или заказов на покупку.

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

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

• Отслеживание    Excel можно использовать для отслеживания данных в списке или табелей — например, табелей для отслеживания трудозатрат или инвентарный список, который отслеживает оборудования.

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

Полезные шаблоны Excel в любой из этих категорий читайте в статье шаблоны для Excel на сайте Microsoft Office Online.

С помощью Access и Excel вместе

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

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

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

Функции базы данных (справка) — Служба поддержки Office

Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Дополнительные сведения об этих различиях.

Чтобы просмотреть подробную справку о функции, перейдите по нужной ссылке в представленном ниже списке.

См. также

Функции Excel (по категориям)

Функции Excel (по алфавиту)