Создание базы в эксель: Создание базы данных в Excel

Создание базы данных в Excel

При упоминании баз данных (БД) первым делом, конечно, в голову приходят всякие умные слова типа SQL, Oracle, 1С или хотя бы Access. Безусловно, это очень мощные (и недешевые в большинстве своем) программы, способные автоматизировать работу большой и сложной компании с кучей данных. Беда в том, что иногда такая мощь просто не нужна. Ваш бизнес может быть небольшим и с относительно несложными бизнес-процессами, но автоматизировать его тоже хочется. Причем именно для маленьких компаний это, зачастую, вопрос выживания.

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

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

Со всем этим вполне может справиться Microsoft Excel, если приложить немного усилий. Давайте попробуем это реализовать.

Шаг 1. Исходные данные в виде таблиц

Информацию о товарах, продажах и клиентах будем хранить в трех таблицах (на одном листе или на разных — все равно). Принципиально важно, превратить их в «умные таблицы» с автоподстройкой размеров, чтобы не думать об этом в будущем. Это делается с помощью команды

Форматировать как таблицу на вкладке Главная (Home — Format as Table). На появившейся затем вкладке Конструктор (Design) присвоим таблицам наглядные имена в поле Имя таблицы для последующего использования:

Итого у нас должны получиться три «умных таблицы»:

Обратите внимание, что таблицы могут содержать дополнительные уточняющие данные. Так, например, наш Прайс содержит дополнительно информацию о категории (товарной группе, упаковке, весу и т.п.) каждого товара, а таблица

Клиенты — город и регион (адрес, ИНН, банковские реквизиты и т.п.) каждого из них.

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

Шаг 2. Создаем форму для ввода данных

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

В ячейке B3 для получения обновляемой текущей даты-времени используем функцию

ТДАТА (NOW). Если время не нужно, то вместо ТДАТА можно применить функцию СЕГОДНЯ (TODAY).

В ячейке B11 найдем цену выбранного товара в третьем столбце умной таблицы Прайс с помощью функции ВПР (VLOOKUP). Если раньше с ней не сталкивались, то сначала почитайте и посмотрите видео тут.

В ячейке B7 нам нужен выпадающий список с товарами из прайс-листа. Для этого можно использовать команду Данные — Проверка данных (Data — Validation), указать в качестве ограничения

Список (List) и ввести затем в поле Источник (Source) ссылку на столбец Наименование из нашей умной таблицы Прайс:

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

=ДВССЫЛ(«Клиенты[Клиент]»)

Функция ДВССЫЛ (INDIRECT) нужна, в данном случае, потому что Excel, к сожалению, не понимает прямых ссылок на умные таблицы в поле Источник. Но та же ссылка «завернутая» в функцию ДВССЫЛ работает при этом «на ура» (подробнее об этом было в статье про создание выпадающих списков с наполнением).

Шаг 3. Добавляем макрос ввода продаж

После заполнения формы нужно введенные в нее данные добавить в конец таблицы

Продажи. Сформируем при помощи простых ссылок строку для добавления прямо под формой:

Т.е. в ячейке A20 будет ссылка =B3, в ячейке B20 ссылка на =B7 и т.д.

Теперь добавим элементарный макрос в 2 строчки, который копирует созданную строку и добавляет ее к таблице Продажи. Для этого жмем сочетание Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer). Если эту вкладку не видно, то включите ее сначала в настройках Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon)

. В открывшемся окне редактора Visual Basic вставляем новый пустой модуль через меню Insert — Module и вводим туда код нашего макроса:

Sub Add_Sell()
    Worksheets("Форма ввода").Range("A20:E20").Copy                         'копируем строчку с данными из формы
    n = Worksheets("Продажи").Range("A100000").End(xlUp).Row                'определяем номер последней строки в табл. Продажи
    Worksheets("Продажи"). Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'вставляем в следующую пустую строку
    Worksheets("Форма ввода").Range("B5,B7,B9").ClearContents               'очищаем форму
End Sub

Теперь можно добавить к нашей форме кнопку для запуска созданного макроса, используя выпадающий список Вставить на вкладке Разработчик (Developer — Insert — Button):

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

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

Продажи, а затем форма очищается для ввода новой сделки.

Шаг 4. Связываем таблицы

Перед построением отчета свяжем наши таблицы между собой, чтобы потом можно было оперативно вычислять продажи по регионам, клиентам или категориям. В старых версиях Excel для этого потребовалось бы использовать несколько функций ВПР (VLOOKUP) для подстановки цен, категорий, клиентов, городов и т.д. в таблицу Продажи. Это требует времени и сил от нас, а также «кушает» немало ресурсов Excel. Начиная с Excel 2013 все можно реализовать существенно проще, просто настроив связи между таблицами.

Для этого на вкладке Данные (Data) нажмите кнопку Отношения (Relations). В появившемся окне нажмите кнопку Создать (New) и выберите из выпадающих списков таблицы и названия столбцов, по которым они должны быть связаны:

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

Само-собой, аналогичным образом связываются и таблица Продажи с таблицей Клиенты по общему столбцу Клиент:

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

Шаг 5. Строим отчеты с помощью сводной

Теперь для анализа продаж и отслеживания динамики процесса, сформируем для примера какой-нибудь отчет с помощью сводной таблицы. Установите активную ячейку в таблицу Продажи и выберите на ленте вкладку Вставка — Сводная таблица (Insert — Pivot Table). В открывшемся окне Excel спросит нас про источник данных (т.е. таблицу

Продажи) и место для выгрузки отчета (лучше на новый лист):

Жизненно важный момент состоит в том, что нужно обязательно включить флажок Добавить эти данные в модель данных (Add data to Data Model) в нижней части окна, чтобы Excel понял, что мы хотим строить отчет не только по текущей таблице, но и задействовать все связи.

После нажатия на ОК в правой половине окна появится панель Поля сводной таблицы, где нужно щелкнуть по ссылке

Все, чтобы увидеть не только текущую, а сразу все «умные таблицы», которые есть в книге.А затем можно, как и в классической сводной таблице, просто перетащить мышью нужные нам поля из любых связанных таблиц в области Фильтра, Строк, Столбцов или Значений — и Excel моментально построит любой нужный нам отчет на листе:

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

Также, выделив любую ячейку в сводной и нажав кнопку Сводная диаграмма (Pivot Chart) на вкладке Анализ (Analysis) или Параметры (Options) можно быстро визуализировать посчитанные в ней результаты.

Шаг 6.

Заполняем печатные формы

Еще одной типовой задачей любой БД является автоматическое заполнение различных печатных бланков и форм (накладные, счета, акты и т.п.). Про один из способов это сделать, я уже как-то писал. Здесь же реализуем, для примера, заполнение формы по номеру счета:

Предполагается, что в ячейку C2 пользователь будет вводить число (номер строки в таблице Продажи, по сути), а затем нужные нам данные подтягиваются с помощью уже знакомой функции ВПР (VLOOKUP) и функции ИНДЕКС (INDEX).

Ссылки по теме

Создание базы данных в Excel

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

К сожалению (а может и к счастью), Excel в первую очередь является электронной таблицей, поэтому чтобы создать базу данных в этой программе, требуется выстраивать связи с помощью формул подстановки, создавать интерфейс на VBA и добавлять дашборды. Все это было до недавнего времени. С приходом Excel 2013, программа обзавелась новыми инструментами работы с таблицами, позволяющими связывать диаграммы и ячейки, выполнять поиск и создавать динамически обновляемые отчеты. Все верно!!! Как в реляционных базах данных. Excel может ежедневно обрабатывать большие количество данных. Каким образом? Читайте дальше.

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

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

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

Одной записи основной таблицы может соответствовать несколько записей из дополнительной (или дочерней) таблицы. Эта связь называется один-ко-многим. Информация в дочерней таблице – такая как, ежедневные продажи, цена на продукт, количество – обычно периодически изменяется.

Чтобы избежать повторения всей информации из основной таблицы в дополнительной таблице, необходимо создать отношения, используя уникальное поле, такое как ID Продаж, и позволить Excel сделать все остальное. К примеру, у вас имеется 10 продавцов со своей уникальной информацией (основная таблица). Каждый продавец имеет 200 продуктов, которые он продает (дополнительная таблица). В конце года вам необходимо создать отчет, который отображает результаты продаж каждого сотрудника. Плюс к этому, вам необходимо создать отчет, который отображает результаты продаж по городам.

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

Создание основной и дополнительной таблиц

Наша основная таблица будет содержать 4 поля: ID Продаж, Имя продавца, Адрес и Город. Создайте таблицу аналогично изображению на рисунке. Данные для таблицы можно взять из файла прикрепленном в конце статьи.

Моя таблица имеет двадцать продавцов из 3-х различных городов, по которым в дальнейшем мы будем делать отчет.

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

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

Осталось дать имя нашей таблице. Выберите любую ячейку в таблице, перейдите по вкладке Работа с таблицами -> Конструктор в группу Свойства. В поле Имя таблицы поменяйте название таблицы на Основной.

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

Также таблице необходимо дать более осмысленное название, например, Продажи.

Определение отношений между таблицами

Первое правило сводных таблиц: отношения между таблицами определяются в системе отчетов сводной таблицы, с использованием инструмента Отношения. Не пытайтесь с самого начала определить связи между таблицами – инструмент формирования отчетов сводных таблиц все равно их не воспримет.

Выделяем таблицу с продажами, во вкладке Вставка переходим в группу Таблицы, щелкаем Сводная таблица.

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

Excel создаст новый лист с пустой сводной таблицей. В левой части экрана появится панель Поля сводной таблицы. Чтобы свести данные обоих таблиц, в панели Поля сводной таблицы вкладки Активная проставьте галочки напротив пунктов Квартал 1, Квартал 2, Квартал 3 и Квартал 4. Excel построит сводную таблицу с данными по кварталам, пока не обращайте на нее внимание. Далее в этой же панели переходим на вкладку Все, где вы увидите обе наши таблицы. Ставим галочку напротив поля Город, таблицы Основной. Появится желтое поле с уведомлением Могут потребоваться связи между таблицами, щелкаем кнопку Создать.

В появившемся диалоговом окне Создание связи необходимо определить отношения между таблицами. Выберите из выпадающего списка Таблица пункт Продажи, а из выпадающего списка Столбец (Чужой) пункт ID Продаж. Помните, что ID Продаж единственное поле, которое находится в обоих таблицах. Из списка Связанная таблица выбираем Основной, из списка Связанный столбец (первичный ключ) — пункт ID Продаж. Жмем ОК.

Excel создаст связи и отобразит результирующий отчет на экране. Дайте имя вашему отчету, и он будет готов.

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

Вам также могут быть интересны следующие статьи

Создание связи между двумя таблицами в Excel

Вы применяли функцию ВПР, чтобы переместить данные столбца из одной таблицы в другой? Так как в Excel теперь есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами на основе совпадающих данных в них. Затем можно создать листы Power View или сводные таблицы и другие отчеты с полями из каждой таблицы, даже если они получены из различных источников. Например, если у вас есть данные о продажах клиентам, вам может потребоваться импортировать и связать данные логики операций со временем, чтобы проанализировать тенденции продаж по годам и месяцам.

Все таблицы в книге указываются в списках полей сводной таблицы и Power View.

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

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

2. Одно из следующих условий: форматирование данных в виде таблицы или импорт внешних данных в виде таблицы на новом таблице.

3. Присвойте каждой из таблиц понятное имя: На вкладке Работа с таблицами щелкните Конструктор > Имя таблицы и введите имя.

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

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

5. Щелкните Данные> Отношения.

Если команда Отношения недоступна, значит книга содержит только одну таблицу.

1. В окне Управление связями нажмите кнопку Создать.

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

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

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

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

6. Нажмите кнопку ОК.

Дополнительные сведения о связях между таблицами в Excel

Примечания о связях

Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании

«Могут потребоваться связи между таблицами»

Шаг 1. Определите, какие таблицы указать в связи

Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблицы к другой

Примечания о связях

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

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

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

• Типы данных в двух столбцах должны быть совместимы. Подробные сведения см. в статье Типы данных в моделях данных.

• Другие способы создания связей могут оказаться более понятными, особенно если неизвестно, какие столбцы использовать. Дополнительные сведения см. в статье Создание связи в представлении диаграммы в Power Pivot.

Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании

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

1. Запустите надстройку Power Pivot в Microsoft Excel и откройте окно Power Pivot.

2. Нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace. В мастере импорта таблиц откроется домашняя страница Microsoft Azure Marketplace.

3. В разделе Price (Цена) нажмите Free (Бесплатно).

4. В разделе Category (Категория) нажмите Science & Statistics (Наука и статистика).

5. Найдите DateStream и нажмите кнопку Subscribe (Подписаться).

6. Введите свои учетные данные Майкрософт и нажмите Sign in (Вход). Откроется окно предварительного просмотра данных.

7. Прокрутите вниз и нажмите Select Query (Запрос на выборку).

8. Нажмите кнопку Далее.

9. Чтобы импортировать данные, выберите BasicCalendarUS и нажмите Готово. При быстром подключении к Интернету импорт займет около минуты. После выполнения вы увидите отчет о состоянии перемещения 73 414 строк. Нажмите Закрыть.

10. Чтобы импортировать второй набор данных, нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace.

11. В разделе Type (Тип) нажмите Data Данные).

12. В разделе Price (Цена) нажмите Free (Бесплатно).

13. Найдите US Air Carrier Flight Delays и нажмите Select (Выбрать).

14. Прокрутите вниз и нажмите Select Query (Запрос на выборку).

15. Нажмите кнопку Далее.

16. Нажмите Готово для импорта данных. При быстром подключении к Интернету импорт займет около 15 минут. После выполнения вы увидите отчет о состоянии перемещения 2 427 284 строк. Нажмите Закрыть. Теперь у вас есть две таблицы в модели данных. Чтобы связать их, нужны совместимые столбцы в каждой таблице.

17. Убедитесь, что значения в столбце DateKey в таблице BasicCalendarUS указаны в формате 01.01.2012 00:00:00. В таблице On_Time_Performance также есть столбец даты и времени FlightDate, значения которого указаны в том же формате: 01.01.2012 00:00:00. Два столбца содержат совпадающие данные одинакового типа и по крайней мере один из столбцов (DateKey) содержит только уникальные значения. В следующих действиях вы будете использовать эти столбцы, чтобы связать таблицы.

18. В окне Power Pivot нажмите Сводная таблица, чтобы создать сводную таблицу на новом или существующем листе.

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

20. Разверните таблицу BasicCalendarUS и нажмите MonthInCalendar, чтобы добавить его в область строк.

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

22. В списке полей, в разделе «Могут потребоваться связи между таблицами» нажмите Создать.

23. В поле «Связанная таблица» выберите On_Time_Performance, а в поле «Связанный столбец (первичный ключ)» — FlightDate.

24. В поле «Таблица» выберитеBasicCalendarUS, а в поле «Столбец (чужой)» — DateKey. Нажмите ОК для создания связи.

25. Обратите внимание, что время задержки в настоящее время отличается для каждого месяца.

26. В таблице BasicCalendarUS перетащите YearKey в область строк над пунктом MonthInCalendar.

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

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

1. Таблица BasicCalendarUS должна быть открыта в окне Power Pivot.

2. В главной таблице нажмите Сортировка по столбцу.

3. В поле «Сортировать» выберите MonthInCalendar.

4. В поле «По» выберите MonthOfYear.

Сводная таблица теперь сортирует каждую комбинацию «месяц и год» (октябрь 2011, ноябрь 2011) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, потому что канал DateStream предоставляет все необходимые столбцы для работы этого сценария. Если вы используете другую таблицу логики операций со временем, ваши действия будут другими.

«Могут потребоваться связи между таблицами»

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

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

Шаг 1.

Определите, какие таблицы указать в связи

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

Примечание:  Можно создавать неоднозначные связи, которые являются недопустимыми при использовании в сводной таблице или отчете Power View. Пусть все ваши таблицы связаны каким-то образом с другими таблицами в модели, но при попытке объединения полей из разных таблиц вы получите сообщение «Могут потребоваться связи между таблицами». Наиболее вероятной причиной является то, что вы столкнулись со связью «многие ко многим». Если вы будете следовать цепочке связей между таблицами, которые подключаются к необходимым для вас таблицам, то вы, вероятно, обнаружите наличие двух или более связей «один ко многим» между таблицами. Не существует простого обходного пути, который бы работал в любой ситуации, но вы можете попробоватьсоздать вычисляемые столбцы, чтобы консолидировать столбцы, которые вы хотите использовать в одной таблице.

Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблице к другой

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

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

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

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

• Типы данных столбца подстановок и исходного столбца должны быть совместимы. Подробнее о типах данных см. в статье Типы данных в моделях данных.

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

К началу страницы

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

Сравнивая же sms-мониторинг с другими подобными ресурсами, скажу, что sms — самый доступный источник информации на данный момент, так как не требует наличия у родителей компьютера и не отнимает у них, таких занятых, драгоценное время на освоение просторов Интернета (поверьте, многие ссылаются на то, что у них нет времени, чтобы включить компьютер».

Excel как средство создания базы данных и автоматической обработки

результатов олимпиад, конкурсов, конференций

Бурданова Людмила Юрьевна ([email protected])

МОУ «Центр развития образования городского округа», г. Самара

Аннотация

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

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

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

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

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

В целях создания общей среды, стимулирующей творческий рост, развитие личностных качеств каждого ребенка, расширяется система олимпиад и конкурсов школьников, являющихся наиболее распространенными формами выявления и поддержки талантливых детей. В школьном этапе олимпиады участвуют ученики с 5-го класса, другие мероприятия проводятся для начальной школы. Таким образом, банк данных включает учеников 1-11-х классов и содержит не одну тысячу учащихся и данные о них. При работе с такими большими объемами информации остро встает вопрос о максимальной автоматизации процессов обработки различного типа данных. Эту проблему позволяет решить использование MS Excel в качестве хранения и обработки данных. Эта программа обладает многими возможностями, присущими современным программам для работы с базами данных. К тому же эта программа имеется во всех ОУ, нет необходимости разрабатывать какую-либо специальную оболочку, да и в дальнейшем списки в Excel можно использовать как в Access-, так и Net-школе.

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

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

Формирование базы данных талантливых детей начинается в ОУ, когда формируются списки участников. Из ОУ списки направляются в территориальные информационно-методические отделы (ТИМО) Центра развития образования (ЦРО), а затем в оргкомитет, где и составляются списки участников

городских мероприятий учебно-исследовательского характера. После проведения олимпиады, конкурса результаты заносятся в общий список и размещаются на сайте ЦРО. На основе списка победителей и призеров всех 29 мероприятий составляется банк данных одаренных и талантливых детей городского округа Самара. Такая система формирования банка данных требует единой формы заявок.

На примере олимпиады рассмотрим все этапы, от сбора заявок до подведения результатов и формирования базы данных на городском уровне:

1. Создание формы для ввода данных об участниках олимпиады.

2. Сбор заявок из ОУ.

3. Обработка результатов.

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

Этап создания формы заявок

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

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

— делить информацию на как можно большее количество столбцов (полей). Например, ФИО лучше делить на три отдельных столбца с фамилией, именем и отчеством;

— верхняя строка списка должна быть «шапкой», т. е. содержать заголовки столбцов. К сожалению, Excel в большинстве случаев не умеет корректно работать с многоуровневыми «шапками», поэтому в реальном списке необходимо ограничиться однострочной без пустых и объединенных ячеек;

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

Первый шаг при создании базы данных — это разработка хорошо продуманной структуры (организации хранения данных), которая и определяет возможности будущей обработки информации. Структура списка определяется структурой одинаково организованных записей, его составляющих. Под структурой записи принято понимать совокупность ее полей (их имена, типы, назначение). В ячейках списка чаще всего хранятся константы соответствующих основных типов: числовые, текстовые, даты и времени. Однотипность данных столбцов крайне важна, так как влияет на скорость обработки данных. Так, к примеру, чтобы поле Класс заполнялся всеми одинаково, без литеры параллели в форме заявки можно предусмотреть заполнение ячейки через открывающийся список. Или название предмета Французский язык можно написать несколькими способами: французский язык, фр. язык, Фр. Язык и т. д. Такое разнообразие в написании осложняет работу с фильтром, возможность заполнения через открывающийся список решает эту проблему. Учитывая, что данные вносятся в каждом ОУ, а их в Самаре более 170, очень важно максимально унифицировать форму заявок. Конечно, все эти требования можно описать к прилагаемой инструкции, но это потребует дополнительного времени для ее изучения, а возможность задавать форматы ячеек и использовать инструмент проверки данных позволяет этого избежать.

Рассмотрим структуру списка на примере данных об ученике.

№ п/п Имя поля Тип поля Комментарий для обеспечения унификации

1 2 3 4

1 № п/п Числовой Вычисляется по формуле: (первый порядковый номер+1), т. е. = А2+1, копируем на все остальные. Ссылки относительные. Неудобство: при удалении строки в низлежащих появляется ошибка #ССЫЛКА! Но это легко устранить копированием формулы ячейки выше и вставкой на все нижние

2 Фамилия Текстовый Строки состоят из букв кириллицы без ведущих и хвостовых пробелов, первый символ — прописная буква

Продолжение табл.

1 2 3 4

3 Имя Текстовый То же

4 Отчество Текстовый То же

5 Пол Текстовый Односимвольная строка: буквы «м» или «ж». При помощи Проверки данных в условия проверки вводят длину текста 1 и (или) в сообщении для ввода сообщение «введите «м» или «ж»»

6 Дата рождения Дата Формат ячейки Дата. Список

8 Кол-во баллов Числовой Используется Проверка данных Тип данных Действительное значение Между 0 и максимальным баллом

9 Тип диплома Текстовый Вычисляется по формуле: (если кол-во баллов больше или равно половине максимально возможных, то призер, иначе участник) = ЕСЛИ(07>=74;» призер»;»участник»). С помощью фильтра находим заданное число наибольших баллов в списке

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

Форму, созданную по вышеперечисленным правилам, заполняют ОУ и отправляют в Центр развития образования городского округа Самара.

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

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

Возможности Excel позволяют оптимизировать данные формы в зависимости от необходимости. Например, для регистрации необходимо составить только список участников с несколькими полями (класс, ОУ, учитель). Для этого все ненужные поля в копии, созданной для распечатки, просто удаляются, а для полей Фамилия, Имя, Отчество используется текстовая функция СЦЕПИТЬ, чтобы соединить в одну строку содержимое трех ячеек. Причем для ребенка эту функцию мы применяем для сцепления фамилии и имени, а для учителя, дополнительно используя функцию ЛЕВСИМВ, сцепляем только фамилию и инициалы.

Этап сбора заявок

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

1. Координатору ЦРО, координатор в ТИМО, ответственные за заполнение заявок в ОУ заводят аккаунт Google.

2. Главный координатор размещает форму заявки в документах Google и дает доступ координаторам в ТИМО.

3. В ТИМО делают копию формы, размещают ее в своих документах Google и приглашают пользователей из числа ответственных ОУ своего района.

4. Ответственные в ОУ начинают вносить данные своего ОУ в один общий документ, работая на сервисе Документы Google. Вносить данные при маленьких объемах можно на один лист одним

списком. При больших объемах целесообразно разделить ее по листам, например по предметам, если это олимпиада. Место внесения можно не соблюдать, все легко сортируется. В ТИМО могут отслеживать и корректировать процесс.

5. Координаторы ТИМО вносят данные по районам в форму от координатора ЦРО.

6. Координатор ЦРО имеет возможность отслеживать ход заполнения формы и делать замечания в случае необходимости.

Для координирования сроков выполнения заявок, дат проведения мероприятий удобно использовать Календарь Google. Это позволяет в случае изменения сроков не оповещать дополнительно ТИМО и ОУ.

Этап подведения итогов

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

1) число участников по предметам;

2) число участников по возрастным группам;

3) число мальчиков и девочек;

4) кол-во ОУ;

5) процент участников по предметам от общего количества;

6) процент по возрастным группам от общего числа участников;

7) достижения ОУ (кол-во победителей и призеров) и др.

Для составления аналитической справки эти количественные показатели могут использоваться в различных сочетаниях. Для подсчета динамики количества участников, процентов используются математические и логические функции. Фильтрация, или выборка — очень частая операция во время работы со списками. Суть ее в том, чтобы отобрать из списка все строки (записи), удовлетворяющие определенным условиям. Условий может быть много, они могут быть простыми и сложными, связанными друг с другом или независимыми. Наиболее удобен для фильтрации Расширенный фильтр, который практически не имеет ограничений на количество условий, налагаемых на список. Но он требует некоторых подготовительных операций:

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

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

Например, необходимо подсчитать, сколько учеников 9-11 классов гимназии № 1 стали призерами. Расширенный фильтр. Откроется окно Расширенного фильтра, в которое необходимо ввести адрес исходного диапазона, адрес диапазона условий и указать место, куда поместить результаты фильтрации.

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

Литература

Городская предметная олимпиада школьников 2008/2009 учебного года: Информационный бюллетень / А.А. Теп-лов. Самара: Издательство МГПУ, 2009. 96 с.

3.7.2. Создание базы данных в ms Excel

Основные термины

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

• Поле – столбец таблицы, элементами которого являются однотипные данные (реквизиты). Каждый столбец базы данных является полем. Поле данных обязательно должно иметь заголовок, имя.

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

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

• Диапазон – это диапазон ячеек, содержащий записи базы данных.

Основные правила создания списка

• На отдельном рабочем листе создавайте только одну базу данных.

• Не вставляйте в базу данных пустые строки. Если пустая строка вставлена между именами полей и данными, то Excel не определит имена полей. Пустые строки между записями воспринимаются Excel как конец БД.

• При вводе данных любого типа (текст, число и т.д.) не допускайте пробелов ни в начале, ни в любой другой позиции вводимых данных.

• Имена полей вводите в ячейки только одной (первой) строки базы данных. Имена, занимающие несколько строк, не распознаются Excel.

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

• Не используйте в качестве имен полей даты, формулы или пустые ячейки.

• Теоретически размер базы данных в Excel ограничен размерами листа (65536 строк и 256 столбцов). Но если вы хотите использовать для базы данных это пространство полностью, то лучше воспользуйтесь реляционными базами данных, например Access или Visual FoxPro, так как Excel не обработает такой объем информации.

Основные правила подготовки информации для создания базы данных

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

• Обдумайте содержание вашей базы данных, ее назначение, размеры, название, расположение на рабочем листе.

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

• Определите ширину поля для каждого элемента записи, число полей, количество записей.

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

• Предусмотрите возможность ввода новых записей, полей и элементов базы данных.

• Не вводите непонятные или не принятые сокращения слов.

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

В следующих упражнениях будем создавать базу данных Зарплата (рисунок 3.16) , а затем работать с ней.

Список в MS Excel создается как обычная электронная таблица, но при выполнении операций Данные, Excel автоматически воспринимает таблицу как базу данных.

Упражнение 57. Порядок ввода данных в список Зарплата

1. Д ля создания БД Зарплата введите сначала в ячейку В1 справочную информацию Тарифная ставка, а в ячейку С1 введите значение тарифной ставки, равное 1100 рублям. В дальнейшем при изменении тарифной ставки достаточно ввести новое значение в ячейку С1, чтобы все расчеты в таблице выполнились автоматически для нового значения тарифной ставки.

Рисунок 3.16. Начальный вариант базы данных Зарплата

2. Введите название таблицы (см. рисунок 3.16) в ячейку В3.

3. В ячейки В5:В14 (поле Ф.И.О.) введите фамилии с инициалами имен и отчеств работников (см. рисунок 3.16).

4. В ячейки А4:F4 введите имена полей базы данных (см. рисунок 3.16). Выделите имена полей жирным шрифтом, выровняйте по центру с переносом по словам.

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

Упражнение 58. Заполнение ячеек последовательными значениями

1. В ячейку А5 введите число 1.

2. Выделите ячейки А5:А14.

3. Выполните команды Правка/Заполнить/Прогрессия. Появится диалоговое окно Прогрессия.

4. В секции Расположение выберите по столбцам.

5. Выберите тип прогрессии Арифметическая прогрессия.

6. В поле Шаг: введите 1.

7. Щелкните ОК. Ячейки заполнятся числами от 1 до 10.

Если в ячейке БД находятся данные, состоящие из нескольких слов, разделенных пробелами (поле Ф.И.О. в БД Зарплата), то выполнение сортировки данных по фамилиям становится затруднительным. Поэтому для фамилий, имен, отчеств лучше предусмотреть отдельные поля в базе данных или разделить уже введенные данные на составные части.

Упражнение 59. Выделение составных частей имени

1. Вставьте столбец после столбца В.

2. Выделите поле Ф.И.О. в БД Зарплата. Выберите Данные/Текст по столбцам…. Появится диалоговое окно Мастер текстов – шаг 1.

3. Установите селектор с разделителями. Щелкните Далее.

4. Во втором окне мастера текстов выберите символом разделителем пробел. Щелкните Далее.

5. В третьем окне мастера текстов установите формат общий. Нажмите кнопку Готово.

6. В ячейку В4 введите новое имя поля Фамилия, в ячейку С4 введите имя поля И.О.

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

Упражнение 60. Проверка данных при вводе

1. Выделите ячейки D5:D14.

2. Выберите Данные/Проверка…. Появится диалоговое окно Проверка вводимых значений, которое содержит три вкладки.

3. На вкладке Параметры откройте выпадающий список Тип данных и выберите Целое число.

4. Откройте выпадающий список Значение и выберите между.

5. В поле Минимум наберите 1, в поле Максимум введите 25.

6. Щелкните на вкладке Сообщение для ввода.

7. В текстовом поле Заголовок: напечатайте Число дней. В текстовом поле Сообщение: напечатайте Вводите числа от 1 до 25.

8. Перейдите на вкладку Сообщение об ошибке. Установите переключатель Выводить сообщение об ошибке.

9. Откройте выпадающий список Вид. Выберите формат сообщения Останов. В поле Заголовок: напечатайте Дни. В поле Сообщение: наберите текст сообщения — Нажмите клавишу Повторить и введите правильное значение от 1 до 25.

10. Нажмите ОК. Диалоговое окно закроется.

11. Теперь вводите данные в поле Число дней. Введите в ячейку D5 число 240. С помощью проверки данных исправьте ошибку.

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

В следующих упражнениях заполним формулами вычисляемые поля базы данных Зарплата.

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

В поле Начислено нужно ввести формулу вычисления заработной платы =D5*$C$1. Заработная плата в данной задаче вычисляется умножением числа дней, отработанных за месяц (ячейки D5:D14), на величину тарифной ставки. Значение тарифной ставки содержится в ячейке С1. При копировании формулы в ячейки D5:D14 это значение, очевидно, должно оставаться неизменным. Значит, в формулу в ячейке Е5 должна быть введена абсолютная ссылка на ячейку С1 (см. упражнение 43). Абсолютную ссылку можно задать с помощью функциональной клавиши [F4].

Упражнение 61. Использование клавиши [F4] для ввода абсолютной ссылки

1. Активизируйте ячейку Е5 и наберите (но не вводите!) с клавиатуры или с помощью панели формул (см. упражнение 22) формулу =D5*C1.

2. Нажмите клавишу [F4]. Ссылка C1 в ячейке D5 станет абсолютной: $C$1.

3. Снова нажмите клавишу [F4]. Абсолютным станет номер строки: C$1.

4. Нажмите клавишу [F4]. Абсолютным станет имя столбца: $C1.

5. Нажмите клавишу [F4]. Ссылка С1 станет относительной: С1.

6. Еще раз нажмите клавишу [F4]. Ссылка C1 в ячейке D5 станет абсолютной: $C$1.

7. Теперь подтвердите ввод формулы в ячейку Е5.

8. Скопируйте содержимое ячейки Е5 в ячейки Е6:Е14.

Упражнение 62. Вычисление совокупного налога

1. Для заполнения формулами вычисляемого поля Удержано в ячейку F5 введите формулу =0,13*Е5 (тринадцать процентов совокупного налога от начисленного заработка).

2. Скопируйте эту формулу в ячейки F6:F14.

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

Упражнение 63. Использование имен полей в вычисляемых полях

1. Активизируйте ячейку G5.

2. Введите с клавиатуры формулу с именами полей =Начислено-Удержано.

3. Скопируйте эту формулу в диапазон ячеек G6:G14.

Округлите все результаты расчетов до двух знаков после запятой (см. упражнение 33). Сохраните базу данных Зарплата в своей папке. Результат всех действий по созданию базы данных показан на рисунке 3.17.

Рисунок 3.17. Подготовленная к работе база данных Зарплата

Создание базы данных «Мой край» в Microsoft Excel

Содержание

Введение	2
Создание базы данных «Мой край» в Microsoft Excel	3
Первый шаг – разработка модели	3
Второй шаг – выбор программного средства	3
Третий шаг – сбор информации и заполнение БД	3
Полезные ссылки	3
Формирование базы данных	5
Четвертый шаг – конструирование отчетов и запросов	7
Сортировка списков	7
Команда Автофильтр	10
Команда Расширенный фильтр	14
Интервал критериев	14
Текстовые критерии	18
Вычисляемые критерии	19
Применение формы данных для поиска информации в списке	22
Копирование отфильтрованных строк в другое место рабочего листа	22
Подведение итогов	23
Функции баз данных	27
Список Интернет-ресурсов по работе в Microsoft Excel	28

Введение

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

Есть различные программные среды, в которых можно создавать базы данных и использовать их в образовательном процессе. Выбор программной среды зависит от того, для каких целей создается БД. Небольшие базы данных, с несложными запросами можно оперативно создавать в Microsoft Excel, более сложные в Microsoft Access. Сложные БД можно создавать в СУБД MySQL или других мощных оболочках, изменение содержимого такой базы данных можно производить через Web-интерфейс с использованием HTML-формы, не вторгаясь при этом в технические детали каждой специфической СУБД. В данной работе изложены основные способы работы по созданию БД и ссылки на Интернет-ресурсы по данной проблеме, и мы надеемся, что все это поможет вам самостоятельно создать свою базу данных.

Начнем с определения, что же такое база данных.

База данных (БД) – структурированная совокупность взаимосвязанных данных в рамках некоторой предметной области, предназначенная для длительного хранения во внешней памяти ЭВМ и постоянного применения.

Реляционные БД – базы данных с табличной формой организации информации. Реляционная БД состоит из одной или нескольких взаимосвязанных таблиц. (Учебное издание. Информатика. Задачник практикум. Том 2. Издательство «Бином. Лаборатория базовых знаний», 2002, 278с.).

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

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

Какие же списки можно создавать и использовать в учебном процессе? Например, БД под названием: «Физическая география нашего региона», «Полезные ископаемые», «Население нашего района», «Растительный мир», «Животный мир», «Заболеваемость в нашем районе», «История нашего района», «Профессии моего города», «Наши соседи». Хочется отметить, что количество полей этих БД может быть весьма разнообразным. Возьмем, например, таблицу «Наши соседи». В ней может быть представлен список стран, граничащих с данным регионом, количественный состав населения, столица, города, реки, туристические маршруты и т.д.

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

Учитель (школа) может предложить для учащихся готовые списки литературы по тем или иным предметам, интересных сайтов, конкурсов.

Автору представляется наиболее интересным использование баз данных в рамках краевого телекоммуникационного проекта «Мой край». В рамках этого проекта учащиеся всех административных единиц Приморского края сообщают информацию о своем районе, городе, поселении координатору проекта. Координатор проекта собирает эту информацию и помещает ее в БД.

Создание базы данных «Мой край» в Microsoft Excel

Первый шаг – разработка модели

БД «Мой край» представляет собой обычную таблицу с полями: Название административной единицы, Тип поселения, Название района, Численность населения, Год основания, Расстояние до Владивостока. Таким образом, это реляционная БД, состоящая из одной таблицы. Пока из одной, а может быть, у Вас появятся новые интересные идеи и наша БД значительно преобразится, у нее появятся новые поля и вполне вероятно новые таблицы.

Карта Приморского края /russiaoutdoors/853/map.php

Второй шаг – выбор программной среды

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

Третий шаг – сбор информации и заполнение БД

Полезные ссылки

Информацию по нашей проблеме можно найти на сайтах:

/PRIMORYE/index.htm – Инициативный проект лаборатории компьютерных технологий Дальневосточного геологического института Дальневосточного отделения Российской Академии Наук.

, /russiaoutdoors/853/index.php – Мир путешествий и приключений. Дизайн, программирование, контент, поддержка, авторские права и в. в. © 2006 Ury Zimin.

/cgi-bin/allrussia/index.pl?act=reg&id=25 – Территориальное устройство России.

/25.htm – интерактивные карты Приморья.

/index.php?page=catalog&did=&prod=548 – Юридический Интернет-портал Справочник ЮНЕТ.

/primor_kraj/dalnerechsk Народная энциклопедия городов и районов России «Мой город».

/25_06.htm – Приморскстат.

Сайты городов Приморского края

– официальный сайт администрации города Владивосток.

– сайт администрации Артемовского городского округа.

/ru – сайт город Артем.

– Большой Камень Приморского края — Информационный сайт Большого Камня.

– официальный сайт г. Дальнегорска.

/default.aspx – официальный сайт администрации города Находки.

– официальный сайт администрации Уссурийского городского округа.

/lesozavodsk – сайт администрации муниципального образования город Лесозаводск .

– Информационный сайт создан и поддерживается
администрацией Партизанского городского округа.

/spasskd – сайт администрации муниципального образования город Спасск-Дальний.

– Неофициальный сайт города Фокино.

– Дальнереченск.

Сайты районов Приморского края

– официальный сайт администрация Приморского края.

/anuchinsky – сайт администрации муниципального образования Анучинский район.

/kavalerovo – сайт администрации муниципального образования Кавалеровский район.

– сайт управления образования Кировский район.

/krasnoarmeisky – сайт администрации муниципального образования Красноармейский район.

/lazovsky/viewart.php?id=4 – сайт администрации муниципального образования Лазовский район.

/mikhaylovsky/viewart.php?id=5 – сайт администрации муниципального образования Михайловский район.

/nadezhdinsky – сайт администрации муниципального образования Надеждинский район.

– официальный сайт Пограничного муниципального района.

/pozharsky – сайт администрации муниципального образования Пожарский район.

/terneisky – сайт администрации муниципального образования Тернейский район.

– Ханкайский муниципальный район. Администрация Ханкайского Муниципального района.

/hasansky – сайт администрации Хасанского района.

/horolsky – сайт администрации муниципального образования Хорольский район.

/chernigovsky – сайт администрации муниципального образования Черниговский район.

/chuguevsky – сайт администрации муниципального образования Чугуевский район.

Теперь, когда мы знаем, где найти информацию, приступим к созданию нашей таблицы «Мой край».

Формирование базы данных

В списке Excel каждый столбец — это поле, а каждая строка — это запись.

При формировании БД в Excel необходимо руководствоваться следующими правилами:

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

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

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

• В идеале на рабочем листе не должно быть ничего, кроме данной таблицы.

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

Н
а рис.1 приведен список из шести столбцов. Как создать такую таблицу?

Список состоит из строк и столбцов. Столбцы — это поля списка (таблицы БД). Первая строка списка обычно содержит имена полей (заголовки столбцов). Создадим первую строку.

Установите необходимую ширину столбцов, воспользуйтесь командой Столбец, Ширина меню Формат.

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

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

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

Чтобы подчеркнуть названия полей списка воспользуйтесь вкладкой Граница диалогового окна Формат ячеек. На рис.2 приведены примеры диалогового окна Формат ячеек.

П

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

Новые данные можно добавлять непосредственного в конец списка. Однако редактирование списка можно выполнять с помощью стандартной экранной формы. Для этого выделите какую-нибудь ячейку в списке (например, A1) и выполните команду Форма из меню Данные. Перед выполнением команды Форма из меню Данные, должна быть выделена только одна ячейка списка. На рис.4 приведен пример такой формы для БД рис1.

В
строке заголовка формы выводится имя рабочего листа, содержащего редактируемый список. На левой части формы располагаются заголовки столбцов списка и поля ввода соответствующих значений. Если столбец содержит формулы, то поле ввода отсутствует. Ширина формы определяется максимальной шириной столбцов на рабочем листе, а высота формы – количеством столбцов в списке. Справа располагаются кнопки управления списком. Для добавления новой строки (записи) в БД щелкните кнопку Добавить. Excel выведет пустую форму, в поля которой можно вводить значения новой строки. Чтобы вернуться на рабочий лист щелкните кнопку Закрыть. При добавлении новых строк список удлиняется, не затрагивая ячейки вне списка. Для коррекции в списке исходных данных найдите с помощью полосы прокрутки в диалоговом окне формы нужную запись (строку) и внесите изменения в соответствующее поле ввода. Чтобы удалить текущую запись из списка щелкните кнопку Удалить. Удаление записи в диалоговом окне формы нельзя отменить, поэтому Excel выводит запрос на подтверждение этой операции.

Вид нашей БД приведен на рис.1.

Шаг четвертый – конструирование запросов и отчетов

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

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

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

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

В
ыберете поле, по которому нужно сортировать список (в нашем случае – Численность населения). Установите переключатель по убыванию. В разделе Идентифицировать поля по должен быть установлен переключатель подписям (первая строка диапазона) и щелкните ОК. Результат сортировки списка показан на рис.6. Всегда проверяйте результат сортировки. Если она Вас не устраивает, то воспользуйтесь командой Отменить из меню Правка. Если после сортировок нужно восстановить исходный порядок строк в списке, то до сортировки необходимо создать столбец с номерами строк, а затем можно отсортировать список по этому столбцу. В рассмотренном примере исходный список рис.1 был отсортирован по одному столбцу –Численность населения.

О
тсортируем список по полю Название района. Выполните команду Сортировка из меню Данные. Выберите поле по которому нужно сортировать список, в нашем случае Название района. Для сортировки по возрастанию, т.е. от А до Я, установите переключатель по возрастанию.

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

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

Excel дает возможность отсортировать данные не более, чем по трем столбцам. Отсортировать более чем по трем столбцам можно последовательно: сначала по наименее важному столбцу, затем по следующему по важности столбцу и т.д.

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

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

Сортировка в Excel не ограничивается стандартным упорядочением по возрастанию или убыванию.

Команда Автофильтр

Командой Автофильтр можно воспользоваться, выделив какую-либо ячейку в списке и выполнив команду Автофильтр из подменю Фильтр меню Данные. Справа от каждого заголовка столбца появится кнопка со стрелкой вниз. Щелкнув эту кнопку, Вы раскроете список уникальных значений данного столбца, которые можно использовать для задания критерия фильтра. В нашем примере предположим, что в списке, приведенном на рис.1 нужно выделить только строки, относящиеся к Дальнегорскому району. Для фильтрации этих данных следует установить курсор в любую ячейку таблицы (например A1), выбрать команду Автофильтр, в столбце Название района раскрыть список районов (нажав на кнопку со стрелкой, расположенную справа в данном столбце) и выбрать в нем Дальнегорский. Результат показан на рис.9.

При обработке больших интервалов раскрывающиеся списки команды Автофильтр могут содержать не одну сотню элементов. Для быстрого поиска нужного элемента нажмите его первую букву. Обратите внимание, что на рис.9 пропущены номера строк не удовлетворяющие критериям команды Автофильтр, эти строки Excel просто скрывает. Номера отфильтрованных строк выводятся другим цветом.

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

Критерии команды Автофильтр можно задавать по любому количеству столбцов. Сначала отфильтруйте список по одному столбцу, затем полученный список отфильтруйте по другому столбцу и т.д. В нашем примере мы отфильтровали БД по двум столбцам Название района (выбрали Дальнегорский район) и Тип поселения (выбрали пгт-поселок городского типа) рис.10.

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

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

Ч
тобы избавится от строк с пустыми ячейками в фильтруемом столбце, выберите элемент Непустые. Например, отфильтруем сначала строки в списке рис.1, относящиеся к Надеждинскому району, а затем строки в столбце Численность населения, Непустые, результат приведен на рис.12.

В Excel можно создавать пользовательский автофильтр со сложным критерием, состоящим из одного или двух условий с любыми знаками сравнения. Такие критерии позволяют отфильтровывать значения, находящиеся в некотором интервале. Чтобы создать пользовательский автофильтр, раскройте список критериев для требуемого столбца и выберите элемент (Условие…). Откроется диалоговое окно Пользовательский а
втофильтр, приведенное на рис.13.

В этом диалоговом окне можно задать два условия, соединяемые логическими операторами И (AND) или ИЛИ (OR). С левой стороны диалогового окна при помощи раскрывающегося списка выбирается оператор сравнения (равно, не равно, больше, больше или равно, меньше, меньше или равно), который используется в данном условии. С правой стороны диалогового окна при помощи раскрывающегося списка выбирается одно из значений, которое содержится в столбце фильтруемого списка или, если требуемого значения в списке нет, то оно вводится с клавиатуры.

Предположим, мы хотим показать населенные пункты численность населения которых больше 20тыс. человек, но меньше 50тыс. человек. Для списка на рис.1 выполним команду Автофильтр из подменю Фильтр меню Данные. Раскроем список критериев для столбца Численность населения, выберем элемент (Условие…). В диалоговом окне Пользовательский автофильтр при помощи раскрывающегося списка, в первой строке выберем условие Больше или равно, а во второй строке выберем условие Меньше или равно. В первой строке справа введем число 20, а во второй строке – число 30. Переключатель установим в положение И. Результат выполнения данных операций приведен на рис.14.

Для нахождения всех текстовых значений столбца, попадающих в заданный алфавитный интервал, необходимо раскрыть список критериев этого столбца и выбрать элемент (Условие…). В диалоговом окне Пользовательский автофильтр следует задать два критерия, объединенных оператором И. Например, чтобы найти все населенные пункты, названия которых начинаются с буквы Б, нужно установить фильтр по столбцу Название административной единицы, задав в диалоговом окне Пользовательский Автофильтр два условия >Б И
иалогового окна рис. 15. Результат такой фильтрации для таблицы рис.1 приведен на рис.16.

При создании критериев можно использовать два символа шаблона:

• Звездочка (*) для представления любой последовательности символов.

• Вопросительный знак (?) для представления любого отдельного символа.

Например, чтобы получить результат представленный на рис.16, для списка на рис.1 выполним команду Автофильтр из подменю Фильтр меню Данные. Раскроем список критериев для столбца Название административной единицы, выберем элемент (Условие…). В диалоговом окне Пользовательский автофильтр задим критерий, как показано на рис.17.

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

Команда Расширенный фильтр

Интервал критериев

Команда Расширенный фильтр позволяет выполнять следующие операции:

• Создавать критерии с условиями по нескольким столбцам, связанным по правилу ИЛИ. Хотя для этого проще несколько раз выполнить команду Автофильтр.

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

• Создавать вычисляемые критерии. Например,

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

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

Рассмотрим пример критерия с двумя условиями, объединенными по правилу ИЛИ

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

• Вставьте несколько строк для интервала критериев выше списка на рабочем листе. Для этого установите курсор в ячейку A1 и пять раз выполните команду Строки из меню Вставка.

• Создайте интервал критериев, как показано на рис.18. Заголовки условий (Тип поселения, Численность населения, тыс.чел) скопируйте. На рис.18 в ячейках A1:B3 критерии содержаться в виде обыкновенных меток. Под заголовком Тип поселения введено

пгт

а под заголовком Численность населения, тыс.чел.

>=5

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

• Выберите команду Расширенный фильтр из подменю Фильтр меню Данные и введите в соответствии с рис. 19 данные в диалоговом окне Расширенный фильтр. Перед тем как выполнять команду Расширенный фильтр, установите курсор в ячейку A6.

• У
  бедитесь, что установлен переключатель фильтровать список на месте, и щелкните ОК. Результат фильтрации показан на рис.20.

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

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

• Условия на одной строке объединяются по правилу И.

• Условия на отдельных строках объединяются по правилу ИЛИ.

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

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

Пример критерия с тремя условиями по одному столбцу, объединенных по правилу ИЛИ.

П
редположим теперь, что в списке на рис.1 нужно выделить административные единицы с названиями, начинающимися с букв Ш, Я или Ж. В интервал критерия следует включить заголовок столбца Название административной единицы и ввести буквы Ш, Я, Ж в три ячейки под этим заголовком. Установить курсор в любую ячейку списка, который необходимо отфильтровать, затем открыть диалоговое окно Расширенный фильтр и задать ссылки на список и интервал критериев. Результат выполнения данных операций показан на рис.22

Пример критерия с условиями, объединенными по правилам И и ИЛИ одновременно.

К
ак выделить одновременно поселки городского типа и села, численность населения которых больше 10тыс. человек? Необходимо создать интервал критериев, показанный на рис.23. Обратите внимание, что условие >=10 присутствует в каждой строке интервала, потому что записи каждой группы типа поселения (будь-то поселок городского типа или село), должны одновременно удовлетворять и критерию численности населения, т.е. эти два условия объединяются по правилу И.

Текстовые критерии

Обработка текстовых критериев в Excel выполняется по следующим правилам:

• Если задана одна буква, то равенству (=) будут найдены все начинающиеся на эту букву значения. Например, по критерию =П в столбце Название района будут найдены Партизанский, Пограничный, Пожарский районы и т.п.

• По условиям больше (>) или менше (К в столбце Название района будут выделены районы с названиями, начинающимися с букв от К до Я. Противоположный критерий

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

• Символы шаблона обрабатываются также как в автофильтре.

Вычисляемые критерии

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

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

• Ссылки на ячейки вне списка должны быть абсолютными.

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

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

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

2. В ячейку A1 введите заголовок вычисляемого критерия Плотность населения больше медианы. Перед вводом текста установите в A1 Формат ячейки Переносить по словам.

3. В ячейку D1 введите текст Медиана плотности населения. Перед вводом текста установите в D1 Формат ячейки Переносить по словам.

4. При создания вычисляемого фильтра в ячейку E1 вне списка введите формулу =МЕДИАНА(E6:E29).
   Воспользуйтесь командой Функция из меню Вставка, в диалоговом окне Мастер функций шаг 1 из 2 выберите Категория: Статистические и Выберите функцию: МЕДИАНА, щелкните ОК. В диалоговом окне Аргументы функции задайте интервал E6:E29, ОК.

5. В ячейку A2 введите условие вычисляемого критерия с абсолютной ссылкой на эту внешнюю ячейку
   =E6>$E$1

6. Перед тем как выполнять команду Расширенный фильтр, установите курсор в ячейку A5.

7. Выберите команду Расширенный фильтр из подменю Фильтр меню Данные и введите Диапазон условий $A$1:$A$2.

В
строке состояния на рис.25 видно, что по этому фильтру выделено 12 из 24 исходных записей. Как и следовало ожидать, не больше половины всех районов имеют плотность населения выше медианы.

Рассмотренный пример показывает следующее:

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

• В формуле критерия только ячейка E6 сравнивается с ячейкой E1, но при обработке фильтра сравнение выполняется последовательно для всех ячеек столбца E, начиная с ячейки E6 и до конца списка.

• Ссылка на ячейку E1 – абсолютная. Если в ячейке A2 задать формулу =E6>E2 с относительной ссылкой, то ячейка E6 сравнивалась бы с ячейкой E1, ячейка E7 – с ячейкой E2 и т.д., что, очевидно, не правильно.

• На значение, возвращаемое формулой критерия в ячейке A2, можно не обращать внимания. В данном случае значение ЛОЖЬ соответствует тому, что плотность населения Анученского района меньше медианы.

Пример ссылки на ячейки внутри списка

Если в таблице на рис.24 отсутствовал бы столбец Плотность населения, то мы могли бы применить вычисляемый фильтр, для поиска районов с заданной плотностью населения. Например, мы хотим найти районы, где плотность населения меньше чем 5 человек на кв.км. Тогда в формуле критерия
=C6/D6используются относительные ссылки, так как вычисления выполняются с данными из ячеек только текущей строки: C6 делится на D6, C7 делится на D7 и т.д.
Результат фильтрации с вычисляемым критерием и относительными ссылками, приведен на рис.26.

Применение формы данных для поиска информации в списке

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

1. Выделить любую ячейку в списке.

2. Выбрать команду Форма из меню Данные и в открывшемся диалоговом окне щелкнуть кнопку Критерии

3. Заполнить поля ввода так же, как при создании интервала критериев.

4. Щелкнуть кнопку Следующая, чтобы вывести первую строку списка, удовлетворяющую заданным критериям.

5. Пролистать выделенные записи, используя кнопку Следующая, или кнопку предыдущая.

Копирование отфильтрованных строк в другое место рабочего листа

В диалоговом окне Расширенный фильтр можно задать режим копирования строк в другое место рабочего листа: установить переключатель скопировать результат в другое место и в поле ввода Поместить результат в диапазон: ввести имя или адрес интервала, в который нужно поместить результат фильтрации. Проще задать интервал для копирования – щелкнуть мышью начальную ячейку этого интервала. Убедитесь, что справа от этой ячейки и ниже её имеется достаточно свободного места. Заголовки столбцов и все записи списка, удовлетворяющие критериям расширенного фильтра, будут помещены в заданный интервал. Если интервал для копирования задать полностью, отфильтрованные строки будут копироваться только до заполнения всех ячеек интервала. Интервалу, задаваемому в поле Поместить результат в диапазон:, присваивается имя Извлечь, которое можно использовать для перехода внутри листа. Например, чтобы вернуться к этому интервалу для замены заголовков столбцов, нажмите клавишу F5 и выберите Извлечь из диалогового окна Переход.

Чтобы поместить в новый интервал только некоторые столбцы списка, скопируйте туда их заголовки, в поле Поместить результат в диапазон: задайте ссылку на интервал, занимаемый этими заголовками. Например, для копирования только столбцов Название административной единицы и Численность населения отфильтрованного по поселкам городского типа списка «Мой край» рис. 1 в интервал, начинающийся с ячейки L6, выполните следующие действия:

1. Скопируйте заголовки столбцов Название административной единицы и Численность населения в интервал ячеек L6:M6. Заголовки можно копировать в любом порядке в зависимости от требуемого вида отфильтрованных данных.

2. Выберите команду Расширенный фильтр из подменю Фильтр меню Данные.

3. В открывшемся диалоговом окне Расширенный фильтр задайте ссылки на интервал фильтруемого списка и интервал критериев, установите переключатель скопировать результат в другое место, напечатайте L6:M6 в поле Поместить результат в диапазон: и щелкните ОК.

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

1. Создайте интервал критериев, который бы позволил найти все записи, относящиеся к поселкам городского типа, для этого скопируйте заголовок Тип поселения в ячейку A1, а в ячейку A2 введите пгт.

2. В другой пустой области рабочего листа, например в ячейку K4 введите заголовок Название района, в котором есть поселки городского типа. Ячейка с заголовком является началом интервала для копирования отфильтрованных строк.

3. Выберите команду Расширенный фильтр из подменю Фильтр меню Данные.

4. В диалоговом окне Расширенный фильтр введите ссылки на интервалы списка и критериев. Установите переключатель скопировать результат в другое место, напечатайте K4 в поле Поместить результат в диапазон:, установите флажок Т
   олько уникальные записи и щелкните ОК. Результат показан на рис.27.

Подведение итогов

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

Команда Итоги дает возможность подвести также общие итоги, т.е. применить выбранную функцию, например СУММА или СРЕДН, не только к группе записей в списке, но и ко всему списку. Более того, Вы можете задать, место размещения общих итогов в конце или в начале списка. Последнее удобно при длинном списке, так как не нужно искать общие итоги в его конце.

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

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

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

1. Выберите из меню Данные команду Итоги.

2. З
   аполните диалоговое окно Промежуточные итоги в соответствии с рис.29.

3. Убедитесь, что флажки Заменить текущие итоги и Итоги под данными установлены, и щелкните ОК. Результат показан на рис.30.

В соответствии с заданными в диалоговом окне Промежуточные итоги параметрами Excel выполнит следующие действия:

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

• Построит формулу с функцией СУММА для столбца Численность населения (чтобы сложить значения в этом столбце по каждой группе)

• Поместит промежуточные итоги под каждой группой данных, а общие итоги – в конце списка.

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

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

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

Список на рис.30 структурирован, этим можно воспользоваться, чтобы

• Вывести только общие итоги, щелкнув символ уровня строки 1, результат показан на рис.31 (кнопки 1, 2, 3 находятся в левом углу таблицы).

• Вывести общие и промежуточные итоги, щелкнув символ уровня строки 2, результат показан на рис. 32.

• Вывести полный список, щелкнув символ уровня 3, результат показан на рис.30.

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

1. Щелкнуть символ уровня строки 2 для ввода только основных и промежуточных итогов.

2. Отсортировать этот «свёрнутый» по убыванию поля Численность населения.

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

Формула в ячейке D11 на рис.33 может служить примером того, как Excel подводит итоги. В ней использована функция ПРОМЕЖУТОЧНЫЕ ИТОГИ(), в которой аргумент 9 задает тип вычислений. Не стоит применять собственные формулы подведения итогов, к интервалу, содержащему эту функцию.

Флажок Конец страницы между группами в диалоговом окне Промежуточные итоги дает возможность распечатать каждую группу списка с итогами на отдельной странице.

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

Функции баз данных

Функции для работы с базами данных:

• БСЧЁТ — подсчитывает количество ячеек, содержащих числа;

• БСЧЁТА — подсчитывает количество непустых ячеек;

• ДМАКС — ищет максимальное значение;

• ДМИН — ищет минимальное значение;

• БДСУММ — вычисляет сумму числовых значений;

• БДПРОИЗВЕД — перемножает числовые значения;

• ДСРЗНАЧ — считает среднее значение;

• ДСТАНДОТКЛ — оценивает стандартное отклонение;

• ДСТАНДОТКЛП — вычисляет стандартное отклонение по генеральной совокупности;

• БДДИСП — оценивает дисперсию;

• БДДИСПП — вычисляет дисперсию по генеральной совокупности;

• БИЗВЛЕЧЬ — ищет одну запись (если критерию удовлетворяют несколько записей, возвращается ошибка #ЧИСЛО!).

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

=БДСУММ(A6:F75; «Численность населения, тыс.чел.»; A1:A2)

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

Обратите внимание на правила обращения к функциям баз данных:

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

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

• Вторым аргументом может быть заголовок столбца в виде текстовой константы или порядковый номер столбца в списке. Например, в формуле на рис.34 вторым аргументом могло бы быть число 4 потому, что столбец Численность населения – четвертый в списке.

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

Список сайтов по работе в Microsoft Excel

http://www. planetaexcel.ru/index.php – полезная информация по работе в Microsoft Excel.

Коллекция приёмов, on-line-учебник, галерея файлов, ссылки на интернет-ресурсы.

/category – Проэксель – самоучитель (учебник) содержит: программы курсов, упражнения, видеоролики.

/Assets/aCOMP0021/1.htm – Учебник. Знакомство с Microsoft Excel 97. Для знакомства с возможностями Excel предлагаются семь занятий.

Создание базы данных в Microsoft Excel

№ 45-46 Сабақтың оқу-әдістемелік жоспары

Учебно-методический план занятий № 45-46

Топ/Группа
Күні/Дата

Пән/ Предмет Информатика

Тақырып/Тема: Создание базы данных в Microsoft Excel

Сабақтың түрі/ Вид урока: комбинированный

Сабақтың мақсаты/ Цели урока

Білімділік/Образовательная познакомить с электронными таблицами в прикладной средой Microsoft Excel; сформировать первоначальный навык построения графиков и диаграмм;

Дамытушылық/Развивающая развитие логического мышления, памяти, внимания, творческого воображения, умения применять учебную информацию в нестандартных ситуациях, развитие познавательного интереса;

Тәрбиелік/ Воспитательная воспитание коммуникативных качеств для рациональной и продуктивной работы.

Сабақ түрі/ Тип урока изучение нового материала

Оқыту әдісі/ Методы обучения: словесный (объяснение, рассказ),наглядный (слайды, раздаточный материал).

Пәнаралықбаланыс/ Межпредметные связи:

Көрнекі кұралдар, жабдықтар, үлестірме қағаздар/ Наглядные пособия, оборудования, раздаточный материал: приложение Excel, редактор формул, программа «Тестер», «Венгерский» кроссворд.

Сабақ барысы/ Ход урока

1. Ұйымдастыру кезеңі (амандасу, түгендеу, сабақтың міндеті мен мақсаттарын қою).

Организационная часть (приветствие, перекличка, постановка цели и задачи урока).

2. Өткен тақырыпты қайталау/ Опрос и проверка пройденного материала:

Контрольные вопросы и задания.

План:

С помощью Microsoft Excel можно создавать и обрабатывать базы данных. База данных в Microsoft Excel – таблица, состоящая из однотипных записей (строк). Столбцы таблицы являются полями записи в базе данных. Под имена полей выделяется первая строка базы данных.

Список представляет собой электронную таблицу с большим объемом взаимосвязанной информации (список товаров на складах или список номеров телефонов и адресов абонентов).

Обработка базы данных.

При просмотре, изменении, добавлении и удалении записи в базе данных, а также при поиске записей по определенному критерию удобно использовать формы данных. При обращении к команде Форма меню Данные Microsoft Excel считывает данные и создает диалоговое окно формы данных (рис.4).

Рисунок 4.

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

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

Имена столбцов становятся именами полей в форме данных. Поле соответствует каждому столбцу таблицы.

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

Позиция выведенной записи указывается в верхнем правом углу.

Передвигаться по полям формы можно с помощью мыши и клавиш Tab (вниз), Shift+Tab (вверх).

В правой части окна расположены следующие кнопки.

Добавить – очищает поля для ввода новой записи базы данных. Если снова щелкнуть кнопку Добавить, то введенные данные будут добавлены, как новая запись, в конец базы данных.

Удалить – удаляет выведенную запись, другие записи базы данных сдвигаются. Удаленные записи не могут быть восстановлены.

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

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

Далее – выводит следующую запись базы данных.

Критерии – очищает поля перед вводом критериев сравнения с операторами сравнения для поиска необходимого подмножества записей.

Правка – служит для выхода из режима ввода критериев. Доступна только после нажатия кнопки Критерии.

Очистить – удаляет существующий критерий из окна диалога. Доступна только после нажатия кнопки Критерии.

Закрыть – закрывает форму данных.

Для добавления записи к базе данных необходимо:

1. выделить ячейку в таблице, к которой следует добавить запись;

2. в меню Данные выбрать команду Форма;

3. щелкнуть кнопку Добавить;

4. заполнить поля новой записи;

5. для перемещения к следующему полю нажать клавишу Тab;

6. после ввода данных нажать клавишу Enter для добавления записи;

7. после добавления всех необходимых записей, щелкнуть кнопку Закрыть.

Новые записи будут добавлены в конец базы данных.

Поиск данных.

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

1. выделить ячейку в таблице;

2. в меню Данные выбрать команду Форма;

3. щелкнуть кнопку Критерии;

4. в полях редактирования ввести критерии для поиска данных, например, ввести Иванов в поле Фамилия;

5. для вывода на экран первой записи, отвечающей критерию, щелкнуть кнопку Далее;

6. для вывода на экран предыдущей записи, отвечающей критерию, щелкнуть кнопку Назад;

7. для поиска записей в списке по другим критериям щелкнуть кнопку Критерии и ввести новые критерии;

8. по окончанию щелкнуть кнопку Закрыть.

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

Команда Автофильтр устанавливает кнопки скрытых списков (кнопки со стрелками) непосредственно в строку с именами столбцов (рис.5).

Рисунок 5.

С их помощью можно выбирать записи базы данных, которые следует вывести на экран. После выделения элемента в открывшемся списке, строки, не содержащие данный элемент, будут скрыты. Например, если в скрытом списке поля Фамилия выбрать Иванов, то будут выведены только записи, у которых в поле Фамилия содержится значение Иванов. (рис. 6)

Рисунок 6.

Если в поле списка выбрать пункт Условие … , то появится окно Пользовательский автофильтр (рис.7).

Рисунок 7.

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

Чтобы вывести все данные таблицы, необходимо вызвать команду Отобразить все или отменить команду Автофильтр меню Данные, подменю Фильтр.

4. Өтілген тақырыпты бекіту/ Первичное закрепление изученного

Раздаточный материал. Технический диктант

5. Сабақтың қортындысы (білімдерін бағалау).

Подведение итога урока (оценка знаний)._________________________________________

6. Үй тапсырмасы/ Домашнее задание:

Оқытушы/ Преподаватель: Тур Л.И.

BASE (функция BASE) — служба поддержки Office

В этой статье описаны синтаксис формулы и использование BASE . функция в Microsoft Excel.

Описание

Преобразует число в текстовое представление с заданным основанием системы счисления.

Синтаксис

ОСНОВАНИЕ (Число, основание [Мин_длина])

Аргументы функции BASE описаны ниже.

• Номер Обязательно.53.

• Radix Обязательный. Базовая система счисления, в которую вы хотите преобразовать число. Должно быть целым числом больше или равно 2 и меньше или равно 36.

• Мин_длина Необязательно. Минимальная длина возвращаемой строки. Должно быть целым числом больше или равным 0.

Примечания

• Если Number, Radix или Min_length находятся за пределами минимального или максимального ограничений, BASE возвращает #NUM! значение ошибки.

• Если Number — нечисловое значение, BASE возвращает #VALUE! значение ошибки.

• Любое нецелое число, введенное в качестве аргумента, обрезается до целого числа.

• Если включен аргумент Min_length, ведущие нули добавляются к результату, если в противном случае результат был бы короче, чем указанная минимальная длина. Например, BASE (16,2) возвращает 10000, но BASE (16,2,8) возвращает 00010000.

• Максимальное значение аргумента Min_length — 255.

Пример

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

Формула	Описание	Результат
= БАЗА (7,2)	Преобразует десятичное число 7 в основание 2 (двоичное). Результат 111.	111
= БАЗА (100,16)	Преобразует десятичное число 100 в основание 16 (шестнадцатеричное). Результат — 64.	64
= БАЗА (15,2,10)	Преобразует десятичное число 15 в основание 2 (двоичное) с минимальной длиной 10.Результатом будет 0000001111, то есть 1111 с 6 начальными нулями, чтобы сделать строку длиной 10 символов.	0000001111

Верх страницы

Как создать базу данных Excel

Что нужно знать

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

В этой статье объясняется, как создать базу данных в Excel для Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel для Mac, Excel для Android и Excel Online.

Введите данные

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

Чтобы следовать этому руководству, введите данные, как показано на изображении выше.

Быстрый ввод студенческих билетов

1. Введите первые два идентификатора, ST348-245 и ST348-246 , в ячейки A5 и A6, соответственно.
2. Выделите два идентификатора, чтобы выбрать их.
3. Перетащите маркер заливки в ячейку A13 .

Остальные идентификаторы студента правильно вводятся в ячейки от A6 до A13.

Введите данные правильно

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

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

строк — это записи

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

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

Столбцы — поля

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

• Имена полей используются для обеспечения того, чтобы данные для каждой записи вводились в одинаковой последовательности.
• Данные в столбце необходимо вводить в том же формате. Если вы начинаете вводить числа как цифры (например, 10 или 20), продолжайте в том же духе.Не меняйте на полпути и начинайте вводить числа как слова (например, десять или двадцать). Быть последовательным.
• В таблице не должно быть пустых столбцов.

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

Взаимодействие с другими людьми

После ввода данных их можно преобразовать в таблицу. Чтобы преобразовать данные в таблицу:

1. Выделите на листе ячейки с A3 по E13 .
2. Выберите вкладку Home .
3. Выберите формат в качестве таблицы , чтобы открыть раскрывающееся меню.
4. Выберите синий вариант Table Style Medium 9 , чтобы открыть диалоговое окно «Форматировать как таблицу».
5. Пока диалоговое окно открыто, ячейки от A3 до E13 на листе окружены пунктирной линией.
6. Если пунктирная линия окружает правильный диапазон ячеек, выберите OK в диалоговом окне «Форматировать как таблицу».
7. Если пунктирная линия не охватывает правильный диапазон ячеек, выделите правильный диапазон на листе, а затем выберите OK в диалоговом окне «Форматировать как таблицу».

Рядом с каждым именем поля добавляются раскрывающиеся стрелки, а строки таблицы чередуются голубым и темно-синим цветом.

Используйте инструменты базы данных

Взаимодействие с другими людьми

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

Сортировать данные

1. Щелкните стрелку раскрывающегося списка рядом с полем «Фамилия».
2. Выберите Сортировать от А до Я , чтобы отсортировать базу данных по алфавиту.
3. После сортировки Graham J. становится первой записью в таблице, а Wilson R — последней.

Фильтровать данные

1. Выберите стрелку раскрывающегося списка рядом с полем Программа.
2. Установите флажок рядом с полем Выбрать все , чтобы снять все флажки.
3. Установите флажок рядом с Business , чтобы установить флажок в поле.
4. Выбрать ОК .
5. Видны только два студента, Г. Томпсон и Ф. Смит, потому что они единственные двое студентов, обучающихся по деловой программе.
6. Чтобы отобразить все записи, щелкните стрелку раскрывающегося списка рядом с полем Программа и выберите Очистить фильтр из « Программа ».

Расширение базы данных

Чтобы добавить дополнительные записи в базу данных:

1. Наведите указатель мыши на маленькую точку в правом нижнем углу таблицы.
2. Указатель мыши примет форму двунаправленной стрелки.
3. Нажмите и, удерживая правую кнопку мыши, перетащите указатель вниз, чтобы добавить пустую строку в конец базы данных.
4. Добавьте следующие данные в эту новую строку:
   Ячейка A14: ST348-255
   Ячейка B14: Christopher
   Ячейка C14: A.
   Ячейка D14: 22
   Ячейка E14: Science

Завершите форматирование базы данных

Взаимодействие с другими людьми
1. Выделите ячейки от A1 до E1 на листе.
2. Выбрать Дом .
3. Выберите Объединить и центрировать , чтобы центрировать заголовок.
4. Выберите Цвет заливки , чтобы открыть раскрывающийся список цветов заливки.
5. Выберите из списка Синий, Акцент 1 , чтобы изменить цвет фона в ячейках с A1 на E1 на темно-синий.
6. Выберите «Цвет шрифта», чтобы открыть раскрывающийся список цветов шрифта.
7. Выберите Белый из списка, чтобы изменить цвет текста в ячейках с A1 на E1 на белый.
8. Выделите ячейки от A2 до E2 на листе.
9. Выберите Цвет заливки , чтобы открыть раскрывающийся список цветов заливки.
10. Выберите из списка Синий, Акцент 1, Светлее 80 , чтобы изменить цвет фона в ячейках с A2 на E2 на голубой.
11. Выделите ячейки от A4 до E14 на листе.
12. Выберите Центр , чтобы выровнять текст в ячейках от A14 до E14 по центру.

Функции базы данных

Синтаксис : Dfunction (Database_arr, Field_str | num, Criteria_arr)

Где D функция является одной из следующих:

• DAVERAGE
• DCOUNT
• DCOUNTA
• DGET
• DMAX
• DMIN
• DPRODUCT
• DSTDEV
• DSTDEVP
• DSUM
• DVAR
• DVARP

Тип : База данных

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

• Database_arr — это диапазон, встроенный массив или массив, сгенерированный выражением массива. Он структурирован таким образом, что каждая строка после строки 1 является записью базы данных, а каждый столбец — полем базы данных. Строка 1 содержит метки для каждого поля.
• Field_str | num указывает, какой столбец (поле) содержит значения для усреднения.Это может быть выражено либо как имя поля (текстовая строка), либо как номер столбца, где крайний левый столбец будет представлен как 1.
• Criteria_arr — это диапазон, встроенный массив или массив, сгенерированный массивом. выражение. Он структурирован таким образом, что первая строка содержит имя (имена) поля, к которому будет применяться критерий (критерии), а последующие строки содержат условный тест (и).

Первая строка в критериях определяет имена полей. Каждая вторая строка в Criteria представляет собой фильтр, набор ограничений для соответствующих полей.Ограничения описываются с использованием нотации запроса по примеру и включают значение для сопоставления или оператор сравнения, за которым следует значение сравнения. Примеры ограничений: «Шоколад», «42», «> = 42» и «<> 42». Пустая ячейка означает отсутствие ограничений на соответствующее поле.

Фильтр соответствует строке базы данных, если соблюдаются все ограничения фильтра (ограничения в строке фильтра). Строка базы данных (запись) удовлетворяет критериям, если ей соответствует хотя бы один фильтр. Имя поля может появляться более одного раза в диапазоне критериев, чтобы разрешить одновременное применение нескольких ограничений (например, температура> = 65 и температура <= 82).

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

Спасибо, что сообщили нам!

Расскажите, почему!

Другой Недостаточно деталей Сложно понять

Как создавать и использовать именованные диапазоны Excel

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

Имена Excel — Введение

В Excel вы можете создавать имена, относящиеся к:

• Ячейки на листе
• Удельное значение
• Формула

После тебя определить имена Excel, вы можете:

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

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

ПРИМЕЧАНИЕ : Чтобы создать быстрый список всех имен в книге, см. «Быстрый список имен — без макросов».

Как назвать ячейки

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

Ячейки имени — Поле имени

Вы можете быстро присвоить имя выбранным ячейкам, введя имя в поле «Имя». ПРИМЕЧАНИЕ : существует несколько правил для имен Excel

.
1. Выберите ячейки, которым следует присвоить имя
2. Щелкните поле Имя слева от строки формул
3. Введите допустимое имя из одного слова для списка, например.грамм. FruitList.
4. Нажмите клавишу Enter.

Правила создания имен

На сайте Microsoft есть правила для имен Excel, они кратко изложены ниже.

Более подробно о том, какие символы можно использовать, см. В проекте Мартина Траммера на GitHub excel-names — в нем есть примеры и файл Excel для загрузки.

Следуйте этим правилам Microsoft при создании имени в Excel.

• Первый символ имени должен быть одним из следующих символов:
  • письмо
  • подчеркивание (_)
  • обратная косая черта (\).
• Остальные символы в имени могут быть
  • букв
  • номера
  • периодов
  • символов подчеркивания
• Следующие запрещены :
  • Пробелы не могут быть частью имени.
  • Имена не могут выглядеть как адресов ячеек , например, 35 австралийских долларов или R2D2
  • C, c, R, r — не могут использоваться в качестве имен — Excel использует их как ярлыки выбора
• Имена без учета регистра . Например, Север и СЕВЕР обрабатываются как одноименное.

См. Названия

ПРИМЕЧАНИЕ : Чтобы создать быстрый список всех имен в книге, см. «Быстрый список имен — без макросов».

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

• В правом нижнем углу окна Excel щелкните настройку уровня масштабирования
• В диалоговом окне Zoom выберите Custom
• Введите 39 в поле процентов и нажмите ОК

Имена некоторых диапазонов появятся на листе синим текстом, как список месяцев на этом снимке экрана.

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

Изменение именованного диапазона

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

Чтобы изменить эталонный диапазон, выполните следующие действия:

1. На ленте щелкните вкладку Формулы
2. Нажмите Диспетчер имен
3. В списке нажмите на имя, которое хотите изменить
4. В поле Refers To измените ссылку на диапазон или перетащите лист, чтобы выбрать новый диапазон.
5. Щелкните галочку, чтобы сохранить изменение
6. Нажмите «Закрыть», чтобы закрыть диспетчер имен TOP

Создание имен из текста ячейки

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

Создание имен из текста ячейки

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

ПРИМЕЧАНИЕ : Если метки содержат пробелы, они заменяются на подчеркивание. Другие недопустимые символы, такие как & и #, будут удалить или заменить символом подчеркивания.

Для именования ячеек или диапазонов на основе меток рабочего листа:

1. Выберите метки и ячейки, которым необходимо присвоить имя.Этикетки может быть выше, ниже, слева или справа от именованных ячеек. В в этом примере метки находятся в столбце B слева от ячеек. который будет назван.
2. На ленте откройте вкладку Формулы и нажмите Создать из Выбор.
3. В окне «Создать имена из выделенного» установите флажок для расположение меток, затем нажмите ОК.В этом примере метки находятся в левом столбце выбранных ячеек.
4. Щелкните ячейку, чтобы увидеть ее имя. На снимке экрана ниже ячейка C4 выбран, и вы можете увидеть его имя в поле имени — полное_имя.
   ПРИМЕЧАНИЕ : Пробел заменен на подчеркивание.

Создать имя для значения

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

Часто используемые значения

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

• Имя: TaxRate
• относится к: = 0,5

Затем используйте это имя в формулах вместо ввода значения

.

Специальные значения

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

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

• Имя: XL_Max
• Относится к: 9.99999999999999E + 307

Затем используйте имя XL_Max в формулах, как в этой формуле ПРОСМОТР, которая находит последнее число в столбце.

= ПРОСМОТР (9.99999999999999E + 307, данные о весе [вес])

Как использовать имена в Excel

После создания имен вы можете их использовать:

Использование имен для быстрой навигации

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

ПРИМЕЧАНИЕ : Если имя не отображается в раскрывающемся списке, вы можете ввести имя вместо

Использование имен в формулах

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

= СУММ (количество)

Уловки с именами

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

Изменение размера поля имени

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

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

Выбрать ячейки

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

Показать столбцы

Вот быстрый способ показать определенные столбцы и оставить скрытыми другие.

• Скрыть столбцы от A до J
• Щелкните в поле имени
• Введите a1, e1, h2 в поле имени и нажмите Enter.
• Затем используйте команду Показать, чтобы показать выбранные столбцы
  • Вкладка «Главная»> Формат> Скрыть и показать> Показать столбцы

Заполнить ячейки

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

Иногда, однако, в соседнем столбце нет данных, поэтому автозаполнение не работает при двойном щелчке. Вы можете перетащить Fill Handle вниз, но это не очень эффективно, если вам нужно создать длинную серию.

Вот как создать список из 1000 номеров в столбце A:

• Щелкните в поле имени
• Введите a1: a1000 в поле имени и нажмите Enter
.
• Выделив ячейки, введите число 1 и нажмите Ctrl + Enter.
• Затем выберите ячейку A1 и введите 1-е число в своей серии, e.грамм. 5
• Выберите ячейку A2 и введите 2-е число в своей серии, например 10
• Выберите ячейки A1 и A2 и дважды щелкните маркер заполнения, чтобы создать серию из 1000 чисел

Создать динамический именованный диапазон

Если список, который вы хотите назвать, будет часто меняться, добавляемые и удаляемые элементы, следует создать динамический именованный диапазон. Динамический именованный диапазон автоматически изменять размер при изменении списка.Вот два способа создать динамический именованный диапазон:

Используйте именованную таблицу Excel

Используйте формулу

Используйте именованную таблицу Excel

Самый простой способ создать динамический именованный диапазон — начать с создания именованного Таблица Excel. Затем определите диапазон на основе одного или нескольких столбцов в этой таблице.

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

Сначала создайте таблицу:

1. Выберите ячейку в списке деталей
2. На вкладке «Вставка» ленты щелкните Таблица
.
3. Убедитесь, что выбран правильный диапазон, и добавьте проверку отметка для моей таблицы имеет заголовки
4. Нажмите ОК, чтобы создать таблицу.
5. (необязательно) Измените имя таблицы по умолчанию (например,грамм. Table1) на значащее имя, например tblParts

Затем создайте динамический список идентификаторов деталей:

1. Выберите ячейки A2: A9, которые содержат идентификаторы деталей (не заголовок)
2. Щелкните на панели формул и введите имя диапазона из одного слова: PartIDList
3. Нажмите клавишу Enter, чтобы ввести имя.

Чтобы увидеть определение имени, выполните следующие действия:

• Щелкните вкладку Формулы ленты и щелкните Диспетчер имен.
• В списке два названных элемента:
  1. Детали table с именем по умолчанию, Table1 (или именем, которое вы дали таблице)
  2. PartIDList, который на основе поля PartID в Table1.

Текст для динамического диапазона

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

• Добавить новый элемент в список идентификаторов деталей
• В поле Name выберите имя PartIDList
• Выбран именованный диапазон, который включает новый идентификатор детали. ТОП

Динамический именованный диапазон — формула

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

Письменные инструкции под видео.

Динамический именованный диапазон на основе формулы

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

Примечание. Динамические именованные диапазоны не отображаются в раскрывающемся списке «Поле имени». список.Однако вы можете ввести имена в поле имени, чтобы выбрать диапазон на листе.

1. На ленте щелкните вкладку Формулы
2. Нажмите «Определить имя»
3. Введите имя диапазона, например Список имен
4. Оставьте для области «Рабочая книга».
5. В поле «Относится к» введите формулу смещения, которая определяет размер диапазона, основанный на количестве элементов в столбце, e. g .:
   = OFFSET (Sheet1! $ A $ 1,0,0, COUNTA (Sheet1! $ A: $ A), 1)
   В этом примере список находится на Sheet1, начиная с ячейки A1
   Аргументы в этой функции смещения используются:
• Ссылочная ячейка: Sheet1! $ A $ 1
• Строки для смещения: 0
• Столбцы для смещения: 0
• Количество строк: COUNTA (Sheet1! $ A: $ A)
• Количество столбцов: 1
• Примечание : для динамического количества столбцов замените 1 с:
  COUNTA (Sheet1! $ 1: $ 1)
1. Нажмите ОК TOP

Загрузить образец файла

Чтобы следовать инструкциям на этой странице, загрузите образец файла имен Excel.Заархивированный файл имеет формат xlsx и не содержит макросов. ТОП

Ссылки по теме

Использование имен в формулах

Именованные таблицы Excel

Excel именует макросы

Создание динамических диапазонов с помощью макроса

Базовые параметры импорта Excel

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

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

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

Отметьте все запрещенные термины перед импортом: если переводчики не должны использовать некоторые термины в файле Excel, отформатируйте эти термины с помощью красного текста , цвет в Excel. memoQ импортирует эти термины в терминологическую базу как запрещенные термины.

Как добраться

1. Откройте проект. На главной странице проекта выберите Базы сроков.

   Из консоли ресурсов: откройте консоль ресурсов. Выберите Термин базы.

   Из онлайн-проекта: Как менеджер проекта, вы можете открыть онлайн-проект для управления. В окне онлайн-проекта memoQ выберите Базы терминов.

2. Если вам нужны термины в новой терминологической базе, сначала создайте терминологическую базу.
3. Щелкните правой кнопкой мыши имя базы терминов, в которую необходимо импортировать термины.В меню выберите «Импортировать терминологию».
4. Откроется окно «Открыть». Найдите файл, который нужно импортировать. Щелкните Открыть.

   Если файл представляет собой книгу Excel, откроется окно настроек импорта терминологической базы Excel.

Что ты умеешь?

Выберите, какие поля будет импортировать memoQ и куда

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

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

Если в файле есть заголовки столбцов, memoQ попытается найти нужное поле для каждого столбца.

Чтобы это сработало, установите флажок Первая строка содержит имена полей.

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

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

Для каждого поля вы можете выбрать способ импорта. Поле — это столбец в таблице.В разделе «Поля» вы можете сопоставить (или не сопоставить) поля в файле Excel с полями записи терминологической базы. (Вы можете опустить поля в файле Excel.)

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

  В столбцах и полях термин не называется «Термин»: он вызывается языком, на котором он находится. Когда memoQ импортирует термины, memoQ ищет английское название языка в заголовке столбца, а не слово ‘Срок’.

  Хотите составить таблицу, которую memoQ сможет сразу использовать? Чтобы узнать, какие имена полей использует memoQ: Экспортируйте терминологическую базу в файл CSV из memoQ. Откройте файл в Excel и изучите заголовок столбца.

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

Укажите пользователя, тему и домен, в котором их нет в файле

Если эти данные отсутствуют в файле Excels, вы все равно можете добавить их в новые записи.

Введите сведения в поля Имя пользователя, если не указано, Тема, если не указано, и Имя домена, если не указано.

Если эти данные есть в файле Excel: memoQ будет использовать их, а не те, которые вы здесь вводите.

Когда вы закончите

Чтобы импортировать файл Excel в базу терминов и вернуться на главную страницу Project или в консоль ресурсов: нажмите OK.

Чтобы вернуться на главную страницу проекта или в консоль ресурсов без импорта записей: нажмите «Отмена».

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

Как использовать: Функция BASE

Функция BASE, которая преобразует число в текстовое представление с заданным основанием.53).
Основание системы счисления: Основание, которое вы хотите преобразовать в предоставленное число, должно быть целым числом, равным или большим 2, но не должно превышать 36.
Min_length: Этот аргумент является необязательным. Он определяет минимальную длину возвращаемой строки и также должен быть целым числом = 0 и <256.

Эта функция выдает ошибку # ЧИСЛО, если вышеуказанные критерии для числа, системы счисления или минимальной длины не выполняются. Это приведет к ошибке #Value, если предоставленные аргументы не являются числовыми.

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

Кроме того, Excel состоит из множества встроенных функций, которые полезны при аналитическом и статистическом исследовании чисел.

Просто следуйте инструкциям в инфографике ниже:

Если преобразованное число короче, чем значение [min_length], результат дополняется ведущими нулями.

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

Чтобы изучить другие функции, такие как функции ROUNDDOWN, ABS и TRUNC, щелкните имя функции.

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

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

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

(Архивы) Microsoft Excel 2007: Создание таблиц

Эта статья основана на устаревшем программном обеспечении.

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

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

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

Вы можете создать пустую таблицу или создать таблицу из существующего диапазона данных.

Создание таблицы: из диапазона пустых ячеек

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

2. На вкладке команды Вставить в группе Таблицы щелкните Таблица.
   ПРИМЕЧАНИЯ:
   Откроется диалоговое окно «Создать таблицу», в котором отображается выбранный диапазон ячеек.
   За диалоговым окном «Создать таблицу» выбранный диапазон ячеек выделяется анимированной рамкой.
   

3. (Необязательно) Чтобы указать другой диапазон ячеек в Где данные для вашей таблицы? В текстовом поле введите требуемый диапазон ячеек.
   ИЛИ
   Чтобы выбрать диапазон,
   1. Щелкните Свернуть диалоговое окно.
   2. Выберите желаемый диапазон ячеек.
   3. Щелкните Диалоговое окно «Развернуть».

   4. Нажмите ОК.

4. (Необязательно) Если в выбранном диапазоне ячеек уже есть заголовки (т. Е. Подписи столбцов), выберите Моя таблица имеет заголовки.

5. Чтобы принять выбранный диапазон ячеек для таблицы, нажмите ОК .
   Выбранный диапазон ячеек преобразуется в таблицу .
   
   ПРИМЕЧАНИЕ. Для получения информации о терминах таблиц см. Обзор таблиц: термины таблиц.

Создание таблицы: из существующего диапазона данных

1. Выберите данные, которые будут составлять вашу таблицу .

2. На вкладке команды Вставить в группе Таблицы щелкните Таблицы.
   Откроется диалоговое окно Create Table , отображающее выбранный диапазон данных.
   Если Excel обнаруживает заголовки (т.e., метки столбцов) в выбранном диапазоне данных, Моя таблица имеет заголовков автоматически выбирается.

3. (Необязательно) Если в вашей таблице еще нет заголовков (т. Е. Меток столбцов), отмените выбор В моей таблице есть заголовки.

4. (Необязательно) Чтобы указать другой диапазон ячеек в Где данные для вашей таблицы? В текстовом поле введите требуемый диапазон ячеек.
   ИЛИ
   Чтобы выбрать диапазон,
   1. Щелкните Свернуть диалоговое окно.
   2. Выберите желаемый диапазон ячеек.
   3. Щелкните Диалоговое окно «Развернуть».

   4. Нажмите ОК.

5. Чтобы принять выбранный диапазон ячеек для таблицы , нажмите ОК .
   Выбранный диапазон ячеек преобразуется в таблицу .

Чтобы преобразовать таблицу обратно в диапазон данных:

1. Выберите ячейку в таблице .

2. На вкладке команд Design в группе Инструменты щелкните Преобразовать в диапазон.

3. В диалоговом окне подтверждения нажмите Да.
   Таблица преобразуется в диапазон.

Использование форм для ввода табличных данных

После создания таблицы Table Excel предоставляет простой способ ввода данных, называемый функцией Form .Вместо перемещения курсора к каждой новой ячейке с помощью функции Form вы можете добавлять и редактировать данные Table из простого диалогового окна. Диалоговое окно Форма также полезно для поиска записей. Параметры Найти следующий и Найти предыдущий упрощают поиск определенной записи.

В Excel 2007, чтобы получить доступ к функции Form , сначала необходимо добавить кнопку Form на панель быстрого доступа.

Добавление кнопки формы на панель быстрого доступа

1. В верхней части окна Excel справа от панели инструментов быстрого доступа нажмите Настроить панель быстрого доступа »выберите Дополнительные команды .
   Появится диалоговое окно Excel Option s с выбранным параметром Customize .

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

3. В поле прокрутки выберите Форма…

4. Щелкните Добавить.

5. Нажмите ОК .
   Кнопка Форма добавлена ​​на панель инструментов быстрого доступа .

Доступ к диалоговому окну формы

ПРИМЕЧАНИЯ:
К функции формы нельзя получить доступ с пустого листа; у вас должна быть существующая таблица.
Если вы попытаетесь открыть диалоговое окно формы из диапазона пустых ячеек (т. е.е., а не таблица), появится диалоговое окно, в котором можно будет либо использовать первую строку выделения как метки, а не как данные (т. е. открывается диалоговое окно формы), либо отменить и внести соответствующие изменения. в вашу базу данных (т.е. диалоговое окно Форма не открывается).

1. Выберите ячейку в таблице .

2. На панели инструментов быстрого доступа щелкните Форма .
   Появится диалоговое окно Form , отображающее имя листа, имена полей таблицы (т.е., метки столбцов) и любые ранее введенные данные строки.
   ПРИМЕЧАНИЕ. На следующих двух рисунках изображены таблица и диалоговое окно формы при открытии из таблицы.
   

Использование параметров формы

Добавление новой записи

1. Доступ к диалоговому окну формы.
   ПРИМЕЧАНИЯ:
   Текущее представление в диалоговом окне формы всегда представляет одну строку таблицы (т. е. пустую новую строку или существующую строку с ранее введенными данными).
   В диалоговом окне «Форма» отображаются метки существующих столбцов с соответствующими текстовыми полями, представляющими отдельные ячейки под каждым столбцом.

2. Щелкните New.
   Форма очищена и готова для ввода новой записи.

3. В каждом текстовом поле введите желаемую информацию.

4. Для перемещения между полями нажимайте [Tab].

5. Чтобы добавить текущие данные в таблицу и автоматически открыть пустую форму для новой записи, нажмите [Enter] или [return].

6. При необходимости повторите шаги 3–5.
   ПРИМЕЧАНИЕ. Таблица будет автоматически расширяться, если вы введете больше записей, чем количество строк, которое вы изначально указали для таблицы.

7. Когда вы закончите ввод данных, нажмите Close.
   Новые строки появятся в нижней части таблицы .

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

1. Доступ к диалоговому окну формы.
   ПРИМЕЧАНИЯ:
   Текущее представление в диалоговом окне формы всегда представляет одну строку таблицы (т.е. пустую новую строку или существующую строку с ранее введенными данными).
   В диалоговом окне «Форма» отображаются метки существующих столбцов с соответствующими текстовыми полями, представляющими отдельные ячейки под каждым столбцом.

2. Чтобы перейти к нужной строке, нажмите Найти следующий или Найти предыдущий.
   ИЛИ
   Нажмите [Стрелка вверх] или [Стрелка вниз].

3. В соответствующих текстовых полях диалогового окна Форма внесите необходимые изменения.

4. По завершении нажмите Закрыть.

Удаление записи

1. Доступ к диалоговому окну формы.
   ПРИМЕЧАНИЯ:
   Текущее представление в диалоговом окне формы всегда представляет одну строку таблицы (т.е. пустую новую строку или существующую строку с ранее введенными данными).
   В диалоговом окне «Форма» отображаются метки существующих столбцов с соответствующими текстовыми полями, представляющими отдельные ячейки под каждым столбцом.

2. Чтобы выбрать существующую запись, которую вы хотите удалить, нажмите и удерживайте полосу прокрутки, перемещая вверх или вниз.
   ИЛИ
   Щелкните Найти предыдущий или Найти следующий .

3. Нажмите Удалить.
   Появится диалоговое окно подтверждения.

4. Нажмите ОК.

5. Щелкните Clsoe.
   Запись будет окончательно удалена из вашей базы данных.

Поиск записи

Функция Criteria позволяет выполнять поиск по желаемым критериям.

1. Доступ к диалоговому окну формы.
   ПРИМЕЧАНИЯ:
   Текущее представление в диалоговом окне формы всегда представляет одну строку таблицы (т.е.е., пустая новая строка или существующая строка с ранее введенными данными).
   В диалоговом окне «Форма» отображаются метки существующих столбцов с соответствующими текстовыми полями, представляющими отдельные ячейки под каждым столбцом.

2. Щелкните Критерии.

3. В соответствующих полях введите критерии поиска.

4. Нажмите Найти следующий или Найти предыдущий.

5. При необходимости повторите шаги 3-4.

6. Нажмите Закрыть.

Использование окна рабочего листа

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

Добавление строки

1. Щелкните первую пустую строку в нижней части таблицы .

2. Выберите ячейку, в которую вы хотите ввести новые данные.

3. Введите данные соответствующим образом.

4. Нажмите [Tab].
   ИЛИ
   Используйте клавиши со стрелками для перехода к следующему полю записи.
   ПРИМЕЧАНИЕ. Когда вы вводите данные и перемещаетесь по строке, эта строка автоматически добавляется в вашу таблицу.

Удаление строки

Для получения информации об удалении строк таблицы см. Работа со строками и столбцами: удаление строк.

Поиск определенных терминов или значений

Для получения информации о поиске в вашем Таблица см. Использование функций поиска и замены: Использование функции поиска.

Как я могу создать карту из моих данных Excel в Surfer? — Поддержка Golden Software

Вы можете создать различные типы карт из данных в файле Excel или другом табличном файле данных (CSV, DAT и т. Д.) В Surfer. Как вы это делаете, зависит от того, какие данные у вас есть, и от типа карты, которую вы хотите создать.

Данные XYZ
Если ваш файл данных содержит данные XYZ, и вы хотите создать контурную карту или другой тип карты на основе сетки, выполните следующие действия:

1. Нажмите На главную | Данные сетки | Данные сетки , выберите файл данных и нажмите Открыть .
2. В диалоговом окне Grid Data выберите столбцы X, Y и Z из файла данных и выберите любые параметры сетки, которые вы хотите (или примите значения по умолчанию). Нажмите ОК . Это создаст файл сетки из ваших данных.Вы можете использовать этот файл сетки для создания карт.
3. Например, щелкните Домой | Новая карта | Контур (или любой другой тип карты, который вы хотите создать), выберите файл сетки и нажмите Открыть . Карта создана.
4. Выберите слой карты в окне Содержание и отредактируйте свойства карты в окне Свойства .

Данные XY
Если файл Excel содержит данные точек (каждая пара координат XY в файле данных представляет собой отдельную точку), вы можете создать почтовую карту.

1. Нажмите На главную | Новая карта | Сообщение | Post (или Classed Post ), выберите файл данных и нажмите Открыть . Карта постов создана.
2. Выберите слой карты Post в окне Contents и отредактируйте свойства карты в окне Properties . Например, вы можете пометить точку другим столбцом в файле данных, если хотите.

Если файл Excel содержит данные точек с атрибутами, вы можете создать базовую карту из файла данных.

1. Нажмите На главную | Новая карта | База | База данных , выберите файл данных и нажмите Открыть .
2. Укажите столбцы X и Y в диалоговом окне Base from XY Data и нажмите OK . Базовая карта создана.
3. Выберите слой карты Base (vector) в окне Contents и отредактируйте свойства карты в окне Properties . Например, вы можете пометить точки другим столбцом в файле данных или применить символы к точкам.

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

Если вы хотите создать карту, отображающую некоторую информацию о точках данных XY, такую ​​как плотность или количество точек в области поиска, вы можете сопоставить эти данные, как указано выше, используйте только метод сетки Data Metrics , выбрав один статистики расположения данных показателей в расширенных параметрах . Значение Z в файле данных для метрики Data Location Statistics не требуется, поскольку значения Z сетки вычисляются из точек данных XY.В диалоговом окне Grid Data вы устанавливаете столбец Z в столбец данных X или Y. Поскольку этот столбец Z не привязан к сетке, не имеет значения, какой столбец выбран.

Данные XY для полилиний / многоугольников
Если файл Excel содержит координаты XY полилиний или многоугольников и вы хотите загрузить данные в качестве базовой карты, я предлагаю вам преобразовать файл Excel в формат BLN. Формат файла BLN — это простой файл текстовых данных ASCII, состоящий из однострочного заголовка, за которым следуют точечные данные.

Нажмите Файл | Откройте в Surfer и откройте файл XLS на рабочем листе Surfer и добавьте вверху однострочный заголовок с количеством вершин в полилинии / многоугольнике. Если точки определяют многоугольник, убедитесь, что первый и последний набор координат в точности совпадают (вам может потребоваться скопировать первый набор координат и вставить его в конец списка).

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

5 <- начало многоугольника
0,0
1,0
1,1
0,1
0,0 <- повтор первого набора координат для многоугольника
2 <- начало полилинии
0,0
1,1

Нажмите Файл | Сохраните как и сохраните как файл BLN.Затем вы можете нажать На главную | Новая карта | База , выберите файл BLN и нажмите Открыть . Базовая карта создается с отображением объектов, содержащихся в файле BLN.

Для получения дополнительной информации о создании файла BLN см .

No related posts.