Як працювати в програмі Майкрософт ексель. Як написати формулу в Excel: навчання, найпотрібніші формули. Побудова графіків та діаграм

У другій частині циклу 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), після чого з'явиться те ж значення суми, що і в першому випадку.

Виділіть знову комірку 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). Як ви розумієте, вони мають бути рівними.

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

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

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

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

До складу офісного пакета Microsoft Office входить табличний процесор Excel. Особливістю електронної таблиці Excel є автоматичний розрахунок даних із заздалегідь підготовленими формулами. p align="justify"> Електронні таблиці призначені в основному для роботи з числовими даними, але в них можна обробляти і текстові значення.

Знайомство з електронними таблицями

Кожна клітинка електронної таблиці називається осередком. Має власне позначення – ім'я. Як правило, ім'я осередку задається на ім'я стовпця і номер рядка на перетині яких вона розташована. Наприклад, G5, E8.

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

Числаможуть приймати цілі та дробові значення.

текстомвважається будь-яка послідовність символів, включаючи цифри.

Формулоює запис, що починається зі знака "="(рівно), що містить адреси осередків, знаки арифметичних операцій, числа та функції.

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

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

Ознайомимося з інтерфейсом програми. На малюнку нижче верхня частина вікна блок №1(Виділений червоним кольором) містить зліва кнопки панелі швидкого доступу, посередині розташовується назва поточного файлу Книга1, праворуч, на малюнку не показано, стандартні кнопки управління вікнами.

Зовнішній вигляд інтерфейсу Excel 2016

Блок №2(Виділений жовтим кольором) містить стрічку вкладок з інструментами.

Блок №3(виділений зеленим кольором) зліва містить поле, в якому відображається ім'я (адреса) активного осередку. Далі йдуть три кнопки режиму редагування формули:

• хрестик - відмова від введення в поточну комірку - видалення її вмісту;
• галочка - завершення редагування вмісту комірки;
• Fx – виклик майстра функцій заповнення осередку вбудованими функціями табличного процесора.

Третє поле (довге) служить для відображення вмісту активного осередку або розрахункової формули.

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

Блок №4(Виділений фіолетовим кольором) містить робочу область (таблицю). Стовпці таблиці позначають латинськими літерами в алфавітному порядку. Рядки нумеруються арабськими цифрами за зростанням. Внизу робочої області відображаються ярлики аркушів, які можна додавати кнопкою плюс.

Блок №5містить рядок стану, у правій частині якої розташовані кнопки керування режимами відображення таблиць: звичайний, розмітка сторінки, сторінки та масштаб вмісту аркуша.

Відео:Складання чисел в Excel

Як створити формулу в Excel

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

Формування чека вартості покупок

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

Обчислення вартості товару в Excel

У осередку E5задана формула =C5*D5яка перемножує кількість одиниць товару і ціну за одиницю продукту. Для решти чотирьох рядків будуть подібні формули, тільки в них зміниться частина адреси осередку, яка відповідає за номер рядка. Наприклад, шоколад знаходиться в дев'ятому рядку таблиці та формула для розрахунку вартості в комірці E9відповідно набуде вигляду =C9*D9. Формули можна вводити для кожного рядка вручну, але можна провести копіювання формули з комірки E5вниз до E9(Включно).

Копіювання формули в Excel

Копіювання.Завершіть редагування поточної формули натисканням кнопки Enterна клавіатурі. Клацніть комірку, що містить формулу. Наведіть вказівник миші у правий нижній кут виділеної комірки. Як тільки вказівник перетворитися на чорний плюсик, затисніть ліву кнопку миші і, не відпускаючи її, перетягніть рамку вниз на комірку E9. Формула буде вставлена ​​у вказані комірки і в них автоматично відобразиться результат обчислень.

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

Для розрахунку підсумкової вартості, необхідно підсумувати вміст осередків діапазону E5:E9. Для цього в осередок E10запишемо формулу =E5+E6+E7+E8+E9.

Для обчислення здачі в комірку E12записуємо формулу = E11-E10.У осередок E11вписуємо кількість готівки.

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

Розрахунок комунальних послуг: електрика, газ, вода

Для ведення витрат на оплату комунальних послуг можна створити подібну таблицю. Якщо вам цікаво, як зробити таку електронну таблицю, напишіть свою думку в коментарях. Хочете подивитися файл прямо зараз? Ваше посилання для завантаження файлу Комунальні послуги

Дорогий читачу! Ви переглянули статтю до кінця.
Чи отримали ви відповідь на своє запитання?Напишіть у коментарях кілька слів.
Якщо відповіді не знайшли, вкажіть, що шукали.

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

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

Функція "ВПР"

Однією з найпопулярніших функцій у Microsoft Excel є "ВПР" ("VLOOKUP)". Задіявши її, можна перетягувати значення однієї чи кількох таблиць до іншої. При цьому пошук проводиться тільки в першому стовпці таблиці, тим самим при зміні даних у таблиці-джерелі автоматично формуються дані та похідної таблиці, в якій можуть виконуватися окремі розрахунки. Наприклад, відомості з таблиці, в якій знаходяться прейскуранти на товари, можуть використовуватись для розрахунку показників у таблиці про обсяг закупівель у грошовому вираженні.

ВВР запускається шляхом вставки оператора «ВВР»з «Майстри функцій»в той осередок, де дані мають відображатися.

У вікні після запуску цієї функції потрібно вказати адресу осередку або діапазону осередків, звідки дані будуть підтягуватися.

Зведені таблиці

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

Створюється вона на вкладці "Вставка"натисканням на кнопку, яка так і називається "Зведена таблиця".

Створення діаграм

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

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

Точне налаштування діаграм, включаючи встановлення її найменування та найменування осей, проводиться у групі вкладок «Робота з діаграмами».

Одним із видів діаграм є графіки. Принцип побудови їх той самий, як і в інших типів діаграм.

Формули в Excel

p align="justify"> Для роботи з числовими даними в програмі дозволяється використовувати спеціальні формули. З їх допомогою можна проводити різні арифметичні дії з даними в таблицях: додавання, віднімання, множення, розподіл, зведення в ступінь вилучення кореня і т.д. Щоб застосувати формулу, потрібно в осередку, куди планується виводити результат, поставити знак «=» . Після цього вводиться сама формула, яка може складатися з математичних знаків, чисел та адрес осередків. Для вказівки адреси комірки, з якої беруться дані для розрахунку, достатньо клікнути по ній мишкою, і її координати з'явиться в комірці для виведення результату.

Excel також зручно використовувати і як звичайний калькулятор. Для цього в рядку формул або в будь-якому осередку просто вводяться математичні вирази після знака «=» .

Функція «ЯКЩО»

Однією з найпопулярніших функцій, які використовуються в Excel, є «ЯКЩО». Вона дає можливість задати в осередку виведення одного результату при виконанні конкретної умови та іншого результату у разі невиконання. Її синтаксис виглядає наступним чином: ЯКЩО (логічний вираз; [результат якщо істина]; [результат якщо брехня]) .

Операторами «І», «АБО»та вкладеною функцією «ЯКЩО»задається відповідність декільком умовам чи одному з кількох умов.

Макроси

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

Запис макросів також можна робити, використовуючи мову розмітки Visual Basic у спеціальному редакторі.

Умовне форматування

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

Форматування буде здійснено.

«Розумна» таблиця

Не всі користувачі знають, що таблицю, просто накреслену олівцем або межами, Excel сприймає як просту область комірок. Змусити програму бачити цей набір даних як таблицю можна через переформатування. Робиться це просто: спочатку виділяємо потрібний діапазон з даними, а потім, перебуваючи на вкладці «Головна», клацаємо по кнопці "Форматувати як таблицю". З'явиться список із різними варіантами стилів оформлення, де вкажіть відповідний.

Таблиця також створюється натисканням кнопки «Таблиця», яка розташована на вкладці "Вставка", попередньо виділивши певну область аркуша даних.

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

Вибір параметра

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

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

Функція «ІНДЕКС»

Можливості, які надає функція «ІНДЕКС», у чомусь близькі до можливостей функції «ВВР». Вона також дозволяє шукати дані в масиві значень і повертати їх у вказану комірку. Синтаксис виглядає наступним чином: ІНДЕКС (діапазон_осередків; номер рядка; номер стовпця).

Це далеко не повний перелік усіх функцій, доступних у Microsoft Excel. Ми зупинили увагу лише на найпопулярніших і найважливіших із них.

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

Основні поняття та функції

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

У меню входять розділи, властиві всім компонентам пакета Microsoft Офіс: головна, вставка, розмітка сторінки, вид, рецензування. Є й вкладки, властиві лише цій програмі: формули та дані.

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

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

Усі осередки об'єднані у листи. У нижній частині вікна програми є ярлички з назвами аркушів. За замовчуванням це Лист1, Лист2 і Лист3, і навіть ярлик для створення нового листа. Усі листи можна перейменовувати на власний розсуд. Для цього потрібно навести курсор на ярлик і натиснути праву кнопку миші. У меню вибрати відповідну команду. Крім цього, також можна видалити, скопіювати, перемістити, вставити, приховати та захистити аркуш.

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

Створення таблиць

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

1. Вони можна проводити підрахунки і обчислення.
2. У них можна сортувати дані за вибраними критеріями. Найчастіше за зростанням або за спаданням.
3. Їх можна пов'язувати з іншими сторінками та робити динамічними, тобто при зміні даних у зв'язаних полях змінюватимуться дані в інших пов'язаних полях.
4. p align="justify"> Інформацію таких банків даних можна використовувати для побудови гістограм, графіків та інших інтерактивних елементів, що дуже зручно для наочного подання даних.

Це далеко не всі переваги екселівських документів. Саме створення полів, що обчислюються, в Excel гранично просте.Ось необхідні кроки для отримання такого об'єкта:

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

Обчислення за допомогою формул

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

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

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

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

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

Вступ

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

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

Редагування формул та система відстеження помилок

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

Після закінчення редагування натисніть клавіші Enter або Tab, після чого Excel виконати перерахунок з урахуванням змін та відобразить результат.

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

У багатьох випадках, додаток не тільки повідомить вас про наявність помилки, але і вкаже на те, що саме зроблено не так.

Розшифровка помилок у Excel:

• ##### - результатом виконання формули, що використовує значення дати та часу стало негативне число або результат обробки не вміщується в комірці;
• #ЗНАЧ!- Використовується неприпустимий тип оператора або аргументу формули. Одна з найпоширеніших помилок;
• #СПРАВ/0!- у формулі здійснюється спроба поділу на нуль;
• #ІМ'Я?- ім'я, що використовується у формулі, некоректне і Excel не може його розпізнати;
• #Н/Д- Невизначені дані. Найчастіше ця помилка виникає за неправильного визначення аргументу функції;
• #ПОСИЛАННЯ!- формула містить неприпустиме посилання на комірку, наприклад, на комірку, яка була видалена.
• #КІЛЬКІСТЬ!- результатом обчислення є число, яке занадто мало або занадто велике, щоб його можна було використовувати в MS Excel. Діапазон чисел, що відображаються, лежить в проміжку від -10 307 до 10 307 .
• #ПУСТО!- у формулі задано перетин областей, які насправді не мають спільних осередків.

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

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

Впливають осередки- це осередки, на які посилаються формули, а залежні осередкинавпаки, містять формули, що посилаються на адреси клітин електронної таблиці.

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

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

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

Щоб видалити всі стрілки з робочого листа, у групі Залежність формулна вкладці Формули, натисніть кнопку Прибрати стрілки.

Відносні та абсолютні адреси осередків

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

Справа в тому, що Excel розуміє адреси осередків введених у формулу не як посилання на їхнє реальне місцерозташування, а як посилання на їхнє місце розташування щодо комірки, в якій знаходиться формула. Пояснимо на прикладі.

Наприклад, комірка A3 містить формулу: «=A1+A2». Для Excel цей вираз не означає, що потрібно взяти значення з комірки A1 і додати до нього число з комірки A2. Натомість він інтерпретує цю формулу, як «взяти число з комірки розташованої в тому ж стовпці, але на два рядки вище і скласти його зі значенням комірки цього ж стовпця розташованого вище на один рядок». При копіюванні даної формули в іншу комірку, наприклад D3, принцип визначення адрес осередків, що входять у вираз, залишається тим самим: «взяти число з комірки розташованої в тому ж стовпці, але на два рядки вище і скласти його з…». Таким чином, після копіювання D3, вихідна формула автоматично набуде вигляд «=D1+D2».

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

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

За допомогою абсолютних посилань можна дати команду Excel при копіюванні формули:

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

• $A$1 - посилання завжди посилається на комірку A1 (абсолютне посилання);
• A$1 - посилання завжди посилається на рядок 1, а шлях до стовпця може змінюватися (змішане посилання);
• $A1 - посилання завжди посилається на стовпець A, а шлях до рядка може змінюватися (змішане посилання).

Для введення абсолютних та змішаних посилань використовується клавіша F4. Виділіть комірку для формули, введіть знак рівності (=) і клацніть по клітці, на яку треба встановити абсолютне посилання. Потім натисніть клавішу F4, після чого перед літерою стовпця та номером рядка програма встановить знаки долара ($). Повторні натискання F4 дозволяють переходити від одного типу посилань до інших. Наприклад, посилання на E3 буде циклічно змінюватися на $E$3, E$3, $E3, E3 і так далі. За бажання знаки $ можна вводити вручну.

Функції

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

Наприклад, визначення середнього значення п'яти осередків можна описати формулою: =(A1 + A2 + A3 + A4 + A5)/5, а можна спеціальною функцією СРЗНАЧ, яка скоротить вираз до наступного виду: СРЗНАЧ(А1:А5). Як бачите, що замість введення у формулу всіх адрес осередків можна використовувати певну функцію, вказавши їй як аргумент їх діапазон.

Для роботи з функціями Excel на стрічці існує окрема закладка Формули, На якій розміщуються всі основні інструменти для роботи з ними.

Слід зазначити, що програма містить понад двісті функцій, здатних полегшити виконання обчислень різної складності. Тому всі функції в Excel 2010 розділені на кілька категорій, які групують їх за типом завдань. Які саме ці завдання стає ясно з назв категорій: Фінансові, Логічні, Текстові, Математичні, Статистичні, Аналітичні і так далі.

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

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

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

Багато функцій можуть мати відразу кілька аргументів. У такому разі кожен з них відокремлюється від наступного крапкою з комою. Наприклад, функція = ВИРОБ(7; A1; 6; B2) вважає добуток чотирьох різних чисел, зазначених у дужках, і містить чотири аргументи. При цьому в нашому випадку одні аргументи вказані явно, а інші є значеннями певних осередків.

Так само як аргумент можна використовувати іншу функцію, яка в цьому випадку називається вкладеною. Наприклад, функція =СУМ(A1:А5; СРЗНАЧ(В5:В10)) підсумовує значення осередків що знаходяться в діапазоні від А1 до А5, а також середнє значення чисел, розміщених у клітинах В5, В6, В7, В8, В9 і В10.

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

Далеко не всі функції Ecxel мають просте визначення, як функція СУММ, що здійснює підсумовування обраних значень. Деякі з них мають складне синтаксичне написання, а також вимагають багато аргументів, які мають бути правильних типів. Чим складніша функція, тим складніше її правильне складання. І розробники це врахували, включивши до своїх електронних таблиць помічника зі складання функцій для користувачів - Майстер функцій.

Для того щоб почати вводити функцію за допомогою Майстри функцій, натисніть значок Вставити функцію (fx), розташований зліва від Рядки формул.

Також кнопку Вставити функціюви знайдете на стрічці зверху в групі Бібліотека функційу вкладці Формули. Ще одним способом виклику майстра функцій є поєднання клавіш Shift+F3.

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

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

Зробивши необхідний вибір, натисніть кнопку ОК, після чого з'явиться вікно Аргументи функції.

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

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

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

Повторне натискання на неї призведе до відновлення звичайного розміру.

Після введення всіх необхідних значень залишається клікнути по кнопці ОК і в обраній комірці з'явиться результат обчислення.

Діаграми

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

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

Як приклад, спробуємо подати у наочному вигляді дані щомісячних витрат, зазначених у таблиці, створеній нами у попередніх двох частинах матеріалів «Excel 2010 для початківців».

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

Потім, на стрічці у вкладці Вставкав групі ДіаграмиВиберіть потрібний тип та вид діаграми. Щоб побачити короткий опис того чи іншого типу і виду діаграм, необхідно затримати на ньому покажчик миші.

У правому нижньому кутку блоку Діаграмирозташовується невелика кнопка Створити діаграму, за допомогою якої можна відкрити вікно Вставка діаграми, що відображає всі види, типи та шаблони діаграм.

У прикладі давайте виберемо об'ємну циліндричну гістограму з накопиченням і натиснемо кнопку ОК, після чого на робочому аркуші з'явиться діаграма.

Також зверніть увагу, на появу додаткової закладки на стрічці Робота з діаграмами, Що містить ще три вкладки: Конструктор, Макеті Формат.

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

На вкладці Макетрозташовуються команди, що дозволяють додавати або видаляти різні елементи діаграми, які можна легко форматувати за допомогою закладки Формат.

Вкладка Робота з діаграмамиз'являється автоматично щоразу, коли ви виділяєте діаграму і зникає, коли відбувається робота з іншими елементами документа.

Форматування та зміна діаграм

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

Що швидко змінити тип діаграми на вкладці Конструкторв групі Тип, що розміщується зліва, натисніть кнопку Змінити тип діаграми. У вікні зліва виберіть спочатку відповідний тип діаграми, потім її підтип і натисніть кнопку ОК. Діаграма буде автоматично перебудовано. Намагайтеся підбирати такий тип діаграми, який найбільш точно та наочно демонструватиме мету ваших обчислень.

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

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

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

Область діаграми - основне вікно, де розміщуються решта компонентів діаграми. Навівши курсор миші на цю область (з'являється чорне перехрестя), затиснувши ліву кнопку миші, можна перетягнути діаграму в будь-яку частину аркуша. Якщо ж ви хочете змінити розмір діаграми, то наведіть курсор миші на будь-який із кутів або середину сторони її рамки, і коли покажчик набуде форми двосторонньої стрілочки, потягніть його в потрібному напрямку.

Область побудови діаграми - включає вертикальну і горизонтальну осі, ряд даних, а так само основні і додаткові лінії сітки (стіни).

Ряд даних - Дані, представлені в графічному вигляді (діаграма, гістограма, графік і т.д.). Можуть мати підписи даних, що відображають точні цифрові показники рядків або діаграми.

Вісь значень та вісь категорій - числові параметри, розташовані вздовж вертикальної та горизонтальної ліній, орієнтуючись на які можна оцінити дані діаграми. Можуть мати власні підписи поділів та заголовки.

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

Легенда - Розшифровка значень рядів чи рядків.

Будь-якому користувачеві Excel надається можливість самостійно змінювати стилі та художню виставу кожного з перерахованих вище компонентів діаграми. До ваших послуг вибір кольору заливки, стилю кордонів, товщини ліній, накладання об'єму, тіней, свічення та згладжування на вибрані об'єкти. У будь-який момент можна змінити загальний розмір діаграми, збільшити/зменшити будь-яку її область, наприклад, збільшити саму діаграму та зменшити легенду, або взагалі скасувати відображення непотрібних елементів. Можна змінити кут нахилу діаграми, повернути її, зробити об'ємною чи плоскою. Одним словом, MS Excel 2010 містить інструменти, що дозволяють надати діаграмі власноруч найзручніший для сприйняття образ.

Для зміни компонентів діаграми скористайтесь вкладкою Макет, розташованої на стрічці в області Робота з діаграмами.

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

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

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

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

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

У результаті у вікні Формат ряду данихми прибрали фронтальний зазор, а бічний зробили рівним 20%, додали тінь зовні та трохи об'єму зверху.

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

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

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

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

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

Спарклайни чи інфокриві

Завершуючи тему діаграм в електронних таблицях, давайте розглянемо новий інструмент наочного подання даних, який став доступним у Ecxel 2010. Спарклайниабо інфокриві- це невеликі діаграми, що розміщуються в одному осередку, які дозволяють візуально відобразити зміну значень безпосередньо поряд з даними.

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

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

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

Тепер виберемо потрібний порожній осередок у новоствореному стовпці, наприклад H5. Далі на стрічці у вкладці Вставкав групі Спарклайниоберете подібний тип кривої: Графік, Гістограмаабо Виграш/Програш.

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

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

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

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

Тепер, після додавання спарклайнів, наша зведена таблиця набула такого цікавого вигляду:

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

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

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

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

Висновок

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