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

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

Що таке база даних?

Це спеціальна структура, яка містить і зберігає в собі інформаційні матеріали самого різного призначення. Говорячи простою мовою, це набір організованих і розкладених по поличках інформації. Скільки чоловік навчаються в класі, їх характеристики, дати народження та табель успішності – все це в сукупності являє собою базу даних. Вона потрібна скрізь – на промислових і технічних підприємствах, в освітніх і медичних установах, силових структурах і навіть у закладах громадського харчування. Адже список страв, їх рецептура з калорійністю, а також опис теж є вмістилищем даних.


Тут ми розібралися. Тепер потрібно дізнатися, що являє собою база даних в Excel, і як її створити.

Створення сховища даних в Excel

База, створювана нами, буде простий і без вишукувань. Справжні ж вмістилища даних - досить громіздкі і являють собою велику інформаційну систему з внутрішнім «ядром», яке складається з безлічі рядків програмного коду і написано фахівцем. Наша робота буде являти собою одну таблицю, в якій буде вся потрібна інформація. Перш ніж приступити до вирішення питання, як зробити базу даних в Excel, потрібно дізнатися спеціальні терміни, що використовуються при взаємодії з нею.


Горизонтальні рядки в розмітці листа «Ексель» прийнято називати записами, а вертикальні стовпчики – полями. Можна приступати до роботи. Відкриваємо програму і створюємо нову книгу. Потім в саму першу рядок потрібно записати назви полів.

Особливості формату клітинок

Корисно дізнатися про те, як правильно оформляти вміст клітинки. Якщо ваша база даних в Excel буде містити будь-які грошові суми, то краще відразу в відповідних полях вказати числовий формат, у якому після коми буде йти два знака. А якщо де-небудь зустрічається дата, то слід виділити це місце і також встановити для нього відповідний формат. Таким чином, всі ваші дані будуть оформлені правильно і без помилок. Всі операції з порожніми полями програми проводяться через контекстне меню «Формат ячеек». Також важливо і відповідне оформлення проекту. Лист, на якому знаходиться проект, потрібно підписати, щоб уникнути плутанини. Для того щоб система могла відрізняти просте утримання від заголовків і підписів, слід виділяти їх курсивом, підкресленням або жирним шрифтом, при цьому не забуваючи поміщати назви в окремі, не об'єднані поля. Це варто робити для можливості використання таких інструментів, як автоформа і автофільтр.

Що таке автоформа в «Ексель» і навіщо вона потрібна?

Створення бази даних в Excel – заняття важке і копітка. Щоб допомогти полегшити роботу, програма може запропонувати йому автоматичне заповнення комірок заздалегідь заданою інформацією. Наприклад, ширина стовпця, висота рядка, розмір і тип шрифту, колір поля і т. д. – все може в один клік зробити за вас автоформа, якщо правильно її налаштувати. Зручно, правда?

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

Крім цього, не треба забувати про закріплення першого рядка. У Excel 2007 це можна зробити наступним чином: перейти на вкладку «Вид» і виберіть «Закріпити області» і в контекстному меню клікнути на «Закріпити верхній рядок». Це потрібно, щоб зафіксувати «шапку» роботи. Так як база даних Excel може бути досить великий за обсягом, то під час перегляду вгору-вниз буде губитися головна інформація – назви полів, що незручно для користувача. Після того як верхній рядок закріплена, виділяємо перші три рядки в майбутньої бази даних і додаємо межі клітинок.

Продовження роботи над проектом

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

Як створити розкривні списки?

Копіюємо назви полів і переносимо їх на порожній лист, який для зручності також необхідно назвати. Нехай це буде, наприклад, «Батьки». Після того як дані будуть скопійовані, під ними записуємо у порожні клітинки всі необхідні відомості. Для того щоб база даних MS Excel надавала можливість вибору даних із списку, необхідно створити спеціальну формулу. Для цього потрібно привласнити всім відомостями про батьків діапазон значень, імена. Переходимо на той лист, де записані всі дані під назвою «Батьки» і відкриваємо спеціальне вікно для створення імені. Наприклад, в Excel 2007 це можна зробити, клікнувши на «Формули» і натиснувши «Присвоїти ім'я». В полі імені записуємо: ФИО_родителя_вибор. Але що написати в поле діапазону значень? Тут все складніше.

Діапазон значень в Excel

Існує кілька видів діапазонів значень. Діапазон, з яким ми працюємо, називається динамічним. Це означає, що всі проименованние клітинки в базі даних можуть змінювати свої кордони. Їх зміна відбувається в залежності від кількості значень у певному діапазоні. Щоб вийшов динамічний діапазон, необхідно використовувати формулу OFFSET. Вона, незалежно від того, як були задані аргументи, повертає посилання на вихідні дані. У випадаючому списку, який вийде в підсумку, не повинно зустрічатися порожніх значень. З цим чудово справляється динамічний діапазон. Він задається двома координатами осередків: верхньої лівої та правої нижньої, немов по діагоналі. Тому потрібно звернути увагу на місце, звідки починається ваша таблиця, а точніше, на координати верхньої лівої клітинки. Нехай табличка починається в місці А5. Це значення і буде верхньої лівої клітинки діапазону. Тепер, коли перший шуканий елемент знайдений, перейдемо до другого. Нижню праву клітинку визначають такі аргументи, як ширина і висота. Значення останньої нехай буде дорівнює 1 а першу обчислить формула СЧЕТ3(Батьки!$B$5:$I$5). Отже, в полі діапазону записуємо =OFFSET(Батьки!$A$5;0;0;COUNTA(Батьки!$A:$A)-1;1). Натискаємо клавішу ОК. У всіх наступних діапазонах літеру A міняємо на B, C і т. д. Робота з базою даних в Excel майже завершена. Повертаємося на перший лист і створюємо розкривні списки на відповідних комірках. Для цього клікаєм на порожній клітинці (наприклад B3), розташованої під полем «ПІБ батьків». Туди буде вводитися інформація. У вікні «Перевірка введених значень» у вкладці під назвою «Параметри» записуємо в «Джерело» =ФИО_родителя_вибор. У меню «Тип даних» вказуємо «Список». Аналогічно поступаємо з іншими полями, змінюючи назву джерела на відповідне даними клітинок. Робота над випадними списками майже завершена. Потім виділяємо третю клітинку і «протягуємо її через всю таблицю. База даних в Excel майже готова!

Зовнішній вигляд бази даних

Гарне оформлення теж відіграє чималу роль у створенні проекту. Програма Excel може запропонувати на вибір різні способи оформлення бази даних. Кількість колірних схем дуже багате, потрібно тільки вибрати підходящу за душі забарвлення. Крім того, зовсім необов'язково виконувати всю базу даних у єдиному стилі, можна розфарбувати одну колонку в блакитний колір, іншу – в зелений і т. д.

Як перенести базу даних з Excel в Access

Не лише Excel може зробити базу даних. Microsoft випустила ще один продукт, який чудово управляється з цією непростою справою. Назва йому – Access. Так як ця програма більш адаптована під створення бази даних, ніж Excel, то і робота в ній буде більш швидкою і зручною. Але як же зробити так, щоб вийшла база даних в Access? Excel враховує таке бажання користувача. Це можна зробити декількома способами: • Можна виділити всю інформацію, що міститься на аркуші Excel, скопіювати і перенести в іншу програму. Для цього виділіть дані, призначені для копіювання, і клацніть правою кнопкою мишки. У контекстному меню натисніть «Копіювати». Потім перейдіть на Access, виберіть вкладку «Таблиця», групу «Подання» і сміливо клікайте на кнопку «Подання». Вибирайте пункт «Режим таблиці» і вставляйте інформацію, клацнувши правою кнопкою мишки і виберіть «Вставити». • Можна імпортувати аркуш формату .xls (.xlsx). Відкрийте Access, попередньо закривши Excel. У меню виберіть пункт «Імпорт», і клацніть на потрібну версію програми, з якої будете імпортувати файл. Потім натисніть «ОК». • Можна зв'язати з таблицею Excel в Access. Для цього в «Ексель» потрібно виділити діапазон клітинок, який містить необхідну інформацію, і, клікнувши на них правою кнопкою миші, задати ім'я діапазону. Збережіть дані і закрийте Excel. Відкрийте «Аксесс», на вкладці під назвою «Зовнішні дані» виберіть пункт «Електронна таблиця Ексель» і введіть її назву. Потім клацніть по пункту, який пропонує створити таблицю для зв'язку з джерелом даних, і вкажіть її назву. От і все. Робота готова!