Використання круглих дужок
Для того щоб змінити порядок виконання, укладіть частина формули, яка повинна виконуватися першою, в дужки. Наприклад, результатом такої формули буде число 11, оскільки Microsoft Excel виконує множення до складання. У цій формулі число 2 множиться на 3, а потім до результату додається число 5.
Якщо ж за допомогою дужок змінити синтаксис, Microsoft Excel складе 5 і 2, а потім додасть результат на 3; результатом цих дій буде число 21.
У наведеному нижче прикладі дужки навколо першої частини формули визначають наступний порядок обчислень: визначається значення B4 + 25, потім отриманий результат ділиться на суму значень в осередках D5, E5 і F5.
введення формул
Формулу можна вводити або безпосередньо в осередок, або в рядок формул. Якщо в клітинку введена формула, то після натискання Enter в цьому осередку відобразиться результат обчислень за цією формулою. Якщо зробити дану комірку поточної, то сама формула буде відображена в рядку формул, а в осередку - результат обчислень.
Наприклад, нехай потрібно ввести в клітинку А1 суму чисел 10 і 20. Для цього треба виділити осередок А1 і ввести
Після натискання Enter в поточному осередку А1 з'явилося обчислене значення - число 30. а в рядку формул - сама формула, по якій вироблено обчислення. (Рис. 2)
Посилання. Типи посилань.
Види уявлення посилань
Є два види подання посилань в Microsoft Excel:
Стиль посилань R1C1 (тут R - row (рядок), C - column (стовпець)).
Включити стиль посилань R1C1 можна в налаштуваннях «Сервіс» -> «Параметри» -> закладка «Загальні» -> галочка «Стиль посилань R1C1»
У наведеному нижче прикладі функція СРЗНАЧ використовується для розрахунку середнього значення в діапазоні B1: B10 на аркуші «Маркетинг» в тій же самій книзі.
Зверніть увагу на те, що ім'я листа і знак оклику (!) Передують посиланням на діапазон комірок.
Посилання в Excel бувають 3-х типів:
Відносні посилання (приклад: A1);
Абсолютні посилання (приклад: $ A $ 1);
Змішані посилання (приклад: $ A1 або A $ 1, вони наполовину відносні, наполовину абсолютні).
відносні посилання
Скопійована формула з відносною посиланням
абсолютні посилання
Скопійована формула з абсолютною посиланням
змішані посилання
Скопійована формула зі змішаною посиланням
іменовані посилання
Для вставки іменованої посилання можна скористатися кнопкою зі стрілкою вниз:
або натиснути клавішу "F3", відкриється наступне вікно:
Приклад використання: "= СУММ (tablica_1);"
Стиль тривимірних посилань
Тривимірні посилання можуть бути використані для створення посилань на осередки на інших аркушах, визначення імен і створення формул з використанням наступних функцій: СУМ, СРЗНАЧ, СРЗНАЧ, РАХУНОК, СЧЁТЗ, МАКС, МАКСА, МІН, МІНА, вироблено, СТАНДОТКЛОН, СТАНДОТКЛОНА, СТАНДОТКЛОНП, СТАНДОТКЛОНПА, ДИСП, ДІСПА, ДІСПР і ДІСПР.
Тривимірні посилання не можна використовувати в формулах масиву.
Тривимірні посилання не можна використовувати разом з оператором перетину (пропуск), а також в формулах, що використовують неявне перетин.
Видалення. Якщо між листом 2 і листом 6 книги видалити листи, Microsoft Excel виключить із суми комірки віддалених листів.
Переміщення. Якщо перемістити листи, що знаходяться між листом 2 і листом 6 книги, і розмістити їх таким чином, що вони будуть розташовані перед листом 2 або після листа 6, Microsoft Excel виключить із суми комірки переміщених аркушів.
Переміщення граничного листа. Якщо перемістити лист 2 або лист 6 в нове місце книги, Microsoft Excel включить в суму комірки листів, що знаходяться між листом 2 і листом 6 включно.
Видалення граничного листа. Якщо видалити лист 2 або лист 6, Microsoft Excel включить в суму комірки листів, які перебували між ними.
Стиль посилань R1C1
Також можна використовувати стиль посилань, в якому нумеруються як рядки, так і стовпці. Стиль посилань R1C1 корисний при обчисленні положення стовпців і рядків в макросах. У стилі посилань R1C1 Microsoft Excel вказує положення осередку буквою «R», за якою йде номер рядка, і буквою «C», за якою йде номер стовпчика.
При записи макросу Microsoft Excel записує деякі команди з використанням стилю посилань R1C1. Наприклад, якщо записується така команда як натискання кнопки Автосумма для вставки формули, підсумовує діапазон комірок, Microsoft Excel використовує при записі формули стиль посилань R1C1, а не A1.
Для включення або виключення стиль посилань R1C1
Виберіть пункт Параметри в меню Сервіс і перейдіть на вкладку Загальні.
У меню Сервіс встановіть прапорець Стиль посилань R1C1.
Про імена в формулах
Для посилання на комірки в стовпцях і рядках можна використовувати заголовки цих стовпців і рядків листа. Також для подання осередків, діапазонів осередків, формул або констант можна створювати імена. Заголовки можна використовувати в формулах, які містять посилання на дані на тому ж аркуші; для подання групи осередків, що знаходиться на іншому аркуші, цій групі слід привласнити ім'я.
Використання тексту в формулах
З текстовими значеннями можна виконувати математичні операції, якщо текстові значення містять тільки такі символи:
Цифри від 0 до 9. + - е Е /
Ще можна використовувати п'ять символів числового форматування:
При цьому текст повинен бути укладений в подвійні лапки.
При виконанні обчислень Excel перетворює числовий текст в числові значення, так результатом вищевказаної формули буде значення 88.
Для об'єднання текстових значень служить текстовий оператор (Амперсанд). Наприклад, якщо комірка А1 містить текстове значення "Юрій", а осередок А2 - "Кордик", то ввівши в комірку А3 наступну формулу = А1А2, отримаємо "ЮрійКордик". Щоб вставити пробіл між ім'ям і прізвищем треба написати так = А1 "" А2. Амперсанд можна використовувати для об'єднання осередків з різними типами даних. Так, якщо в осередку А1 знаходиться число 10, а в осередку А2 - текст "мішків", то в результаті дії формули = А1А2, ми отримаємо "10мешков". Причому результатом такого об'єднання буде текстове значення.
«Введення даних в Excel || Ексель || імена осередків Excel »
Використання імен
Імена осередків служать не тільки для того, щоб з їх допомогою звертатися до окремих осередків і діапазонів, а й для їх використання в формулах. Наприклад, у вас є проста формула в комірці КЗ. По ній ви обчислюєте загальну суму до виплати, множачи кількість годин (осередок 13), протягом яких ви працювали на клієнта, на погодинний тариф, установ-ний для даного клієнта (осередок J3). Зазвичай подібна формула вводиться в осередок КЗ в такому вигляді:
Однак якщо ви присвоїли ім'я Годинники осередку 13 та ім'я Тариф осередку J3, можете ввести формулу
в осередок КЗ. Ця формула набагато простіше для розуміння, ніж = 13 * J3.
Для введення формули з використанням імен осередків виконайте такі дії
1. Призначте імена своїм осередкам, як описано вище в цьому розділі.
В даному прикладі ім'я Годинники присвоєно осередку 13, а ім'я Тариф - осередку J3.
Розмістіть табличний курсор в осередку, в яку буде введена формула.
У нашому прикладі це осередок КЗ.
Введіть з клавіатури знак рівності (=), що позначає початок формули.
Виберіть першу осередок, що входить в формулу, виділивши її ім'я у вікні списку в
рядку формул (як описано вище).
У нашому прикладі ми посилаємося на осередок 13, вибираючи її ім'я Годинники з вікна списку імен.
5. Введіть з клавіатури арифметичний оператор, який використовується в даній
формулою.
У нашому прикладі це символ "зірочка" (*) для позначення операції множення.
6. Вкажіть другу посилання на осередок, яка присутня у формулі, вибравши її
ім'я з вікна списку імен в рядку формул.
В даному прикладі ви вибираєте осередок J3 по її імені Тариф у вікні списку.
7. Клацніть на кнопці Введення (Enter) або натисніть клавішу для заверше-
ня введення формули.
У розглянутому прикладі Excel вводить формулу = Годинники * Тариф у клітинку КЗ.
Формули в робочій таблиці обчислюються миттєво. Якщо змінити значення в будь-якому осередку використовується у формулі, то ця формула видасть новий результат без будь-яких проблем з Вашого боку. Це відбувається в тому випадку, якщо в Excel встановлений режим автоматичного обчислень (встановлено за умовчанням). У цьому режимі при обчисленні формул Excel дотримується наступних правил:
Якщо Ви вносите зміни - наприклад, вводите чи редагуючи дані або формули, - Excel миттєво виробляє обчислення з тих формул, в яких використовуються нові або відредаговані дані.
Якщо під час виконання довгих обчислень Вам знадобиться щось змінити в робочій таблиці, то програма тимчасово припинить обчислення і відновить їх, коли Ви закінчите редагування.
Обчислення за формулами виробляються в природному порядку. Іншими словами, якщо у формулі з осередку D12 використовується результат обчислень за формулою з осередку D11. то спочатку буде обчислена формула з осередку D11. a потім - з D12.
Втім, іноді виникає необхідність управляти процесом обчислення за формулами. Наприклад, якщо створити робочу таблицю з тисячами складних формул, то виявиться, що Excel виробляє обчислення з досить низькою швидкістю. У подібному випадку потрібно встановити в Excelручной режим обчислень. Це можна зробити за допомогою вкладки Обчислення діалогового вікна Параметри (рис. 3).
Мал. 3. Діалогове вікно Параметри вкладка Обчислення
Щоб вибрати ручний режим обчислень, треба встановити прапорець опції Вручну. При перемиканні в ручний режим обчислень, автоматично активізується опція перерахунок перед збереженням. Тому, якщо Ви хочете прискорити процес збереження файлу, зніміть прапорець цієї опції.
Якщо під час роботи в ручному режимі обчислень у Вас залишаються будь-які невичісленние формули, то в рядку стану з'явиться напис Обчислити. Для перерахунку формул можна скористатися наступними комбінаціями клавіш:
F9. Перераховує формули у всіх відкритих робочих книгах.
Shift + F9. Перераховує тільки формули активного робочого аркуша. Формули з інших робочих листів цієї ж робочої книги перераховуватися не будуть.
Ctrl + Alt + F9. Прискорює повний перерахунок формул.
Режим обчислень, встановлений в Excel. відноситься не тільки до конкретного робочого аркушу. Перехід в інший режим обчислень впливає на всі відкриті робочі книги, а не тільки на поточну робочу книгу.
Присвоєння імен констант і формул
Імена осередків у формулі
Для присвоєння імені служить команда "Присвоїти" в меню "Вставка", під меню "Ім'я". Вгорі діалогового вікна потрібно ввести ім'я, внизу - посилання на осередок (діапазон комірок), або математичний вираз. Потім слід натиснути на кнопку "Додати". Нове ім'я увійде в список імен, розташований на цьому ж діалоговому вікні, і стане доступним для використання в формулах. Будь-яке з імен мож-но видалити, натиснувши на однойменну кнопку. Щоб відредагувати ярлик, що відповідає певному імені, слід вказати на нього мишею, відредагувати вираз внизу вікна, і натиснути на Вставити. Все присвоєні імена зберігаються в робочій книзі і залишаються доступними при наступному відкритті файлу.
Для переходу на певну комірку або виділення заданого
діапазону комірок досить в поле імені рядка формул ввести посилання
на осередок (діапазон комірок), або присвоєне ім'я. Щоб увійти в режим
редагування поля імені, необхідно клацнути по ньому мишею.
При іменуванні осередку або діапазону осередків керуйтеся такими правилами.
Імена діапазонів повинні починатися з літери, але ні в якому разі не з цифри. Наприклад, замість 01 Підсумки використовуйте підсумками !.
Імена діапазонів не можуть містити пробілів. Замість пробілу використовуйте знак підкреслення для з'єднання різних частин імені. Наприклад, замість Підсумки 01 використовуйте Ітогі_01.
Імена діапазонів не повинні мати нічого спільного з координатами осередків.
Наприклад, ви не можете дати осередку ім'я Q1, так як це діюча координата комірки. Краще використовуйте що-небудь на зразок Q1 ^ продажу.
Для призначення імені вічку або інтервалу осередків виконайте такі дії.
Виділіть клітинку або діапазон комірок, яким необхідно присвоїти ім'я.
Введіть в поле імені ім'я вибраної комірки або діапазону комірок.
При введенні імені діапазону комірок дотримуйтеся угод Excel про імена (зверніться до списку, наведеному вище в цьому розділі).
4. Натисніть.
іменовані константи
У деяких формулах застосовуються константи, наприклад 7,5-процентний податок або 10-відсоткова знижка. Константа - це число або текст, дата або час, введені непо-безпосередніх в формулу. Число, дата і час вводяться відповідно до правил, описаними в пункті, 0. Текстова константа, дата і час полягають в подвійні лапки /
Якщо ви не хочете вводити ці константи в осередку електронної таблиці, щоб згодом використовувати їх в формулах, створіть окремий набір имено-ванних констант, які будуть застосовуватися в формулах електронної таблиці.
Наприклад, щоб створити константу Податок із значенням 7,5%, виконайте наступні дії.
Виберіть команду Вставка / Имя / Прісвоіт' (Insert / Name / Create), щоб
відкрити діалогове вікно Присвоєння імені (Define Name).
У діалоговому вікні Присвоєння імені в поле Ім'я (Name) введіть ім'я кон-
константи (в нашому прикладі - Податок).
Клацніть на кнопці Додати (Add).
Клацніть на кнопці ОК, щоб закрити діалогове вікно.
Присвоївши константі ім'я, можна використовувати його в формулах електронної таблиці наступним чином.
1. Наберіть ім'я, присвоєне константі, в обраній галузі формули.
2. Вставте ім'я, призначене константі, виконавши команду Вставка / Имя / Вставити (insert / Name / Paste), а потім вказавши потрібного імені в діалоговому вікні Вставити ім'я (Paste Name).
висновок
Розглянувши різні функції створення та використання формул, можна з упевненістю сказати, що можливості Excel досить різноманітні. Створеним формулами присвоюються імена, які використовуються в подальшому як спрощена і більш зручна формулювання.
Різні посилання допомагають удосконалити призначені для користувача функції. Тим самим, знання і більш глибоке вивчення формул покращують і прискорюють роботу користувача.
покажчик
додаток
1. Написати формули, заповнення діапазону А1: A100 (лист1) рівномірно розподіленими випадковими числами з відрізка [-3,55; 6,55], а діапазону B1: B100 (лист1) випадковими цілими числами з відрізка [-20; 80]. Скопіювати значення зазначених діапазонів на Аркуш2. збільшивши вдвічі значення другого діапазону. На Лісте3 написати формули:
2. знаходження середнього арифметичного, побудованих діапазонів Ліста2;
3. максимального і мінімального елементів, побудованих діапазонів Ліста2;
4. суми трьох найменших елементів, побудованих діапазонів Ліста2;
позитивного елемента, який найчастіше зустрічається в побудованих діапазонах Ліста2
Для заданого діапазону комірок робочого листа Excel.
Написати формули обчислюють:
Суму елементів діапазону, значення яких потрапляють в відрізок [-5; 10].
Кількість елементів діапазону великих деякого числа, записаного в осередку робочої таблиці (наприклад, з осередку D9).
Кількість елементів діапазону, значення яких менше середнього значення елементів діапазону
Список використаної літератури
Створення і використання формул в excel