Excel 2010: заповнюємо базу даних
Шановні працівники «Б & К»! Мені часто доводиться заповнювати великі бази даних у програмі Excel. Звісно, я використовую для цієї роботи буфер обміну. Але, відверто кажучи, хотілося б знайти зручніше рішення. Порадьте, як прискорити заповнення баз даних в Excel 2010, які для цього є інструменти? Дякую.
Володимир Прохоров, головний бухгалтер, м. Харків
Відповідає Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства
Заповнення баз даних — процес трудомісткий та стомливий. Тому будь-який новий інструмент для автоматичного заповнення таблиць для бухгалтера є дуже важливим, причому з декількох причин. По-перше, автоматичне заповнення прискорює роботу, робить її менш трудомісткою. А по-друге, при автозаповненні значно знижується кількість помилок при введенні даних. Погодьтеся, що цей аспект у бухгалтерській практиці важко переоцінити. Тому не дивно, що для швидкого заповнення таблиць розробники Excel передбачили набір спеціальних інструментів. Із деякими з них я пропоную ознайомитися докладніше. Почнемо з підстановки значень.
Автозаповнення значень,
що повторюються
Як приклад для нашої роботи я вибрав базу даних, зображену на рис. 1. У ній лише три поля: «Дата», «Покупатель» і «Сумма». Причому в колонках «Дата» і «Сумма» вже стоять значення, а дані в колонці «Покупатель» ми будемо заповнювати. Виконуємо такі дії:
1. Стаємо на комірку «B2», уводимо до неї текст із назвою підприємства. Нехай це буде «Омега, ТОВ».
2. Переходимо вниз по колонці «B». У прикладі на рис. 1 я встановив покажчик поточної комірки на «B8».
3. Друкуємо літеру «О». Excel відразу замінить її на слово «Омега, ТОВ».
4. Натискуємо на «Enter» — комірку заповнено.
Що відбулося насправді? Після введення першого символу Excel проаналізував вміст поточної колонки та спробував знайти підприємства, де першим символом у назві буде літера «О». У нашому випадку він виявив одну таку назву — «Омега, ТОВ». Відповідно до внутрішньої логіки роботи Excel «припустив», що ці дані можуть повторюватися в колонці багато разів. Виходячи з цього він запропонував увести це значення автоматично. На мій погляд, дуже зручна можливість.
До речі, підстановка за першими символами виконується у напрямку як «зверху вниз», так і «знизу до верху».
Важливо! Щоб Excel виконав автозаміну, покажчик активної комірки має знаходитися в області бази даних.
Спробуємо ускладнити завдання. Для цього до колонки «Покупатель» додамо назву «Олимп, ООО», після чого повторимо ті самі дії. Тепер на введення літери «О» Excel ніяк не відреагує! І це зрозуміло: одного символу вже недостатньо для чіткої ідентифікації назви в колонці «Покупатель». Для цього потрібно ввести хоча б два символи: послідовність «Ол» Excel запропонує замінити на «Олимп, ООО», символи «Ом» він запропонує замінити на «Омега, ТОВ». Зверніть увагу, що дані для автопідстановки можна набирати в будь-якому регістрі.
Важливо! Щоб уключити (або відключити) можливість автопідстановки, увійдіть до меню «Файл», виберіть розділ «Параметры». Відкриється вікно настройок «Параметры Excel». У ньому перейдіть до розділу «Дополнительно» та у групі «Параметры правки» включіть (або відключіть) прапорець «Автозавершение значений ячеек».
Серед переваг описаного способу я б відзначив простоту його використання. Не потрібні жодні додаткові дії. Уводимо перші лцтери — решту Excel зробить сам.
А от головний його недолік — автопідстановка за збігои символів працює лише з текстом. На числа дія цього інструменту не поширюється. Крім того, автопідстановка працює добре, коли в базі мало схожих елементів. Лише в цьому випадку Excel зможе швидко (уже за першими символами) запропонувати відповідний варіант для заповнення комірок.
Комбінація «Ctrl+D»
Ця комбінація копіює до активної комірки (або виділеного блока робочого листа!) вміст із розташованої вище комірки. Подивимося, який вигляд це має на практиці.
1. Відкрийте базу даних, як на рис. 1.
2. Поставте покажчик активної комірки на «B3».
3. Натисніть на «Ctrl+D». У комірці «B3» з’явиться напис «Омега, ТОВ». Цю інформацію Excel переніс із комірки «B2».
Порада Використовуйте комбінацію «Ctrl+D» для заповнення бази даних «за колонками». Для вибіркового заповнення елементів таблиці використовуйте «Ctrl+D» спільно з автофільтром.
Використання клавіш «Ctrl+D» має кілька переваг:
— у такий спосіб можна продублювати не лише текст, а й числа;
— поєднання «Ctrl+D» копіює не лише вміст, а й формат із розташованої вище комірки;
— комбінація «Ctrl+D» працює при включеному «Caps Lock», тобто як на англійській, так і на російській розкладці клавіатури.
Вибір значень зі списку
Робота в режимі автозаповнення стає неефективною, коли в базі багато схожих елементів. У цьому випадку доведеться надрукувати більшу частину слова, перш ніж програма запропонує скористатися автозаміною. Тут можна порадити роботу зі списком. Повернемося до прикладу на рис. 1. Виконуємо такі дії:
1. Ставимо покажчик миші на вільну комірку в колонці «Покупатель».
2. Клацаємо правою кнопкою миші. З’явиться контекстне меню, як на рис. 2.
3. Із цього меню вибираємо пункт «Выбрать из раскрывающегося списка». Excel покаже список усіх значень з колонки «Покупатель».
4. У списку знаходимо потрібний елемент і клацанням миші підставляємо його до комірки.
Важливо! Список для автозаповнення Excel формує лише з текстових значень.
Вибір значень зі списку приваблює тим, що тут важко помилитися. Усі дані, що повторюються, уведені в такий спосіб, будуть однаковими. Жодних зайвих пробілів, жодних друкарських помилок. Що стосується недоліків, то вибір зі списку не працює з числами.
Повторення дій
за клавішею «F4»
При роботі з базами даних часто доводиться повторювати ті самі дії. Це може бути все, що завгодно, — додавання рядків, виділення кольором, зміна фону, розмірів комірки тощо. У такій ситуації раджу взяти на замітку простий прийом: натиснення на клавішу «F4» повторює останню виконану дію та застосовує її до поточної комірки або виділеного блока. Економія часу від цього прийому інколи просто колосальна. Невеликий приклад: потрібно додати кілька рядків у різних позиціях бази даних. Діючи звичайним способом, нам доведеться:
— поставити покажчик миші на комірку, де потрібно додати рядок;
— клацанням правої кнопки миші розкрити контекстне меню;
— із нього вибрати пункт «Добавить ячейки…»;
— у запропонованому вікні параметрів вибрати варіант «Строку», після чого натиснути на «ОК».
Причому всі перелічені дії доведеться повторити для кожного рядка, що знову додається! Якщо таких рядків буде хоча б сотня, процес може забрати багато часу.
Розглянемо приклад, який вигляд це має при роботі з клавішею «F4». Виконуємо такі дії:
1. Додаємо один рядок будь-яким зручним способом.
2. Ставимо активну комірку на місце вставки нового рядка.
3. Натискуємо на клавішу «F4» (повторюємо попередню дію) і так кілька разів.
За допомогою клавіші «F4» можна виконати більшість операцій Excel: вирівнювання комірок, зміну їх розміру, параметрів форматування тощо. Але повторити введення попереднього значення до нової комірки за клавішею «F4», на жаль, не можна.
Уведення масиву даних
Цей прийом зручний для заповнення області робочого листа однаковими значеннями. Щоб скористатися цим методом, виконайте такі дії:
1. Відкрийте документ Excel. Виділіть блок комірок, куди потрібно записати однакові значення.
2. Не знімаючи виділення, натисніть на клавішу «F2» (увійшовши до режиму редагування комірки). На листі стане доступною для редагування перша комірка виділеного блока.
3. Уведіть до цієї комірки дані. Це можуть бути число, текст або формула.
4. Натисніть на комбінацію клавіш «Ctrl+Shift+Enter». Excel заповнить зазначену частину листа значенням із першої комірки виділеного блока.
Робота з масивами формул — дуже потужний інструмент MS Excel. Він дозволяє не тільки швидко заповнювати даними фрагменти робочої таблиці. Масиви формул можна використовувати у поєднанні з автофільтром, інструментами виділення блоків, для створення складних формул. Але це — матеріал для нашої наступної статті. А на сьогодні все. Сподіваюся, що описані прийоми заповнення таблиць зроблять вашу роботу з Excel продуктивнішою.
Чекаю ваших запитань, зауважень та пропозицій на bk@id.factor.ua , на nictomkar@rambler.ru або на форумі редакції. Успішної роботи!
