Впродовж останнього десятиріччя комп'ютер в бухгалтерії став просто незамінним інструментом. При цьому його застосування різнопланово. В першу чергу це, звичайно, використання бухгалтерської програми. На сьогоднішній день розроблено досить багато програмних засобів, як спеціалізованих ( «1С», «Інфо-Бухгалтер», «БЕСТ» і т. Д.), Так і універсальних, подібно Microsoft Office. На роботі, так і в побуті часто доводиться робити масу різних розрахунків, вести багаторядкові таблиці з числової і текстової інформації, проробляючи з даними всілякі обчислення, виводячи на друк варіанти. Для вирішення ряду економічних і фінансових завдань доцільно використовувати численні можливості електронних таблиць. Розглянемо в зв'язку з цим обчислювальні функції MS Excel. Володимир СЕРОВ, к. П. Н., Ольга ТИТОВА

Джерело: Журнал "Бухгалтер і Комп'ютер" №4 2004р.

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

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

Всі оператори діляться на кілька груп (див. Таблицю).

Арифметичні ОПЕРАТОРИ

+ (Знак плюс) Додавання = А1 + В2 - (знак мінус) Віднімання Унарний мінус = А1-В2 = -В2 / (коса риска) Розподіл = А1 / В2 * (зірочка) Множення = А1 * В2% (знак відсотка) Відсоток = 20% ^ (кришка) Піднесення до степеня = 5 ^ 3 (5 в 3-го ступеня)

ОПЕРАТОРИ ПОРІВНЯННЯ

=

Так само = ЕСЛИ (А1 = В2; "Так"; "Ні")> Більше = ЕСЛИ (А1> В2; А1; В2) <Менше = ЕСЛИ (АКВ2; В2; А1)> = <= Більше або дорівнює Менше або одно = ЕСЛИ (А1> = В2; А1; В2) = ЕСЛИ (АК = В2; В2; А1) <> Не дорівнює = ЕСЛИ (А1 <> В2; "Чи не рівні")

ТЕКСТОВИЙ ОПЕРАТОР

& (Амперсанд) Об'єднання послідовностей символів в одну послідовність символів = "Значення комірки В2 дорівнює:" & В2

АДРЕСНІ ОПЕРАТОРИ

Діапазон (двокрапка) Посилання на всі осередки між кордонами діапазону включно = СУММ (А1: В2) Об'єднання (крапка з комою) Посилання на об'єднання осередків діапазонів = СУММ (А1: В2; СЗ; D4: Е5) Перетин (пропуск) Посилання на загальні осередки діапазонів = CУMM (A1: B2C3D4: E5) Арифметичні оператори використовуються для позначення основних математичних операцій над числами. Результатом виконання арифметичної операції завжди є число. Оператори порівняння використовуються для позначення операцій порівняння двох чисел. Результатом виконання операції порівняння є логічне значення ІСТИНА або БРЕХНЯ.

Для виконання обчислень в програмі Excel використовуються формули. За допомогою формул можна, наприклад, складати, множити і порівнювати дані таблиць, т. Е. Формулами слід користуватися, коли необхідно ввести в комірку аркуша (автоматично розрахувати) обчислюється значення. Введення формули починається із символу "=" (знак рівності). Саме цим знаком відрізняється введення формул від введення тексту або простого числового значення.

При введенні формул можна застосовувати звичайні числові і текстові значення. Нагадаємо, що числові значення можуть містити тільки цифри від 0 до 9 і спеціальні символи: (плюс, мінус, коса риска, круглі дужки, крапка, кома, знаки відсотка і долара). Текстові значення можуть містити будь-які символи. Необхідно відзначити, що використовувані в формулах текстові вирази повинні полягати в подвійні лапки, наприклад "константа1". Крім того, в формулах можна використовувати посилання на комірки (в тому числі у вигляді імен) і численні функції, які з'єднуються між собою операторами.

Посилання являють собою включаються в формулу адреси осередків або діапазони клітинок. Посилання на осередки задаються звичайним способом, тобто. Е. У вигляді A1, B1, C1. Наприклад, для того, щоб отримати в осередку A3 суму осередків A1 і A2, в неї досить ввести формулу = A1 + A2 (рис. 1).

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

• виділити осередок, в яку потрібно ввести формулу;

• почати введення формули, натиснувши клавішу "=" (дорівнює);

• клацнути мишею на клітинці A1;

• ввести символ "+";

• клацнути мишею на клітинці B2;

• закінчити введення формули, натиснувши клавішу Enter.

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

Діапазон осередків є деякою прямокутну область робочого аркуша і однозначно визначається адресами осередків, розташованими в протилежних кутках діапазону. Розділені символом ":" (двокрапка), ці дві координати становлять адресу діапазону. Наприклад, щоб отримати суму значень комірок діапазону C3: D7, використовуйте формулу = СУММ (C3: D7).

В окремому випадку, коли діапазон складається цілком з декількох стовпців, наприклад від В до D, його адреса записується у вигляді В: D. Аналогічно якщо діапазон цілком складається з рядків з 6-ї по 15-ю, то він має адресу 6:15. Крім того, під час запису формул можна використовувати об'єднання декількох діапазонів або осередків, розділяючи їх символом ";" (крапка з комою), наприклад C3: D7; E5; F3: G7.

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

• подвійним клацанням лівої кнопки миші на осередку, щоб коригувати формулу безпосередньо в цьому осередку;

• вибрати комірку і натиснути клавішу F2 (рис. 2);

• вибрати комірку, перемістивши курсор в рядок формул, клацнути лівої кнопки миші.

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

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

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

Нагадаємо, як правильно здійснити режим копіювання. Тут можуть бути різні варіанти (і проблеми теж).

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

Наприклад, вам потрібно складати підрядник значення стовпців А і В (рис. 8) і помістити результат в стовпець С. Якщо ви копіюєте формулу = А2 + В2 з осередку С2 в осередок С3 * (і далі вниз по С), то Excel сам перетворює адреси формули відповідно як = А3 + В3 (і т. д.). Але якщо вам потрібно помістити формулу, скажімо, з С2 в клітинку D4, то формула вже буде виглядати як = В4 + С4 (замість потрібної = А4 + В4), і відповідно результат обчислень буде неправильним! Іншими словами, зверніть особливу увагу на процес копіювання і при необхідності вручну корегуйте формули. До речі, саме копіювання з С2 в С3 робиться наступним чином:

1) вибираємо осередок С2, з якої потрібно скопіювати формулу;

2) натискаємо кнопку "Копіювати" на панелі інструментів, або клавіші Ctrl + C, або вибираємо в меню "Правка ® Копіювати";

3) вибираємо осередок С3, в яку будемо копіювати формулу;

4) натискаємо кнопку "Вставити" на панелі інструментів, або клавіші Ctrl + V, або через меню "Правка ® Вставити" з натисканням Enter.

Розглянемо режим автозаповнення. Якщо необхідно перенести (скопіювати) формулу в декілька осередків (наприклад, в С3: С5) вниз по стовпчику, то це зручніше і простіше зробити так: повторити попередню послідовність дій до пункту 3 вибрати комірку С3, далі курсор миші підвести до початкової осередку діапазону ( С3), натиснути ліву кнопку миші і, не відпускаючи її, протягнути нижче до необхідної останньої клітинки діапазону. У нашому випадку це осередок С5. Потім відпускаємо ліву кнопку миші, переводимо курсор на кнопку "Вставити" панелі інструментів і натискаємо її, а потім Enter. Excel сам перетворює адреси формул в виділеному нами діапазоні за відповідними адресами рядків.

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

1) вибрати комірку (діапазон), з якої потрібно скопіювати дані;

2) натиснути кнопку "Копіювати" на панелі інструментів або вибрати в меню "Правка ® Копіювати";

3) вибрати комірку (ліву верхню нового діапазону), в яку будуть копіюватися дані;

4) вибрати в меню "Правка ® Спеціальна вставка" і натиснути Enter.

При копіюванні формул комп'ютер відразу проводить розрахунки по ним, видаючи, таким чином, швидкий і наочний результат.

:: Функції в Excel

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

У таблиці часто потрібно обчислити підсумкову суму по стовпцю або рядку. Для цього Excel пропонує функцію автоматичної суми, виконуваної натисканням кнопки ( "Автосумма") на панелі інструментів.

Якщо ми введемо ряд чисел, встановимо курсор під ними і виконаємо подвійне клацання мишею по значку автосуммирования, то відбудеться складання чисел (рис. 3).

В останній версії програми праворуч від значка автосуммирования є кнопка списку, що дозволяє зробити замість підсумовування ряд часто використовуваних операцій (рис. 4).

:: Автоматичні обчислення

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

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

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

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

Для прикладу розрахуємо за допомогою цієї функції суму значень для діапазону В3: В9. Виділіть числа в діапазоні осередків В3: В9. Зверніть увагу, що в рядку стану, розташованої внизу робочого вікна, з'явився напис Сума = X, де X - число, що дорівнює сумі виділених чисел діапазону (рис. 5).

Як бачимо, результати звичайного розрахунку за формулою в осередку В10 і автовичісленія збігаються.

:: Майстер функцій

Крім функції підсумовування Excel дозволяє обробляти дані за допомогою інших функцій. Будь-яку з них можна ввести безпосередньо в рядку формул за допомогою клавіатури, однак для спрощення введення і зниження кількості помилок в Excel є "Майстер функцій" (рис. 6).

Викликати вікно діалогу "Майстра" можна за допомогою команди "Вставка ® Функція", комбінацією клавіш Shift + F3 або кнопкою на стандартній панелі інструментів.

Перший діалог "Майстра функцій" організований за тематичним принципом. Вибравши категорію, в нижньому вікні ми побачимо список імен функцій, що містяться в даній групі. Наприклад, функцію СУММ () ви зможете відшукати в групі "Математичні", а в групі "Дата і час" знаходяться функції ЧИСЛО (), МІСЯЦЬ (), РІК (), СЬОГОДНІ ().

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

Припустимо, що необхідно провести розрахунок амортизації майна. В цьому випадку слід в зоні пошуку функції ввести слово "амортизація". Програма підбере всі функції по амортизації (рис. 7).

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

Нерідко потрібно зробити складання чисел, що задовольняють якомусь умові. В цьому випадку слід використовувати функцію СУММЕСЛИ. Розглянемо конкретний приклад. Припустимо необхідно підрахувати суму комісійних, якщо вартість майна перевищує 75 000 руб. Для цього використовуємо дані таблиці залежності комісійних від вартості майна (рис. 8).

Наші дії в цьому випадку такі. Встановлюємо курсор в осередок В6, кнопкою запускаємо "Майстра функцій", в категорії "Математичні" вибираємо функцію СУММЕСЛИ, задаємо параметри, як на рис. 9.

Зверніть увагу, що в якості діапазону для перевірки умови ми вибираємо інтервал осередків А2: А6 (вартість майна), а в якості діапазону підсумовування - В2: В6 (комісійні), при цьому умова має вигляд (> 75000). Результат нашого розрахунку складе 27 000 руб.

:: Дамо ім'я осередку

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

Привласнюючи імена, необхідно пам'ятати, що вони можуть складатися з букв (в тому числі російського алфавіту), цифр, точок і символів підкреслення. Перший знак в імені повинен бути буквою або знаком підкреслення. Імена не можуть мати такий самий вигляд, як і посилання на комірки, наприклад Z $ 100 або R1C1. В імені може бути більше одного слова, але прогалини неприпустимі. В якості роздільників слів можуть бути використані знаки підкреслення і точки, наприклад Налог_на_продажі або Первий.Квартал. Ім'я може містити до 255 знаків. При цьому великі та малі літери сприймаються однаково.

Щоб вставити ім'я в формулу, можна скористатися командою "Вставка ® Ім'я ® Вставити", вказавши потрібне ім'я в списку імен.

Корисно пам'ятати, що імена в Excel використовуються як абсолютні посилання, т. Е. Є різновидом абсолютної адресації, що зручно при копіюванні формул.

Імена в Excel можна визначати не тільки для окремих осередків, а й для діапазонів (в тому числі несуміжних). Для присвоєння імені досить виділити діапазон, а потім ввести назву в поле імені. Крім того, для завдання імен діапазонів, що містять заголовки, зручно використовувати спеціальну команду "Створити" в меню "Вставка ® Ім'я".

Щоб видалити ім'я, виберіть його в списку і натисніть кнопку "Видалити".

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

то для підрахунку загальної суми для стовпця "Комісійні" використовується формула = СУММ (Комісійні) (рис. 11).

:: Додаткові можливості Excel - шаблони

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

Для створення документа на основі шаблону виконайте команду "Створити" з меню "Файл", потім оберіть шаблон на вкладці "Рішення" (рис. 12).

Шаблони копіюються на диск при звічайній установці Excel. Якщо шаблони не відображаються у вікні діалогу "Створення документа", запустіть програму установки Excel і встановіть шаблони. Щоб отримати докладні відомості про встановлення шаблонів, подивіться розділ "Установка компонентів Microsoft Office" в довідці Excel.

Наприклад, для створення ряду фінансових документів виберіть шаблон "Фінансові шаблони" (рис. 13).

Ця група шаблонів містить форми наступних документів:

• посвідчення про відрядження;
• авансовий звіт;
• платіжне доручення;
• Рахунок-фактура;
• накладна;
• доручення;
• прибутковий і видатковий ордери;
• платіжки за телефон і електроенергію.

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

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

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

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

При створенні документа на основі шаблону програма Excel автоматично створює його робочу копію з розширенням * .xls, додаючи в кінець імені документа порядковий номер. Шаблон-оригінал при цьому залишається недоторканим і може бути згодом використаний повторно.

Для автоматичного введення дати можна скористатися наступним способом: в осередок дати введіть функцію СЬОГОДНІ, після цього в ній відобразиться поточний день місяця, місяць і рік відповідно.

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