Зміна формату даних в excel. Числове форматування в Excel

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

Припустимо, що для вмісту більшості осередків робочого аркуша використовується жовтий фон і шрифт Calibri розміром 14 пунктів з напівжирним шрифтом. Крім того, такі ж осередки є і на інших аркушах робочої книги. Змінимо форматування вмісту всіх цих осередків наступним чином: шрифт - Cambria; накреслення - напівжирне; розмір - 16 пунктів; колір букв - білий; колір осередків - чорний.

Щоб змінити форматування за допомогою функції пошуку і заміни, виконайте наступні дії.

Виділіть будь-яку клітинку (або декілька осередків, якщо потрібно виконати пошук в певному діапазоні) і виберіть команду Головна Редагування Знайти і виділити Замінити (Або натисніть Ctrl + H) Для відображення вікна Знайти і замінити.
Переконайтеся, що поля Знайти і Замінити на діалогового вікна Знайти і замінити порожні.
Натисніть кнопку формат поруч з полем знайти, Щоб відкрити діалогове вікно знайти формат. Якщо ця кнопка не видно, натисніть кнопку параметри.
У діалоговому вікні знайти формат ви можете вказати параметри форматування. Але, щоб прискорити цю процедуру, клацніть стрілку поряд з кнопкою формат, Виберіть команду Вибрати формат з осередку, А потім клацніть на осередку з тим форматуванням, яке ви хочете замінити.
Тепер натисніть кнопку формат поруч з полем замінити на для відображення діалогового вікна замінити формат.
І знову: або виберіть команду Вибрати формат з осередку і вкажіть клітинку з вмістом, на форматування якого потрібно замінити, або використовуйте вкладки діалогового вікна замінити формат, Щоб вказати бажане форматування. Перейдіть на вкладку шрифт і виберіть Cambria, вкажіть розмір 16, напівжирний шрифт і задайте білий колір тексту. на вкладці заливка виберіть чорний колір як колір фону комірки. Якщо ви встановили ті ж настройки, то ваше діалогове вікно Знайти і замінити має виглядати як на рис. 22.1.
Натисніть кнопку Замінити все.

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

В деяких випадках. наприклад ви хочете написати про те як купити меблевий тур в Китай, віза в Китай, квитки в КНР, то краще просто вибрати комірки певного формату. Для цього виконайте кроки з 1 по 4 і потім натисніть кнопку знайти всі. У діалоговому вікні з'явиться більше інформації про співпадаючих осередках.

Клацніть кнопкою миші на списку в нижній частині діалогового вікна, а потім натисніть клавіші Ctrl + A. Тепер, коли всі співпадаючі осередки виділені, ви можете форматувати їх як завгодно. Зверніть увагу, що, перетягуючи кордону стовпців, можна збільшувати їх ширину, а також сортувати вміст стовпчика, клацнувши на його заголовку.

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

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

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

Як правило, в таких осередках встановлено текстовий формат. Якщо змінити формат комірки на числовий (через формат ячеек або Головна / Число / Числовий формат), То змін не буде.

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

Після перетворення числа будуть вирівняні по правому краю комірки (якщо, звичайно, у форматі комірки встановлено Вирівнювання по горизонталі - за значенням).

Щоб перетворити відразу кілька значень - виділіть їх. У верхній осередку з'явиться жовтий значок - за допомогою нього перетворіть відразу все значення.

Інший спосіб групового перетворення чисел, збережених як текст, можна використовувати наступний підхід:

введіть в будь-яку вільну позицію число 1 ;
виділіть цей осередок і скопіюйте її значення в Буфер обміну(CTRL + C);
виділіть комірки, які містять числа збережені як текст;
оберіть спеціальна ставка (На вкладці Головна у групі Буфер обміну клацніть стрілку поруч з командою вставити, А потім виберіть у списку пункт спеціальна ставка);
оберіть помножити;
натисніть ОК.

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

Для EXCEL 2007: Додати Якщо до множення осередки мали текстовий формат осередків, то він буде збережений (числові значення все одно будуть перетворені, але будуть вирівняні по лівому краю, як текст). Змінити формат осередків на числовий можна стандартним способом через меню формат ячеек(натиснувши CTRL + 1).

Цей же прийом допоможе перетворити ДАТИ, збережені як текст в формат ДАТ (

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

В Excel існує багато інструментів для форматування тексту і осередки. У цьому уроці ви дізнаєтеся, як змінити колір і стиль тексту і осередків, вирівняти текст і задати особливий формат для чисел і дат.

форматування тексту

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

Щоб змінити шрифт:

Виділіть потрібні комірки.
Клацніть по стрілці меню, що випадає команди шрифт на вкладці Основне. З'явиться меню, що випадає.
Наводите курсор миші на різні шрифти. У виділених клітинках буде інтерактивно змінюватися шрифт тексту.
Виберіть потрібний шрифт.

Щоб змінити розмір шрифту:

Виділіть потрібні комірки.
Клацніть по стрілці меню, що випадає команди розмір шрифту на вкладці Основне. З'явиться меню, що випадає.
Наводите курсор миші на різні розміри шрифту. У виділених клітинках буде інтерактивно змінюватися розміром шрифту.
Виберіть потрібний розмір шрифту.

Також ви можете використовувати команди Збільшення розміру і Зменшити розмір для зміни розміру шрифту.

Щоб використовувати команди жирний шрифт, курсив, підкреслення:

Виділіть потрібні комірки.
Клацніть по команді жирний (Ж), курсив (К) або підкреслений (Ч) в групі шрифт на вкладці Основне.

Щоб додати кордону:

Виділіть потрібні комірки.
Клацніть по стрілці меню, що випадає команди межі на вкладці головна. З'явиться меню, що випадає.
Виберіть потрібний стиль кордонів.

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

Щоб змінити колір шрифту:

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

Вибір квітів не обмежується випадає меню. Виберіть Інші кольори внизу списку, щоб отримати доступ до розширеного вибору Кольори.

Щоб додати колір заливки:

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

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

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

Вирівняти текст по лівому краю: Вирівнює текст по лівому краю комірки.
Вирівняти по центру:Вирівнює текст по центру осередки.
Вирівняти текст по правому краю: Вирівнює текст по правому краю комірки.

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

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

По верхньому краю: Вирівнює текст по верхньому краю осередки.
Вирівняти по середині:Вирівнює текст по центру осередки між верхнім і нижнім краєм.
По нижньому краю: Вирівнює текст по нижньому краю осередку.

За замовчуванням, числа вирівнюються по правому і нижньому краях комірки, а слова і букви - по лівому і нижньому.

Форматування чисел і дат

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

Щоб задати формат чисел і дат:

числові Формати

Загальний - це формат будь-якої комірки за замовчуванням. Коли ви вводите в клітинку число, Excel запропонує найбільш підходящий на його думку формат числа. Наприклад, якщо ви введете «1-5», то в осередку відобразиться число в форматі Короткий формат дати, «1/5/2010».
числовий форматує числа в вид з десятковими розрядами. Наприклад, якщо ви введете в осередок «4», то в осередку відобразиться число «4.00».
грошовийформатує числа в вид з відображенням символу валюти. Наприклад, якщо ви введете в осередок «4», то в осередку число відобразиться у вигляді «».
Фінансовий форматує числа в вид схожий з Грошовим форматом, але додатково вирівнює символи валют і десяткові розряди в шпальтах. Цей формат зробить легше читання довгих фінансових списків.
Короткий формат дати форматує числа в вид M / Д / РРРР. Наприклад, запис Август 8, 2010 року буде представлена \u200b\u200bяк «8/8/2010».
Довгий формат дати форматує числа в вид День тижні, Місяць ДД, РРРР. Наприклад, «Понеділок, Август 01, 2010».
час форматує числа в вид ГГ / MM / СС і підпис AM або PM. Наприклад, «10:25:00 AM».
процентний форматує числа в вид з десятковими розрядами і знаком відсотка. Наприклад, якщо ви введете в осередок «0.75», то в ній відобразиться «75.00%».
дробний форматує числа в вид дробів з косою рисою. Наприклад, якщо введете в осередок «1/4», то в осередку відобразиться «1/4». Якщо ви введете в клітинку з форматом Загальний «1/4», то в осередку відобразиться «4-Jan».
експонентний форматує числа в експонентну запис. Наприклад, якщо введете в осередок «140000», то в осередку відобразиться «1.40E + 05». Зауважимо: за замовчуванням Excel буде використовувати експонентний формат для осередку, якщо в неї записано дуже велике ціле число. Якщо ви не хочете такого формату, то використовуйте Числовий формат.
текстовий форматує числа як текст, тобто в осередку все буде відображено саме так, як ви це ввели. Excel за замовчуванням використовує цей формат для осередків, що містять і числа і текст.
Ви з легкістю можете налаштувати будь-який формат за допомогою пункту Інші числові формати. Наприклад, ви можете змінити знак долара США на символ іншої валюти, вказати відображення ком в числах, змінити кількість відображуваних десяткових розрядів і т.д.

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

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

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

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

Варіанти числових форматів в Excel

На малюнку нижче представлені 11 варіантів числових форматів, які нам пропонує Excel.

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

1. Загальний

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

Наприклад, якщо Ви вводите "1-5", в осередку відобразиться число в короткому форматі дати "1/5/2010".

2. Числовий

У форматі Числовий значення відображаються з десятковими знаками. Наприклад, якщо Ви введете в клітинку число "4", то в ній відобразиться "4,00".

3. Грошовий

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

4. Фінансовий

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

5. Короткий формат дати

Короткий формат дати відображає вміст комірки у вигляді ДД.ММ.РРРР. Наприклад 8 серпня 2014 року виглядатиме як "08.08.2014".

6. Довгий формат дати

7. Час

Формат Час, дозволяє відображати вміст комірок у вигляді Ч: ММ: СС. Наприклад: 8:40:25 або 17:35:15.

8. Процентний

У процентному форматі значення в осередках множаться на 100 і виводяться зі знаком відсотка. За замовчуванням відображається два знака після коми, але Ви можете задати будь-яке інше кількість десяткових знаків. Наприклад, якщо ввести в клітинку число "0,75", то воно буде виглядати як "75,00%".

9. Дробний

Даний формат дозволяє відобразити вміст комірки у вигляді дробу. Наприклад, якщо ввести значення "1/4" в клітинку, яка має формат Загальний, То в результаті ми отримаємо дату " 01.апр". Якщо ж ввести точно таке ж значення в допомогу, що має формат дробний, То відобразиться дріб "1/4".

10. Експоненціальне

Даний формат відображає значення в осередках в експоненційному вигляді. Наприклад, якщо ввести в клітинку число "140000", то воно буде відображатися як "1,40E + 05".

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

11. Текстовий

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