Excel 2010 для початківців: Формули, автозаповнення і редагування таблиць

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

Вступ

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

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

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

Виконання базових арифметичних операцій

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

Для виконання розрахунків в будь-якому осередку таблиці необхідно створити всередині неї найпростішу формулу, яка завжди повинна починатися зі знака рівності (=). Для вказівки математичних операцій всередині формули використовуються звичайні арифметичні оператори:

Наприклад, давайте уявимо, що нам необхідно скласти два числа – «12» і «7». Встановіть курсор миші в будь-яку клітинку і надрукуйте такий вираз: «= 12 + 7». Після закінчення введення натисніть клавішу «Enter» і в осередку відобразиться результат обчислення – «19».

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

Далі спробуйте самостійно в осередках нижче отримати різницю цих чисел, їх твір і приватне.

Після проведення всіх операцій, зверніть увагу на результат ділення чисел 12 на 7, який вийшов не цілим (1,714286) і містить досить багато цифр після коми. У більшості випадків така точність не потрібно, та й настільки довгі числа будуть тільки захаращувати таблицю.

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

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

складання формул

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

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

Щоб порахувати сумарний витрата за січень в осередку B7 можна написати такий вираз: «= 18250 + 5100 + 6250 + 2500 + 3300» і натиснути Enter, після чого ви побачите результат обчислення. Це є прикладом застосування найпростішої формули, складання якої нічим не відрізняється від обчислень на калькуляторі. Хіба що знак рівності ставиться спочатку вирази, а не в кінці.

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

З урахуванням цього давайте змінимо нашу формулу обчислення сумарних щомісячних витрат.

У осередок B7, введіть знак рівності (=) і … замість того, щоб вручну вбивати значення клітини B2, клацніть по ній лівою кнопкою миші. Після цього навколо осередку з’явиться пунктирна видільна рамка, яка показує, що її значення потрапило в формулу. Тепер введіть знак «+» і клацніть по комірці B3. Далі виконайте те ж саме з осередками B4, B5 та B6, а потім натисніть клавішу Enter (Enter), після чого з’явиться те ж значення суми, що і в першому випадку.

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

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

Встановіть курсор на порожній клітці збоку потрібного рядка (в нашому випадку це H2). Потім натисніть кнопку сума на закладці Головна у групі редагування. Тепер, повернемося до таблиці і подивимося, що ж сталося.

У обраної нами осередку з’явилася формула з інтервалом осередків, значення яких потрібно підсумувати. При цьому знову з’явилася пунктирна видільна рамка. Тільки в цей раз вона обрамлює не одну клітку, а весь діапазон комірок, суму яких потрібно порахувати.

Тепер подивимося на саму формулу. Як і раніше, спочатку йде знак рівності, але на цей раз за ним слід функція «СУМ» – заздалегідь певна формула, яка виконає складання значень зазначених осередків. Відразу за функцією йдуть дужки розташовані навколо адрес клітин, значення яких потрібно підсумувати, звані аргументом формули. Зверніть увагу, що в формулі не вказані всі адреси сумміруемих осередків, а лише першої і останньої. Двокрапка між ними позначає, що зазначений діапазон клітин від B2 до G2.

Після натискання Enter, в вибраній комірці з’явиться результат, але на цьому можливості кнопки сума не закінчуються. Клацніть на стрілку поруч з нею і відкриється список, що містить функції для обчислення середніх значень (Середнє), кількості введених даних (Число), максимальних (Максимум) і мінімальних (Мінімум) значень.

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

Заповнити форму

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

Іноді в Excel доводиться вводити схожі однотипні дані в певній послідовності, наприклад дні тижня, дати або порядкові номери рядків. Пам’ятайте, в першій частині цього циклу в шапці таблиці ми вводили назву місяця в кожен стовпчик окремо? Насправді, зовсім необов’язково було вводити весь цей список вручну, так як додаток у багатьох випадках може зробити це за вас.

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

На екрані з’явиться підказка, яка повідомить вам те значення, яке програма збирається вставити в наступну клітину. У нашому випадку це «Февраль». У міру переміщення маркера вниз вона буде змінюватися на назви інших місяців, що допоможе вам зрозуміти, де потрібно зупинитися. Після того як кнопка буде відпущена, список заповниться автоматично.

Звичайно, Excel не завжди вірно «розуміє», як потрібно заповнити наступні клітини, так як послідовності можуть бути досить різноманітними. Уявімо собі, що нам необхідно заповнити рядок парними числовими значеннями: 2, 4, 6, 8 і так далі. Якщо ми введемо число «2» і спробуємо перемістити маркер автозаповнення вправо, то виявиться, що програма пропонує, як в наступну, так і в інші осередки вставити знову значення «2».

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

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

Тепер давайте застосуємо цю корисну можливість програми до нашої таблиці, що б не вводити формули вручну для решти клітин. Спочатку виділіть клітинку з уже обчисленою сумою (B7).

Тепер «зачепите» курсором правий нижній кут квадратика і перетягніть маркер вправо до комірки G7. Після того як ви відпустите клавішу, додаток саме скопіює формулу в відмічені осередки, при цьому автоматично змінивши адреси клітин, що містяться в вираженні, підставивши правильні значення.

При цьому якщо маркер переміщати вправо, як в нашому випадку, або вниз, то осередки будуть заповнюватися в порядку зростання, а вліво або вгору – в порядку убування.

Існує так само спосіб заповнення ряду за допомогою стрічки. Скористаємося їм для обчислення сум витрат за всіма видатковими статтями (стовпець H).

Виділяємо діапазон, який слід заповнити, починаючи з комірки з уже введеними даними. Потім на вкладці Головна у групі редагування натискаємо кнопку Заповнити і вибираємо напрямок заповнення.

Додавання рядків, стовпців і об’єднання осередків

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

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

Клацніть в будь-якому осередку другого рядка правою кнопкою миші і в меню, виберіть команду Вставити …, а потім у вікні – Додати рядок.

Після вставки рядка зверніть увагу на той факт, що за замовчуванням вона вставляється над обраною рядком і має формат (колір фону осередків, настройки розміру, кольору тексту і т. Д.) Ряду розташованого над нею.

Якщо потрібно змінити форматування, вибране за умовчанням, відразу після вставки натисніть на кнопку параметри додавання, яка автоматично відобразиться поруч з правим нижнім кутом обраної комірки і виберіть потрібний варіант.

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

До речі, якщо в результаті рядок або стовпець після вставки виявилися на непотрібному місці, їх легко можна видалити. Клацніть правою кнопкою миші на будь-якому осередку, що належить удаляемому об’єкту і в меню, виберіть команду вилучити. На завершення вкажіть, що саме необхідно видалити: рядок, стовпець або окрему клітинку.

На стрічці для операцій додавання можна використовувати кнопку вставити, розташовану в групі осередки на закладці Головна, а для видалення, однойменну команду в тій же групі.

У нашому випадку нам необхідно вставити п’ять нових рядків у верхню частину таблиці відразу після шапки. Для цього можна повторити операцію додавання кілька разів, а можна виконавши її одного разу використовувати клавішу «F4», яка повторює останню операцію.

У підсумку після вставки п’яти горизонтальних рядів в верхню частину таблиці, наводимо її до наступного вигляду:

Білі неформатовані ряди в таблиці ми залишили спеціально, що б відокремити дохідну, видаткову і підсумкову частину друг від друга, написавши в них відповідні заголовки. Але перед тим як це зробити, ми вивчимо ще одну операцію в Excel – об’єднання осередків.

При об’єднанні декількох суміжних вічок утворюється одна, яка може займати відразу декілька стовпців або рядків. При цьому ім’ям об’єднаної комірки стає адресу верхньої дівою осередку об’єднаного діапазону. У будь-який момент ви можете знову розбити об’єднану клітинку, а ось клітку, яка ніколи не була об’єднана, розбити не вдасться.

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

Повернемося до нашої таблиці. Для того, що б написати заголовки в білих стрічках нам знадобиться лише одна клітинка, в той час як зараз вони складаються з восьми. Давайте виправимо це. Виділіть всі вісім осередків другого ряду таблиці і на вкладці Головна у групі вирівнювання клацніть на кнопку Об’єднати і помістити в центрі.

Після виконання команди, всі виділені комірки в рядку об’єднаються в одну велику клітинку.

Поруч з кнопкою об’єднання розташована стрілочка, натискання на яку викличе меню з додатковими командами, що дозволяють: об’єднувати осередки без центрального вирівнювання, об’єднувати цілі групи осередків по горизонталі і вертикалі, а так само скасувати об’єднання.

Після додавання заголовків, а так само заповнення рядків: зарплата, бонуси та щомісячні доходи, наша таблиця стала виглядати ось так:

висновок

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

Тепер переведемо ці розрахунки в формули зрозумілі Excel. Для січня (осередку B14) формула дуже проста і буде виглядати так: «= B5-B12». А ось для осередку С14 (лютий) вираз можна записати двома різними способами: «= (B5-B12) + (C5-C12)» або «= B14 + C5-C12». У першому випадку ми знову проводимо розрахунок балансу попереднього місяця і потім додаємо до нього баланс поточного, а в другому в формулу включається вже розрахований результат за попереднім місяцем. Звичайно, використання другого варіанту для побудови формули в нашому випадку набагато краще. Адже якщо слідувати логіці першого варіанту, то у виразі для березневого розрахунку буде фігурувати вже 6 адрес осередків, в квітні – 8, в травні – 10 і так далі, а при використанні другого варіанта їх завжди буде три.

Для заповнення решти осередків з D14 по G14 застосуємо можливість їх автоматичного заповнення, так само як ми це робили у випадку з сумами.

До речі, для перевірки значення підсумкових накопичень на червень, що знаходиться в клітці G14, в осередку H14 можна вивести різницю між загальною сумою щомісячних доходів (H5) та щомісячних витрат (H12). Як ви розумієте, вони повинні бути рівні.

Як видно з останніх розрахунків, в формулах можна використовувати не тільки адреси суміжних осередків, а й будь-яких інших, незалежно від їх розташування в документі або приналежності до тієї чи іншої таблиці. Більш того ви маєте право пов’язувати комірки знаходяться на різних аркушах документа і навіть в різних книгах, але про це ми вже поговоримо в наступній публікації.

А ось і наша підсумкова таблиця з виконаними розрахунками:

Тепер, при бажанні, ви вже самостійно зможете продовжувати її наповнення, вставляючи як додаткові статті витрат або доходів (рядки), так і додаючи нові місяці (стовпці).

У наступному матеріалі ми більш детально поговоримо про функції, розберемося з поняттям відносних і абсолютних посилань, обов’язково освоїмо ще кілька корисних елементів редагування таблиць і багато іншого.

Читайте також:

Рейтинг: 1.11
| Переглядів: 276 910
Сподобалася стаття? Підпишіться на

Ссылка на основную публикацию