Створення формул та аналіз даних – ЧАСТИНА 3

Про синтаксис посилань

До цих пір в прикладах фігурували імена, які Excel присвоює листам і книгам за замовчуванням Але при збереженні книги ви даєте їй інше, постійне імя Якщо ви спочатку створюєте формули, а потім зберігаєте книгу під новим імям, формули підлаштовуються потрібним чином Так, якщо ви зберігаєте книгу Книга2 під імям Продажіxls, посилання = [Книга2] Лист2 $ А $ 2 автоматично перетвориться на заслання

= [Продажіxls] Лист2 $ А $ 2 А якщо перейменувати Лист2 в Лютий, посилання прийме вигляд

= [Продажіxls] лютого $ А $ 2 Якщо ж книга, на яку вказує посилання, закрита, на засланні зявляється повний шлях до папки, де зберігається ця книга:

= С: \ Ехсе1 \ [Продажіх15] лютого! $ А $ 2

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

Використання у формулах «числового тексту»

Під умовним поняттям «Числовий текст» (Numeric text) ховаються вводяться в комірки значення, які за зовнішнім виглядом нагадують числа, але при цьому насправді є текстом З «числовим текстом» можна виробляти будь-які математичні операції, тільки якщо текстові значення містять такі символи:

0 1 2 3 4 5 б 7 8 9, + Е е1

Крім цього, допускається використовувати символ / для запису дробів і пять символів форматування (включаючи пробіл):

$  %  (  )  2

1 У «рідний» версії Excel замість коми за замовчуванням використовується крапка – Прямуючи перев

2 У «рідний» версії Excel замість пробілу за замовчуванням використовується кома – Прямуючи перев

Числовий текст повинен бути укладений у лапки Наприклад, якщо ввести формулу

= $ 1234 + $ 123, Excel виведе повідомлення про помилку у формулі У цьому ж повідомленні буде запропоновано для виправлення помилки прибрати знаки грошової одиниці Але якщо ввести в комірку все ту ж формулу в такому вигляді: = $ 1234 + $ 123 , то програма ігнорує знаки долара і виведе коректний результат – 1357 При виконанні додавання Excel автоматично перетворює числовий текст в числові значення

Текстові значення

Текстові значення(Text values) – це будь вводяться у комірку значення, які не є ні числами, ні числовим текстом У більшості випадків робота з текстом практично не відрізняється від роботи з числами Скажімо, якщо в комірці А1 зберігається текстове значення ABCDE, то, набравши в комірці А10 посилання = А1, ви побачите в цьому осередку правильний результат – ту ж рядок символів ABCDE

Дляобєднання(Concatenation) текстових значень використовується оператор & (Амперсанд) Повертаючись до попереднього прикладу, припустимо, що в комірці А2 зберігається текст FGHIJ і ви набрали у клітинці A3 формулу = А1 & А2 У результаті в комірці A3 зявиться злитий текст ABCDEFGHD Щоб розділити значення пропуском, змініть трохи вид формули: = А1 & & А2 У цій формулі використовуються два оператора обєднання і літерал (текстова константа) – в даному випадку пробіл, укладений в лапки

За допомогою оператора «амперсанд» (&) можна обєднувати і чисельні значення Наприклад, якщо вічко A3 містить числове значення 123, а в комірці А4 зберігається число 456, то результатом використання формули = АЗ & А4 буде рядок символів 123456 Цей рядок вирівнюється в осередку по лівому краю, так як вона є текстом (Памятаєте, з числовим текстом допустимо виробляти будь-які математичні операції, якщо в ньому використовуються тільки перераховані вище числові символи)

Нарешті, оператор & можна використовувати для обєднання текстових і числових значень Так, якщо осередок А1 містить текст ABCDE, а осередок A3 – число 123, то значенням формули = А1 & АЗ буде рядок ABCDE123

Обєднання на практиці

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

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

Повні імена, які сформовані в шпальтах D і Е, створені за допомогою формул, з яких одна видна в рядку формул Значення в стовпці Е є обєднанням через пробіл текстових значень у стовпцях А і В, відповідна формула виглядає так:

= В2 & & А2 Формула для стовпця Е (див рядок формул на малюнку) просто замість символу пробілу вставляє між імям і прізвищем кому

ДИВИСЬ ТАКОЖ

Детально про засоби роботи з текстом розповідається в розділі «Текстові функції»

глави 14

Використання функцій: огляд

Функція (Function) – це заздалегідь визначена формула Багато з функцій Excel – не що інше, як короткі варіанти часто використовуваних формул Наприклад, функція СУММ (SUM) складає значення у виділеному діапазоні Порівняйте запис

= А1 + А2 + А3 + А4 + А5 + А6 + А7 + А8 + А9 + А10 з її аналогом = СУММ (А1: А10) Очевидно,

що функція СУММ істотно спрощує введення і полегшує читання таких виразів

ДИВИСЬ ТАКОЖ

Детальніше про стандартні функції Excel див розділ 13, «Використання функцій» робота з функцією СУММ докладно описана в главі 14, «Найбільш корисні функції»

Деякі функції Excel виконують дуже складні обчислення Так, за допомогою функції ПЛТ (РМТ) можна розрахувати розмір періодичних виплат, необхідних для погашення позики заданого розміру при заданій процентній ставці

Кожна функція має власні імя тааргументи(Arguments), останні записуються в круглих дужках У попередньому прикладі аргументом функції СУММ був діапазон А1: А10 Якщо ви забули поставити заслони дужку і натиснули Enter, програма виправить за вас помилку і додасть заслони дужку в те місце формули, де вона повинна, на її думку, перебувати Але покладатися в таких випадках на табличний процесор не слід, дужка може виявитися зовсім не там, де ви її збиралися поставити, і результат обчислень стане непередбачуваний Рекомендуємо завжди додатково перевіряти правильність розстановки дужок

Автосумма

Функція СУММ використовується частіше інших, саме тому кнопка Автосумма (AutoSum) виведена на панель інструментів Стандартна Розглянемо, як додавати формулу для підсумовування в клітинку, використовуючи кнопку Автосумма:

1 Введіть ряд числових значень в комірки, як показано на рис 127

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

3 Якщо запропонований діапазон не влаштовує вас, то, перш ніж натиснути клавішу Enter,

виділіть мишею потрібний вам

Рис 127 Для підсумовування чисел у стовпцях або рядках виділіть комірку нижче або правіше осередків з числами і натисніть кнопку Автосумма

РАДА

Щоб швидко дізнатися результат підсумовування значень у виділеному діапазоні, подивіться на рядок стану У ній виводиться підсумкове значення Клацніть на цій області рядка стану правою кнопкою миші і в контекстному меню виберіть іншу групову функцію для виділеного діапазону (наприклад, середнє, максимальне або мінімальне значення) Детальніше див розділ «Висновок результатів в рядку стану »глави 2

Ще одна зручність при роботі з кнопкою Автосумма – меню, яке зявляється на екрані, якщо клацнути на стрілці поруч із цією кнопкою, як показано на рис 127 справа Використовуючи команди даного меню, нарівні з функцією СУММ, ви можете ввести в комірку будь-яку з інших запропонованих функцій: Середнє (Average), Число (Count), Максимум (Мах) або Мінімум (Min) Крім того, якщо вибрати команду Інші функції (More Functions), на екрані зявиться вікно Майстра функцій, що відкриває доступ до будь-якої доступної в Excel функції

Кнопка Автосумма дозволяє одночасно вставляти групову функцію в діапазон Наприклад, якщо в таблиці на рис 125 виділити комірки В9: С9 і клацнути на кнопці Автосумма (AutoSum), програма заповнить сумарними значеннями обидві виділені

осередки Докладніше про функції СУММ розповідається в розділі «Функція СУММ» глави

Вставка функцій

Кнопка Вставка функції (Insert Function) у рядку формул надає простий і зручною доступ до вбудованих функцій Excel (Тій же меті служить команда Функція (Function) меню Вставка) Після клацання на цій кнопці на екрані зявляється діалогове вікно Майстра функцій, показане на рис 128 Детальніше про роботу з Майстром функцій див розділ «Додавання функцій на робочий лист» глави 13

Рис 128 Діалогове вікно Майстра функцій

Створення «тривимірних» формул

Ми познайомилися з тим, як оперувати посиланнями на комірки поточного листа, інших листів або інших книг Але ви також можете створювати посилання на комірки діапазонів різних листів в одній книзі Такі посилання називаються«Обємними»(3-D), або

«Тривимірними»(Three-dimensional) Припустимо, у вашій книзі є 12 листів, по одному для кожного місяця, і один лист (Лист1) для підведення підсумків за рік Якщо всі аркуші з місячними звітами мають одну і ту ж структуру, то для підсумовування даних по місяцях допустимо застосувати формулу з тривимірною посиланням Так, формула

= СУММ (Аркуш2: Ліст13 В5) складає все значення, що містяться в комірках В5 на всіх аркушах книги, від Ліста2 до Ліста13 Щоб створити таку формулу, виконайте такі дії:

1 У комірку В5 листа Лист1 введіть = СУММ (

2 Клацніть на ярличку Лист2 і виділіть комірку В5

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

4 При натиснутій клавіші Shift клацніть на ярличку Ліст13 Зверніть увагу, що його сусіди в діапазоні з Лист2 по Ліст13 виявилися виділеними білим кольором, що вказує, що всі ці листи будуть включені в тривимірну посилання

5 Виділіть клітинку В5

6 Поставте заслони дужку та натисніть клавішу Enter

ДИВИСЬ ТАКОЖ –

Детально про груповому редагуванні йдеться в розділі «Редагування групи листів» глави 7

7 При роботі з тривимірними посиланнями можна використовувати такі статистичні функції: СУММ (SUM), МІН (MIN), ДИСП (VAR), СЧЕТЗ (C0UNTA), ПРОИЗВЕД (PRODUCT), ДІСПР (VARP), СРЗНАЧ (AVERAGE), СТАНДОТКЛОН (ST0EV), РАХУНОК (COUNT), МАКС (МАХ) і СТАНДОТКЛОНП (STDEVP)

Форматування в рядку формул

З метою поліпшення сприйняття і спрощення роботи з формулою ви вправі включати в неї пробільні символи і розриви рядка Це ніяк не вплине на результат обчислень Щоб додати у формулу розрив рядка, натисніть клавіші Alt + Enter На рис 129 наведено приклад такої формули

Рис 129 Для полегшення сприйняття формули додайте в неї прогалини і розриви рядка

Позначення діапазонів у формулах

Замість посилань на комірки ви можете при створенні формул посилатися на позначення діапазонів в рядках чи шпальтах таблиці Позначення діапазонів (Labels), або, інакше, заголовки, поміщаються в примикає комірці над діапазоном шпальти і в комірці лівіше діапазону рядка Відповідні формули називаються природно-мовними, це досить точне тлумачення закладеного в даний механізм сенсу, оскільки ви вкладаєте в назви діапазонів необхідну семантику і залучаєте дружні терміни На рис 1210 представлений приклад застосування подібного роду посилань в таблиці з даними про продажі

ДИВИСЬ ТАКОЖ

Діапазонам можна давати явні, фіксовані імена, відмінні від їх позначень-

заголовків Про це розповідає розділ «Присвоєння імен осередкам і діапазонам»

РАДА

За замовчуванням режим «перетину мовного барєру» завжди активний Якщо ви не хочете, щоб він був задіяний, відкрийте діалогове вікно Параметри (Options), перейдіть на вкладку Обчислення (Calculation) і скиньте прапорець допускати назви діапазонів (Accept Labels In Formulas) Цей параметр не має глобальної дії, але запамятовується для кожної окремої книги

Рис 1210 У формулах для посилання на комірки і діапазони можна використовувати позначення діапазонів

При звичайній записи формула в рядках 9 і 10 виглядала б як = СУММ (В4: В7) Якщо ж посилання на комірки підмінити звичайними словами, цю ж формулу можна переписати у вигляді = СУММ (Кв 1) (див рис 1210) У такому варіанті Excel переглядає всі заголовки рядків і стовпців таблиці, і якщо виявляє потрібний, звертається потім для обчислення формули до досить складного набору правил Не будемо на ньому детально зупинятися, зауважимо лише таке:

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

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

Якщо програма не в змозі точно розібратися у ваших намірах, на екран виводиться діалогове вікно Пошук комірки (Identify Label), як показано на рис 1211

У синтаксисі природно-мовних формул окрему клітинку аркуша можна визначити як перетин стовпчика і рядка із заданими іменами заголовків Наприклад, формула

= Регіон 2 Кв 2 визначає комірку С5, оскільки вона знаходиться у вузлі, утвореному стовпцем Кв 2 і рядком Регіон 2 Зверніть увагу, ви повинні між позначеннями діапазонів поставити пробіл

Рис 1211 Діалогове вікно Пошук осередку

Формули такого роду дозволено точно так само копіювати і вставляти в інші осередки листа, при цьому Excel автоматично налаштовує ці формули відповідно з місцем розташування цільової комірки, як у таблиці на рис 1211, де формула з комірки В9 була тиражувана в діапазон С9: Е9 Всі формули даного діапазону змінюються таким чином, щоб у них використовувалися позначення відповідних діапазонів Так, формула з комірки В9 після копіювання в осередок Е9 прийняла вид = СУММ (Кв 4)

ПРИМІТКА

При копіюванні природно-мовних формул в абсолютно невідповідний для них місце Excel завжди виводить попередження Наприклад, при спробі копіювати формулу з комірки Н9 в осередок J9 в ній зявиться текст ІМЯ (NAME), Оскільки стовпець J не має заголовка

Якщо ви зміните позначення діапазонів, Excel автоматично оновить і посилання відповідних формул Наприклад, якщо замість тексту Всього в комірці G3 (див рис 1211) ввести За рік, формула в комірці G9 буде виглядати як = СУММ (За рік)

РАДА

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

Природно-мовна форма запису істотно полегшує читання і розуміння формул,

але, як і будь-яка складна система, породжує інші проблеми:

► Дуже просто отримати повідомлення про те, що імя недійсне

► Результат обчислень може виявитися невизначеним (помилка # ПУСТ0 (# NULL))

► При редагуванні формули її частина може трактуватися як назва функції,

наприклад для формули = Col Row імовірна наступна інтерпретація: = Col ROW ()

Причин тому може бути декілька:

> Формула містить підпрограму VBA, імя якої збігається з позна чением діапазону

> Формула включає в себе посилання на іншу книгу, в свою чергу, містить підпрограму VBA, імя якої і позначення однакові

> У даній робочій книзі вже присутня ідентичне імя (питання іменування осередків та їх діапазонів розглядаються нижче в розділі «Присвоєння імен осередкам і діапазонам»)

> Діапазон названий так само, як і вбудована функція Excel

Щоб уникнути подібних казусів, завжди укладайте позначення в апострофи

Відповідно, формулу = Кв 1 Регіон 1 краще записати у вигляді = Кв 1 Регіон 1.

Зауваження про пересічних діапазонів

Якщо в комірку I4 листа, зображеного на рис 1211, ввести формулу = Кв 1 * 4, Excel буде вважати, що нас цікавить тільки одне значення Кв 1 з діапазону В4: В7 – саме те, яке знаходиться в тій же рядку, що і формула (в даному випадку в комірці В4) Це називається неявним перетинанням (Implicit intersection) Якщо скопіювати формулу = Кв 1 * 4 в діапазон I5: I7, кожна клітинка цього діапазону стане містити одну і ту ж формулу, з тією різницею, що в комірці I5 вона буде фактично посилатися на В5, в I6 – на В6 і т д

Явна перетин (Explicit intersection) вказує на конкретну комірку і задається за допомогою оператора перетину (Intersection operator) – символу пробілу Якщо в будь-якому місці аркуша ввести формулу = Кв 1 Регіон 1 * 4, програма сприйме це так, як ніби ми збираємося в даній комірці використовувати значення, лежаче на перетині діапазону Кв 1 і діапазону Регіон 1, тобто значення з комірки В4

Автоматична підстановка позначень діапазонів

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

1 Виберіть у меню Вставка команду Імя (Name), а потім – Заголовки діапазонів (Label) На екрані зявиться діалогове вікно Заголовки діапазонів (Label Ranges), показане на рис 1212

2 Клацніть у полі Додати діапазон (Add Label Range) і виділіть за допомогою миші потрібний діапазон

3 Встановіть перемикач в одне з положень: Назви рядків (Row Labels) або

Назви стовпців (Column Labels)

4 Натисніть кнопку Додати (Add), щоб включити виділений діапазон в список

Наявні діапазони (Existing Label Ranges)

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

Рис 1212 Діалогове вікно Заголовки діапазонів

Згодом посилання у вже існуючих формулах не зазнають жодних змін, але при введенні будь-яких нових формул Excel автоматично буде заміщати в них посилання на комірки на відповідний текст Так, якщо у прикладі на рис 1212 клацнути на комірці I5, потім ввести знак рівності і клацнути на комірці С5, то в звичайному випадку Excel вивів би в цьому осередку посилання на С4, але тепер у ній зявиться значення Кв 2 Регіон 2

ВИРІШЕННЯ ПРОБЛЕМ

Завжди залишайте порожній рядок знизу і порожній стовпець праворуч від таблиці, яка містить формули, в яких використовуються позначення діапазонів В іншому випадку результати можуть бути непрогнозованими, особливо якщо таблиця має кілька рівнів підсумкових формул, як приклад на рис 1210 Тут, без порожнього рядка, що відокремлює рядка Всього і Середнє від інших даних у таблиці, у рядку Середнє були б підраховані неправильні результати, оскільки значення в рядку Усього розглядалися б як дані за додатковий міфічний квартал Щоб уникнути таких вторгнень в простір-час, використовуйте в формулах замість позначень посилання на імена діапазонів

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


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

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*