Ms Excel використовує тип даних. Типи даних, формули та функції в Ms Excel. Синтаксис оператора Type

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

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

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

У свою чергу, константи діляться на п'ять груп:

• Текст;
• Числові дані;
• Дата і час;
• Логічні дані;
• Помилкові значення.

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

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

Текстовий тип містить символьні дані і не розглядається Excel як об'єкт математичних обчислень. Це інформація для користувача, а не для програми. Текстом можуть бути будь-які символи, включаючи цифри, якщо вони відповідним чином відформатовані. У мові DAX цей вид даних належить до малих значень. Максимальна довжинатексту становить 268435456 символів в одному осередку.

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

Числові дані

Для безпосередніх обчислень використовуються числові дані. Саме з ними Excel робить різні математичні операції (додавання, віднімання, множення, розподіл, зведення в ступінь, вилучення кореня і т.д.). Цей тип даних призначений виключно для запису чисел, але може містити допоміжні символи (%, $ та ін.). Щодо нього можна використовувати кілька видів форматів:

• Власне числовий;
• Відсотковий;
• Грошовий;
• Фінансовий;
• Дробний;
• Експонентний.

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

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

дата і час

Ще одним типом даних є формат часу та дати. Це саме той випадок, коли типи даних та формати збігаються. Він характеризується тим, що з його допомогою можна вказувати на аркуші та проводити розрахунки з датами та часом. Примітно, що з обчислення цей тип даних приймає добу за одиницю. Причому це стосується не лише дат, а й часу. Наприклад, 12:30 розглядається програмою, як 0,52083 діб, а вже потім виводиться в комірку у звичному для користувача вигляді.

Існує кілька видів форматування для часу:

• ч:мм:сс;
• ч:мм;
• ч:мм:сс AM/PM;
• ч:мм AM/PM та ін.

Аналогічна ситуація і з датами:

• ДД.ММ.РРРР;
• ДД.МММ
• МММ.РР та ін.

Є й комбіновані формати дати та часу, наприклад ДД:ММ:РРРР ч:мм.

Також потрібно врахувати, що програма відображає як дати лише значення, починаючи з 01.01.1900.

Логічні дані

Досить цікавим є тип логічних даних. Він оперує лише двома значеннями: «ІСТИНА»і «БРЕХНЯ». Якщо перебільшувати, це означає «подія настала» і «подія не настала». Функції, обробляючи вміст осередків, які містять логічні дані, виробляють ті чи інші обчислення.

Помилкові значення

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

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

Формули

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

Обов'язковою умовою, щоб програма сприймала вираз як формулу, є наявність перед ним знака одно (=) .

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

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

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

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

1. ЧисловіЗначення - це власне число, дата і час.

Наприклад, форми уявлення:

чисел - 3,15 ; -3456; -3,445E+01; 34,5E+0

часу - 11:40:35 ; 11:40; 11:40:35 AM

Введеннячислових даних: активізувати комірку, ввести числове дане та натиснути Enter.

Редагуваннячислових даних у комірці - виділити комірку, клацнути у правій частині рядка формул і виправити з клавіатури зміст.

Форматуваннячислових даних осередку – виділити осередок, виконати команди меню Формат, Осередки.У діалоговому вікні вибрати числовий формат і натиснути ОК. Можна вказати кількість знаків числа після коми тощо. Аналогічно можна вибирати вид подання (формат) дати та часу.

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

2. Текст. Вводиться з клавіатури аналогічно до редактора Word.

Автоматичнийвведення - запам'ятовування попередніх слів та відновлення за першими літерами при новому введенні (щоб зняти режим автовведення – виконати команди: Сервіс, Параметри, Виправлення,зняти прапорець Автозаповненнязначень осередків).

Автопідбірширини стовпців- виділити набрану таблицю і виконати такі команди меню: Формат, Стовпець, Автопідбірширини.

Вирівнюваннятексту – аналогічно текстовому редактору, І.О. кнопки форматування - вирівнювання зліва, справа, по центру та центрування.

створіннясписку автозаповнення - виконати команди меню Сервіс, Параметри, Списки.Вибрати Новий списокта ввести свій список у вікні Елементисписок. Далі клацнути кнопку Додати. Наприклад, ми внесли назви міст: Одеса, Миколаїв, Херсон тощо. Тепер записавши в комірку слово Одесаі потягнувши за маркер вправо, отримаємо в сусідніх осередках інші міста.

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

Приклад формули: = А7 + 5,45 * COS (A1) + СУМ (В1: В10).Звичайно, що для виконання цієї формули в осередках А7, А1, В1: В10 повинні бути записані числа.

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

Для відображення в осередках всіх записаних формул, потрібно виконати команди меню: Сервіс, параметри.Клацнути вкладку Видта встановити прапорець у полі Формули.Зняття прапорця Формули, Приведе до відображення в осередках результатів обчислення формул. Τᴀᴋᴎᴎᴩᴀᴈᴏᴍ, формула має два види відображення в осередку - формульнеі результативне.

Редагуванняформули – активізувати комірку з формулою та клацнути мишкою у правій частині рядка формул. Виправити формулу і натиснути Enter (редагувати можна і в самій клітинці – двічі клацнувши її).

Типи даних у Excel та їх введення – поняття та види. Класифікація та особливості категорії "Типи даних у Excel та їх введення" 2017, 2018.

Основні типи даних.

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

Числа в електронних таблицях Excel можуть бути записані у звичайному чисельному або експонентному форматі, наприклад 195,2 або 1,952Е+02. За замовчуванням числа вирівнюються в комірці праворуч. Це тим, що з розміщення чисел друг під одним зручно мати вирівнювання за розрядами (одиниці під одиницями, десятки під десятками тощо.)

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

Формула повинна починатися зі знака рівності і може включати числа, імена осередків, функції і знаки математичних операцій. Але до формули не може входити текст. Наприклад, формула =А1+В1 забезпечує додавання чисел, що зберігаються в осередках А1 і В1, а формула =С3*9 - множення числа, що зберігається в осередку С3, на 9.

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

Формат даних.

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

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

Експоненційний формат застосовується, якщо число, що містить велику кількість розрядів, не вміщується в комірці (наприклад 3500000000, записуватиметься в 5,00Е + 09).

Так як програма Excelпризначена для обробки числа, важливу роль відіграє правильне налаштуванняїх формату. Для людини число 10 – це просто одиниця та нуль. З точки зору Excel ці дві цифри можуть нести абсолютно різну інформаціюзалежно від цього, чи позначає вона кількість працівників підприємства, грошову величину, відсоткову частину цілого чи фрагмента заголовка " 10 провідних фірм " . У всіх чотирьох ситуаціях це число має відображатися та оброблятися по-різному. Excel підтримує такі формати:

• - Загальний (General) - призначається автоматично, якщо не вказується формат спеціально.
• - Числовий (Number) – найбільший загальний спосіб представлення чисел
• - Грошовий (Currency) - грошові величини
• - Фінансовий (Accounting) - грошові величини з вирівнюванням по роздільнику цілою та дробовою частиною
• - Дата (Data) - дата чи дата та час
• - Час (Time) - час або дата та час
• - Процентний (Percentage) - значення комірки, помножене на 100 із символом % наприкінці
• - Дробний (Fraction) - раціональні дроби з чисельником та знаменником
• - експоненційний (Scientific) - десяткові дробові числа
• - Текстовий (Text) - текстові дані відображаються так само, як вводяться та обробляються рядки, незалежно від їх змісту
• - Додатковий (Special) - формати для роботи з базами даних та списками адрес
• - Замовний (Custom) - формат, що настроюється користувачем

Вибір формату даних

• 1. Ввести команду Формат-Комірка
• 2. На діалоговій панелі Формат Комірок виберіть вкладку Число.
• 3. У списку Числові формати: вибрати найбільш підходящий формат.

Вкладка Вирівнювання визначається:

• 1. Вирівнювання - спосіб вирівнювання даних в комірці по горизонталі (за лівим або правом краєм, за значенням, по центру виділення, по центру, по ширині, із заповненням) або по вертикалі (по нижньому або верхньому краю, по центру або висоті);
• 2. Відображення - визначає, чи можна переносити в осередку текст, за словами, дозволяє чи забороняє об'єднання осередків, задає автопідбір ширини осередків.
• 3. Орієнтація тексту

Вкладка Шрифт - змінює шрифт, зображення, розмір, колір, підкреслення та ефект тексту у виділених осередках;

Вкладка Кордон - створює рамки (обрамлення) навколо виділеного блоку осередків;

Вид - дозволяє задати зафарбування осередку (колір та візерунок);

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

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

Ми ж виділяємо 4 основні групи:

• Текстовий;
• Числовий;
• Дата і час;
• Загальна.

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

Дамо короткий описгрупам (для більш детального опису пройдіть посилання):

• Текстовий тип (Текстовий тип даних у Excel) – представляє інформацію як текст (рядок). Якщо число записано в комірку з цим типом, то програма вважає його рядком. Хоча з ним можна буде здійснити операції обчислення, застосувати його як число в деяких функціях буде неможливо.
• Числовий тип (Числовий тип даних в Excel) - визначає інформацію як число, внаслідок чого з ним можна проводити будь-які обчислення та застосовувати математичні функції. також в додаток Excelпропонує різні види відображення такого роду інформації.
• Формат дати і часу (Типи даних дата і час в Excel) - представляє інформацію або як дата, або час, або дата і час. За своєю суттю тип дати і часу є лише зручним способом подання числової інформації і тому дозволяє проводити математичні операції з ним. Цей типможе бути негативним.
• Загальний формат даних – використовується, коли не встановлено формат. Програма сама визначить, якого формату відносити ті чи інші дані.

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

Функція T в Excel виконує перевірку типу даних і повертає текстовий рядок, якщо в комірці, посилання на яку була передана як параметр, містяться текстові дані або текст був переданий у функцію явно (наприклад, Т(«будь-яке текстове значення»)).

Приклади використання функції Т в Excel

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

Вихідні таблиці:

Для вирішення використовуємо таку формулу:

Цей запис має такий зміст:

1. Функція ЯКЩО виконує перевірку значення, яке поверне функція Т;
2. Якщо осередок G2 не містить текстових даних, буде повернуто порожнє значення«» і спрацює функція ТЕКСТ, яка перетворює числовий тип даних у текстовий та запише отримане значення в комірку B2;
3. Якщо в осередку G2 містяться текстові дані, вони будуть перенесені до осередку B2 без змін.

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

Щоб переконатися, що дана таблицямістить лише текстові дані, знову скористаємося функцією Т:

Тобто перетворення на текстові дані пройшло успішно.

Як визначити формат осередку в Excel?

Приклад 2. У таблиці Excel знаходиться захищений аркуш з таблицею, де містяться дані про адресу клієнта. Імовірно, номер будинку та номер квартири є числовими даними, отриманими в результаті перебору рядка з використанням функцій ПРАВСІМВ, ЛЕВСІМВ, ЗНАЙТИ та ЗНАЧЕНИЙ. Остання перетворює дані на формат Число (якщо це можливо). Визначити формат даних допоможе функція Т, а перетворити текстовий формат – ТЕКСТ.

Таблиця на захищеному листі (формули не видно, оскільки стоїть захист):

Дані у вигляді тексту необхідно ввести до наступної таблиці:

У осередку B2 введемо таку формулу:

Опис функцій формули:

1. Т(захищений!B3)="" – умова, що перевіряється функцією ЯКЩО. Результат виконання функції Т перевіряється на порожнє значення "".
2. ТЕКСТ(захищений!B3;"#") – функція, що перетворює дані з комірки захищений!B3 у текстовий тип даних.
3. захищений! B3 – отримання даних із комірки, якщо умова Т(захищений! B3)="" - БРЕХНЯ.

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

Як визначити тип даних у Excel?

В Excel можна визначити тип даних, що містяться в певному осередку, використовуючи функцію ТИП, яка повертає числове значення, що відповідає одному з типів, що підтримуються (наприклад, 1 - Число, 2 - Текст, 4 - Логічне значення і т. д.). Визначимо типи даних у деякій таблиці, і якщо вони є текстовими, перетворимо їх з допомогою функції ТЕКСТ.

Вихідна таблиця:

Введемо в комірці C3 таку формулу:

Опис аргументів:

1. ТИП(B3)<>2 – умова перевірки; 2 – тип текстових даних;
2. ТЕКСТ(B3;"") – функція, що повертає текстове значення з комірки B3, якщо результат перевірки умови – ІСТИНА;
3. B3 – незмінене значення комірки, якщо результат умови перевірки – БРЕХНЯ.

Аналогічно заповнимо наступні осередки стовпця C. У результаті отримаємо:

Щоб переконатися, що кожен осередок містить текст, використовуємо функцію Т:

Функція Т в Excel та особливості її синтаксичного запису

Функція Т має наступний синтаксис:

Т(значення)

Єдиний параметр значення є обов'язковим для заповнення. Він використовується для передачі даних, що перевіряються.

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

Примітки 2:

1. Якщо функція Т приймає на вхід текстові дані (рядок тексту, комірка, що містить текст), вона поверне ці дані.
2. Функція Т повертає порожній рядок, якщо вона приймає на вхід числові дані, логічне значення, посилання на комірку, в якій немає тексту. Приклади:

• Результатом виконання функції =Т(293) буде порожній текст «», оскільки прийняте на вхід значення є даними числового типу;
• Наступний запис =Т(«функція Т працює») поверне рядок тексту «функція Т працює» в комірку, в якій дана функціязаписано;
• Якщо в комірку ввести запис типу =Т(текст), то результатом виконання функції буде код помилки #ІМ'Я? Тому при явній передачі параметра функцію його значення необхідно поміщати в подвійні лапки.
• Результатом виконання функції =Т(A1) є текст, що міститься в осередку A1 або порожнє значення «», якщо в цій клітинці знаходяться дані іншого типу.