Як перетворити цифри. Перетворення числа в текст і назад на Microsoft Excel

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

1. Виділіть стовпець

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

2. Натисніть цю кнопку

Кнопка "стовпці" зазвичай застосовується для розділення стовпців, але її також можна використовувати для перетворення стовпця тексту на числа. На вкладці Данінатисніть кнопку Текст по стовпцях.

3. Натисніть кнопку "Готово"

Інші кроки майстра потрібні поділу тексту на стовпці. Оскільки нам потрібно лише перетворити текст, натисніть кнопку Готово, і Excel перетворює комірки.

4. Вкажіть формат

Натисніть клавіші CTRL+1 (або +1 на Mac). Виберіть потрібний формат.

Примітка:Якщо ви бачите формули, які не виводять числові результати, можливо, включений параметр Показати формули. Відкрийте вкладку Формулита вимкніть параметр Показати формули.

Інші способи перетворення

Використання формули

За допомогою функції ЗНАЧЕНИЙ можна повертати числове значення тексту.

1. Вставте новий стовпець

Вставте стовпець поруч із осередками, що містять текст. У цьому прикладі стовпець E містить числа, які зберігаються у вигляді тексту. Стовпець F є новим стовпцем.

2. Застосуйте функцію ЗНАЧЕНИЙ

В одному з осередків нового стовпця введіть =ЗНАЧЕНИЙ()та вкажіть у дужках посилання на комірку, що містить числа, які зберігаються у вигляді тексту. У цьому прикладі це осередок E23.

3. Наведіть вказівник миші

Тепер необхідно заповнити формулу вниз. Ось як це зробити: Наведіть вказівник на правий нижній кут комірки, щоб він набув вигляду знака плюс (+).

4. Натисніть та перетягніть вниз

Натисніть і перетягніть вниз, щоб додати формулу до інших осередків. Після цього ви можете використовувати новий стовпець або скопіювати та вставити нові значення у вихідний стовпець. Ось як це зробити: Виділіть комірки з новою формулою. Натисніть клавіші CTRL+C. Клацніть перший осередок у вихідному стовпчику. На вкладці Головнаклацніть стрілку поруч із кнопкою Вставитита виберіть пункт Спеціальна вставка > Значення.

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

А в деяких напрямках програмування також часто використовуються числа в двійковому поданні. Мені принаймні доводиться мати з цим справу постійно.

Нерідко я створював власні функції для подібних перетворень. Це корисно для розвитку та навчання.

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

Функція BinStr

Функція BinStr Перетворює ціле число на рядок у двійковому поданні. Синтаксис:

BinStr(Val: Longint; cnt: byte) : shortstring; function binStr(Val: int64; cnt: byte) : shortstring; function binStr(Val: qword; cnt: byte): shortstring;

Функція binStr повертає рядок, який містить двійкове уявлення цілого числа, переданого через параметр Val. Кількість символів у рядку буде не більшою, ніж зазначено в параметрі cnt. Тобто у рядку будуть лише молодші (праві) біти числа. Щоб отримати повне уявлення великих цілих чисел, треба вказувати cnt = 32. Інакше старші розряди можуть потрапити у результат.

program binstrfunc; var x, y: integer; begin x:=100; y: = x * 245; (x, "-", binStr(x, 4)); //0100 WriteLn(x, "-", binStr(x, 8)); //01100100 WriteLn(y, "-", binStr(y, 16)); //0101111110110100 z:= (Int64); WriteLn(z, ":", #10#13, binStr(z, 64)); z: = (Int64); WriteLn(z, ":", #10#13, binStr(z, 64)); ; end.

Двійкове число у вигляді рядка

Навіщо взагалі може знадобитися виводити двійкове число у вигляді рядка?

Буває це не так часто. Але все-таки інколи така потреба виникає. Наприклад, якщо вам потрібно відстежити на екрані значення кожного біта в числі.

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

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

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

Перетворення числа Х із системи числення з основою q в систему числення з основою р здійснюється за правилом заміщення або за правилом розподілу-множення на основу системи числення.

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

приклад .111011,011 (2) = 1 2 4 +0 2 3 +1 2 2 +0 2 1 +l 2 0 +0 2 -1 +l 2 -2 +l 2 -3 = 59, 375.

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

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

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

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

приклад .75,35 (10) =1001011,01011… (2) .

Перетворювати дробові та цілі числа з двійкової системи числення до шістнадцятковоїі назад просто. Достатньо розділити двійкове число на групи по 4 біти ( бітомбудемо називати двійковий розряд), причому 4 біти починають формувати безпосередньо від точки, яка поділяє цілу та дробову частини числа. Отже, для цілої частини групи формуються від точки праворуч наліво, а для дробової частини зліва направо. Кожну групу по 4 біти, яку ще називають зошитом, можна перетворити на один шістнадцятковий розряд зі значенням від 0 доF(див. Табл.1.3).

Таблиця 1.3. Відповідність двійкових зошитів та шістнадцяткових цифр.

Двійковий зошит	Шістнадцяткова цифра	Десятичне значення

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

Рис.1.3. Перетворення дробового двійкового числа на шістнадцяткове.

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

Рис.1.4. Перетворення дробового шістнадцяткового числа A18.2E 16 в двійкове.

Цілі числа

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

1 й Метод. Послідовне віднімання з числа ступенів двійки.Найбільший ступінь двійки, менший, ніж число, віднімається від цього числа. Та ж операція проходить з отриманою різницею доти, доки різниця не стане рівною нулю (Рис. 1.5). Коли число розкладено за ступенями двійки, то двійкове значення виходить так: одиниці ставляться у тих позиціях, які відповідають отриманим ступеням двійки, а нулі – переважають у всіх інших позиціях.

Рис.1.5. Перетворення десяткового числа 197 на двійкове (1 й спосіб).

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

Мал. 1.6. Перетворення десяткового числа 197 на двійкове (2 й спосіб).

Перетворення цілих десяткових чисел у шістнадцятковіможна здійснювати послідовним розподілом числа на 16. Якщо результат розподілу більше 16, то розподіл проводиться ще раз. Частка, отримана останнім, буде старшим розрядом шістнадцяткового числа. Далі записуються залишки від розподілу у порядку їх одержання (Рис. 1.7). Числа, більші за 9, але менші за 16, що отримуються при розподілі, замінюються відповідними шістнадцятковими цифрами (табл. 1.1 або 1.3).

Мал. 1.7. Перетворення десяткового числа 1970 на шістнадцяткове.

Цілі двійкові числа у десятковіможна перетворювати двома способами.

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

Мал. 1.8. Перетворення двійкового числа на десяткове (1 й спосіб).

2 й Метод.Двійкове число записується вертикально по одному біту в рядку, починаючи з крайнього правого. Останній лівий біт знаходиться внизу. Десяткове число збирається знизу нагору. Нижній рядок це 1. Число з нижнього ряду переходить нагору, множачись на 2, і підсумовується з бітом поточного ряду. Результат виходить у верхньому ряду:

Мал. 1.9. Перетворення двійкового числа на десяткове (2 й спосіб).

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

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

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

Спосіб 1: форматування через контекстне меню

Найчастіше користувачі виконують форматування числових виразів у текстові через контекстне меню.

Спосіб 2: інструменти на стрічці

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

Дані перетворюються на текстовий варіант.

Спосіб 3: використання функції

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

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

Запускається вікно Майстри функцій. у категорії «Текстові»виділяємо пункт «ТЕКСТ». Після цього клацаємо по кнопці "OK".

Відкриється вікно аргументів оператора Текст. Ця функція має наступний синтаксис:
ТЕКСТ (значення; формат)

Вікно, що відкрилося, має два поля, які відповідають даним аргументам: «Значення»і «Формат».

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

В полі «Формат»Необхідно вказати варіант відображення результату. Наприклад, якщо ми введемо «0» , то текстовий варіант на виході відображатиметься без десяткових знаків, навіть якщо у вихіднику вони були. Якщо ми внесемо "0,0", то результат буде відображатися з одним десятковим знаком, якщо "0,00", то з двома, і т.д.

Після того, як всі необхідні параметри введені, клацаємо по кнопці "OK".

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

Тепер весь ряд заповнений потрібними даними. Але це ще не все. По суті, всі елементи нового діапазону містять формули. Виділяємо цю область і тиснемо на піктограму «Копіювати», який розташований у вкладці «Головна»на стрічці інструментів групі "Буфер обміну".

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

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

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

Конвертація тексту в число

Тепер давайте розберемося, якими способами можна виконати зворотне завдання, саме як перетворити текст на число в Excel.

Спосіб 1: перетворення за допомогою піктограми про помилку

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

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

Усі дані масиву будуть перетворені на вказаний вид.

Спосіб 2: конвертація за допомогою вікна форматування

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

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

Спосіб 3: конвертація за допомогою інструментів на стрічці

Перевести текстові дані на числові можна, скориставшись спеціальним полем на стрічці інструментів.

Значення в діапазоні будуть перетворені з текстових на числові.

Спосіб 4: застосування формули

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

До речі, для перетворення значень даним методом зовсім не обов'язково використовувати виключно подвійне множення на «-1». Можна застосовувати будь-яку іншу арифметичну дію, яка не веде до зміни значень (додавання або віднімання нуля, виконання зведення в перший ступінь і т.д.)

Спосіб 5: застосування спеціальної вставки

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

Спосіб 6: використання інструменту "Текст стовпцями"

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

Спосіб 7: застосування макросів

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

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