Как да направя числов формат в excel. Автоматична промяна на формата на клетката след въвеждане на данни

Промяната на формата на клетката в Excel ви позволява да организирате данните на лист в логична и последователна верига за професионална работа. От друга страна, неправилното форматиране може да доведе до сериозни грешки.

Съдържанието на клетка е едно, но начинът, по който съдържанието на клетките се показва на монитора или се отпечатва е друго. Преди да промените формата на данните в клетка на Excel, трябва да запомните едно просто правило: "Всичко, което клетката съдържа, може да бъде представено по различни начини и представянето на дисплея на данните зависи от форматирането." Това е лесно за разбиране, ако се покаже с пример. Вижте как можете да използвате форматиране, за да покажете числото 2 по различни начини:

Повечето потребители на Excel използват изключително стандартни инструментиформатиране:

• бутони на началния панел;
• готовите шаблони за клетъчен формат, налични в диалоговия прозорец, се отварят с помощта на комбинацията от горещи клавиши CTRL + 1.

Формати на данни, въведени в клетките на електронната таблица

Попълнете диапазона от клетки A2:A7 с числото 2 и форматирайте всички клетки, както е показано на фигурата по-горе.

Решението на проблема:

Какви опции предоставя диалоговият прозорец Форматиране на клетки? Функциите на всички инструменти за форматиране, които разделът Начало съдържа, могат да бъдат намерени в този диалогов прозорец (CTRL+1) и дори повече.

В клетка A5 използвахме финансовия формат, но има и паричен формат, те много често се бъркат. Двата формата се различават по начина, по който се показват:

• финансовият формат, когато показва числа, по-малки от 0, поставя минус от лявата страна на клетката, а паричният формат поставя минус пред числото;
• валутният формат по подразбиране показва отрицателни стойности с червен шрифт (например въведете стойността -2p в клетката и валутният формат ще бъде зададен автоматично);
• във финансов формат се добавя 1 интервал след намаляване на валутата при показване на стойности.

Ако натиснете комбинацията от клавишни комбинации: CTRL + SHIFT + 4, тогава на клетката ще бъде присвоен паричен формат.

Що се отнася до датата в клетка A6, тук си струва да споменем правилата на Excel. Форматът на датата се счита за поредица от дни от 1 януари 1900 г. Тоест, ако клетката съдържа стойност - числото 2, тогава това число във формата на датата трябва да се показва като 01/02/1900 и т.н.

Времето за Excel е стойността на числата след десетичната запетая. Тъй като имаме цяло число в клетка A7, времето се показва съответно там.

Формат на дата с час в Excel

Нека форматираме таблицата с данни, така че стойностите в редовете да се показват според имената на колоните:

В първата колона форматите вече съответстват на нейното име, така че отидете на втората и изберете диапазона B3:B7. След това натиснете CTRL + 1 и в раздела "Число" посочете часа и в секцията "Тип:" изберете метода на показване, както е показано на фигурата:

Правим същото с обхватите C3:C7 и D3:D7, като избираме подходящите формати и типове показване.

Ако клетката съдържа стойност, по-голяма от 0, но по-малка от 1, тогава форматът на датата в третата колона ще бъде показан като: 0 януари 1900 г. Докато в четвъртата колона датата вече се показва по различен начин поради различен тип (1904 системи за дата, вижте по-долу за подробности). И ако броят

Забележете как се показва времето в клетки, които съдържат дробни числа.

Тъй като датите и часовете в Excel са числа, е лесно да се извършват математически операции с тях, но ще разгледаме това в следващите уроци.

Две системи за показване на дата в Excel

Има две системи за показване на дати в Excel:

1. Датата 1 януари 1900 г. съответства на числото 1.
2. Датата 1 януари 1904 г. съответства на числото 0, а 1 вече е съответно 02.01.1904 г.

Забележка. За да се показват всички дати по подразбиране според системата от 1904 г., можете да направите съответните настройки в параметрите: „Файл“ - „Опции“ - „Разширени“ - „При преизчисляване на тази книга:“ - „Използване на 1904 г. система за дата”.

Ясно даваме пример за разликата в показването на датите в тези две системи на фигурата:

Помощта на Excel изброява минималните и максималните числа за дати и в двете системи.

Настройките за промяна на системата за дата се отнасят не само за конкретен лист, но и за цялата програма. Ето защо, ако няма спешна нужда да ги промените, тогава е по-добре да използвате системата по подразбиране - 1900. Това ще избегне сериозни грешки при извършване на математически операции с дати и часове.

Excel има много вградени числови формати, но ако никой от тях не удовлетворява потребителя, тогава можете да създадете свой собствен числов формат. Например числото -5,25 може да се покаже като дроб -5 1/4 или като (-)5,25 или 5,25- или като цяло в произволен формат, например ++(5)rub.### 25коп. Форматите на паричните суми, процентите и експоненциалното представяне също се вземат предвид.

Можете да използвате много формати за показване на число. Според руските регионални стандарти ( Бутон Старт/ Контролен панел/ Регионални и езикови опции) номерът обикновено се показва в следния формат: 123 456 789.00 (цифрите са разделени с интервали, дробната част е разделена със запетая). В EXCEL можете сами да измислите формата за показване на число в клетка. Има подходящ механизъм за това - персонализиран формат. Всяка клетка може да бъде настроена на определен числов формат. Например числото 123 456 789,00 има формата: # ##0,00;-# ##0,00;0

Персонализираният числов формат не засяга изчисленията, променя се само показването на числото в клетката. Персонализираният формат може да бъде въведен чрез диалоговия прозорец Формат на клетка, раздел Номер, (всички формати) чрез натискане CTRL+1. Въведете формата в полето Тип, след като изтрих всичко от него.

Нека започнем със стандартния цифров формат, споменат по-горе # ##0,00;-# ##0,00;0
В бъдеще ще се научим как да го променяме.

Точка и запетая отделя части от формата: формат за положителни стойности; за отрицателни стойности; за нула. За описание на формата се използват специални знаци.

• Знакът за паунд (#) означава произволно число.
• Интервал в конструкцията # ##0 дефинира цифрата (интервалът показва, че има 3 цифри в цифрата). По принцип може да се напише # ###, но е необходима нула, за да се покаже 0, когато цялата част е нула и има само дробна част. Без нула (т.е. # ###) числото 0.33 ще се покаже като .33.
• Следващите 3 знака, 00 (запетая и 00) определят как ще се показва дробната част. Въвеждането на 3.333 ще покаже 3.33; при влизане 3.3 - 3.30. Естествено, това няма да повлияе на изчисленията.

Втората част от формата е за показване на отрицателни числа. Тези. Можете да зададете различни формати за показване на положителни и отрицателни числа. Например, с формат # ##0.00;-###0;0, числото 123456.3 ще се покаже като 123456.30, а числото -123456.3 като -123456. Ако премахнете минус формата, тогава отрицателните числа ще се показват БЕЗ МИНУС.

Има и 4-та част - тя определя изхода на текста. Тези. ако е в клетка с формат # ##0.00;-# ##0.00;0;"Въвели сте текст"въведете текстова стойност, тя ще се покаже Вие сте въвели текст.

Например форматът 0;\0;\0;\0 ви позволява да замените всички отрицателни, равни на нула и текстови стойностидо 0. Всички положителни числа ще бъдат показани като цели числа (с нормално закръгляване).

Не е необходимо да включвате всички части на формата (секция) в генерирания числов формат. Ако са посочени само две секции, първата секция се използва за положителни числа и нули, а втората секция се използва за отрицателни числа. Ако е дадена само една секция, всички числа ще имат този формат. Ако искате да пропуснете част от кода и да използвате секцията, която го следва, трябва да оставите точка и запетая във вашия код, която завършва секцията, която пропускате.

Нека разгледаме потребителските формати с конкретни примери.

Наличието на такъв усъвършенстван персонализиран формат е наследство от предишни версии EXCEL, който нямаше . Форматите, които променят цвета на шрифта и фона на клетка в зависимост от големината на стойността, са по-добре внедрени условно форматиране.

| Повече ▼ сложни примериперсонализираното форматиране е предоставено в примерния файл.

Не мога да препоръчам използването на персонализиран формат твърде често. Първо, 90% от вградените формати са достатъчни, те са ясни за всички и лесни за използване. Второ, като правило персонализиран формат може значително да промени показването на стойност в клетка от самата стойност (в противен случай защо иначе би бил необходим персонализиран формат?) Например числото 222 може да се покаже като "ABCD333- 222". Можете да забравите и да объркате, че клетката не съдържа текст, а не само число. И това вече е възможна причинагрешки. Претеглете плюсовете и минусите, преди да използвате сложен персонализиран формат.

Форматът на клетката включва параметри, които определят: » вида на представяне на числовата стойност на клетката; » подравняване и ориентация на съдържанието на клетките; » вид на шрифта; « рамкиране на клетки; запълване (фон) на клетката. Освен това можете да зададете настройките за защита на данните, записани в клетката. Клетките обикновено се форматират чрез промяна на отделните

параметри. Преди да можете да промените формата на клетка, трябва да поставите табличен курсор върху нея. Ако искате да зададете формата за няколко клетки едновременно, първо трябва да изберете тези клетки.

Стандартни формати Excel има шест стандартни формата на клетки (набори от опции), които могат да се използват във всички документи. нова масавсички клетки имат еднакви стандартен форматПо подразбиране. Този формат се нарича нормален. Има следните опции:

Изобразената стойност повтаря истинската стойност;

Хоризонталното подравняване зависи от вида на данните;

Вертикалното подравняване се извършва на долния ръб;

Текст без пренасяне на думи;

Ориентацията на показаната стойност е хоризонтална;

Съдържанието се показва със стандартен шрифт без акценти или ефекти;

Няма граница около клетките;

Няма пълнеж.

Останалите стандартни формати се различават от нормалния формат само чрез показване на числа в клетки:

Финансови (или разделени) - числото се закръгля до втори знак след десетичната запетая, например числото 43,569 се представя като 43,57;

Financial(0) - числото се закръгля до най-близкото цяло число, например числото 43.569 се представя като 44;

Паричен - числото се закръгля до две цифри след десетичния разделител и се добавя знак за валута, например числото 43,569 на "руски" компютър е представено като 43,57 рубли;

Паричен (0) - числото се закръгля като цяло и се добавя знак за валута, например числото 43.569 се представя като 44 рубли;

Процент - ако след настройка на формата в клетката е въведено число от 0 до 1, то се умножава по 100, след което числото (независимо от стойността) се закръгля до цяло число и към него се добавя знак %, т.к. в резултат на това, например, числата 0,43569 и 43,569 ще бъдат представени еднакво 44%. Ако в диалоговия прозорец Опции в раздела Редактиране изключите Автоматично въвеждане, процентен превключвател, тогава всички числа ще бъдат умножени по 100, а не само от 0 до 1. В резултат на това, например, числата 0,43569 и 43,569 ще бъдат представени като 44% и 4357%. Същото ще се случи, ако този превключвател е активиран, но числата са били въведени в клетките, преди да им бъде зададен процентен формат.

Когато използвате финансови и парични формати, разделителят между цифрите на единици, хиляди, милиони и т.н. се задава в настройките на Windows: раздел Числа в програмата Език и стандарти в контролния панел. Обикновено това е пространство. При Настройка на Windowsзадава се и знакът на валутната единица: раздел Валута в програмата Език и стандарти на Контролния панел. За рублата това обикновено е r. Използваният разделител за хиляди може да се промени, без да се променя обща настройка Windows.

За да направите това, в диалоговия прозорец Опции, в раздела Международни, в групата Числа, изключете превключвателя Използване на системни разделители и въведете различен разделител в полето Разделител на цифри. Понякога като разделител се използва запетая или апостроф. За прилагане на стандартни формати могат да се използват съответните инструменти на панела Форматиране и клавишните комбинации, както и диалоговия прозорец Стил. Представяне на числова стойност Както вече беше отбелязано, стойностите, след като бъдат въведени в клетките, имат формата, зададена по подразбиране.

Въпреки това е възможно да промените външния вид на клетките, както желаете. Можете да използвате лентата с инструменти за форматиране и клавишните комбинации, за да промените индивидуалните настройки. Въпреки това диалоговият прозорец Форматиране на клетки предоставя най-пълните опции за форматиране на клетки. За да извикате прозореца Форматиране на клетки, направете едно от следните неща:

Изпълнете команда Клетки...(Формат);

Изпълнете Форматиране на клетки... контекстно менюклетки (избрани клетки);

Натиснете комбинацията Ctrl + 1.

В раздела Число на прозореца Формат на клетка списъкът Числови формати съдържа имената на групите представяния на числови стойности, налични в Excel. По подразбиране списъкът е настроен на Общи, което показва числови стойности, въведени от потребителя. След като изберете различна стойност, разделът се показва допълнителни опции, с помощта на които се задават параметрите на появата на числови стойности.

Например, когато изберете Числов, се появява следното:

Поле Брой десетични знаци - задаване на броя знаци след десетичния разделител;

Превключвател за разделител на хиляди - активирайте разделителя на цифрите (интервал) между групи от цифри на единици, хиляди, милиони и др.;

Списък Отрицателни числа - избор на вида на представяне на отрицателни числа. Когато избирате формат, е удобно да използвате полето Пример, което се променя в съответствие с промените в опциите на раздела. Ако стойността (всички формати) е избрана в списъка Числови формати, тогава се появява списъкът Тип, който включва всички налични числови формати, които обаче са написани като кодове ( символи). След като се справи с тези кодове, потребителят в полето Тип може самостоятелно да задава форматите.

Така например в кодовете на форматите се използва следната нотация:

Незадължителна цифра, т.е. ако няма цифра на мястото на знака # в числото, тогава на това място не се показва нищо;

Задължителна цифра, т.е. ако няма цифра на мястото на знака 0, тогава на това място се показва нула (обикновено извеждането на нули след десетичния разделител е зададено по този начин, например форматът 0.000 задава задължителното показване от три знака след разделителя);

Задължително място, т.е. ако е на мястото на знака? в числото няма цифра, тогава на това място се показва интервал (обикновено това гарантира подравняването на числата в една колона, например форматът ?????0.00 задава подравняването към десетичния разделител на всички числа, които има до шест цифри преди разделителя); h, hh, m, mm, s, ee, D, DD часовете, минутите, секундите и дните от месеца h:m ще изглеждат като 6:7, а във формат hh:mm - 06:07); M, MM, MMM, MMMM месец (например февруари в различни формати ще изглежда като 2.02, фея, февруари); IT, ГГГГ година (записани съответно с 2 или 4 цифри).

Разделителите във форматите за дата и час се определят от настройките, посочени в настройките на Windows:

разделите Дата и Час в програмата Език и стандарти в контролния панел. | Повече ▼ подробна информацияотносно нотацията, използвана в кодовете, можете да намерите в помощна система Excel. След това създаденият от потребителя формат може да бъде изтрит чрез щракване върху бутона Изтриване. Когато променяте формата на числовите стойности, техните истински стойности (въведени или изчислени по формули) не се променят, т.е. например, без значение колко знака след десетичната запетая се показват, всички налични знаци ще бъдат използвани при изчисленията на формулата.

С други думи, настройката на нов формат само променя външен видчислова стойност, без да променя стойността си. В същото време е възможно да се зададе такъв режим, при който точността на истинските стойности винаги ще бъде равна на точността на представянето на числата на екрана. За да направите това, в диалоговия прозорец Опции, в раздела Изчисления, в групата Опции за книга, включете превключвателя за точност на екрана. Тази настройка ще се прилага за всички таблици в книгата.

За задаване на формата на числовите стойности може да се използва панелът Форматиране, в който са разположени следните инструменти:

Паричен формат Задайте стандартния формат на Паричен;

Процентен формат Задайте формата по подразбиране на Процент;

Разделен формат Задава стандартния формат Финансови,

Увеличете битовата дълбочина, увеличете с един броя на знаците след десетичния разделител;

Намаляване на битовата дълбочина..;

намалете с един броя на знаците след десетичния разделител. Клавишни комбинации за задаване на формати на числови стойности:

Ctrl+Shift+" .... задайте формата на нормален;

CtrI+Shift+1 ... задава числовия формат на 0.00 с разделител на групи от цифри;

Ctrl+Shift+2.... задаване на формат на часа на hmm;

Ctrl+Shift+Z.... задайте формата на датата на DD. МММ.ГГ;

Ctrl+Shift+4.... задайте формата на Паричен;

Ctrl+Shift+5.... задаване на формат Процент;

CtrI+Shift+6.... задаване на формата на 0.00E+00 (експоненциален изглед). Ако в клетка е въведена числова стойност, но искате тя да бъде интерпретирана от програмата като текстова стойност, тогава за такова преобразуване в диалоговия прозорец Cell Shape в раздела Number, в списъка Numeric formats изберете Text стойност.

При попълване на листове данни в ексел, никой не може веднага да изпълни всичко красиво и правилно от първия опит.

В процеса на работа с програмата постоянно трябва нещо да се променя, редактира, изтрива, копира или премества. Ако в клетката са въведени грешни стойности, естествено искаме да ги коригираме или изтрием. Но дори такава проста задача понякога може да създаде трудности.

Как да задам формат на клетка в Excel?

Съдържанието на всяка клетка на Excel се състои от три елемента:

1. Значение: текст, числа, дати и часове, булево съдържание, функции и формули.
2. Формати: тип и цвят на рамката, тип и цвят на запълване, как се показват стойностите.
3. Бележки.

И трите елемента са напълно независими един от друг. Можете да зададете формата на клетката и да не пишете нищо в нея. Или добавете бележка към празна и неформатирана клетка.

Как да променя формата на клетката в Excel 2010?

За да промените формата на клетката, извикайте съответния диалогов прозорец, като натиснете CTRL + 1 (или CTRL + SHIFT + F) или от контекстното меню след щракване с десния бутон на мишката: опцията „Форматиране на клетки“.

В този диалогов прозорец има 6 раздела:

Ако не сте постигнали желания резултат при първия опит, извикайте отново този диалогов прозорец, за да коригирате формата на клетката в Excel.

Какво форматиране е приложимо за клетки в Excel?

Всяка клетка винаги има някакъв формат. Ако няма промени, тогава това е форматът "Общ". Също така е стандартен Excel формат, в който:

• числата са подравнени вдясно;
• текстът е подравнен вляво;
• Шрифт Colibri с височина 11 точки;
• клетката няма граници и запълване на фона.

Изтриването на формат е промяна към стандартния "Общ" формат (без граници и запълване).

Струва си да се отбележи, че форматът на клетките, за разлика от техните стойности, не може да бъде изтрит с клавиша DELETE.

За да премахнете формата на клетките, изберете ги и използвайте инструмента "Изчистване на формати", който се намира в раздела "Начало" в секцията "Редактиране".

Ако искате да изчистите не само формата, но и стойностите, изберете опцията „Изчистване на всички“ от падащия списък на инструмента (гума).

Както можете да видите, инструментът за изтриване е функционално гъвкав и ни позволява да направим избор какво да изтрием в клетките:

• съдържание (същото като клавиша DELETE);
• формати;
• бележки;
• хипервръзки.

Опцията Изчисти всички съчетава всички тези функции.

Изтриване на бележки

Бележките, както и форматите, не се изтриват от клетката с клавиша DELETE. Бележките могат да бъдат изтрити по два начина:

1. Инструмент за изтриване: опция за изчистване на бележки.
2. Щракнете с десния бутон върху клетката с бележката и изберете опцията „Изтриване на бележка“ от контекстното меню, което се появява.

Забележка. Вторият начин е по-удобен. Когато изтривате няколко бележки едновременно, първо трябва да изберете всичките им клетки.

Трябва да промените формата по подразбиране на нещо друго

Формат на клетъчни данни

Формат на клетката по подразбиране („Общ“)

По подразбиране, след създаване на документ, всички клетки са в общ формат. Този формат има редица трикове:

• числата са подравнени вдясно, а текстът е подравнен вляво;
• ако, като промените ширината на колона, за да я направите по-малка от определена, тогава числото в клетките се заменя със знаците "#". Не е грешка. Това означава да направите колоната по-широка;
• ако числото е много голямо ("6000000000000") или много малко ("0.00000000000001"), то автоматично се преобразува в експоненциален (научен) формат ("6E+12" и "1E-14" съответно);
• десетичните дроби се закръглят при промяна на ширината на колоната. Например, ако напишете "3.1415", след това променете ширината, така че "5" вече да не пасва, клетката ще покаже "3.142".

Често трябва да добавите символ на валута, знак за процент към число в клетка, да зададете броя на десетичните знаци, да представите дата в определен формат и т.н.

Не добавяйте символи на парични единици ръчно!След това може да се окаже, че когато се опитате да използвате стойността от тази клетка във формулата, Excel ще даде грешка! Има начин да кажем на Excel, че клетките са в определен формат и той автоматично ще добави валутни символи (и не само) вместо нас.

Има 3 начина за промяна на формата на представяне на данни в клетки:

1. автоматично след въвеждане на определени данни в клетка, самият Excel ще промени формата на клетката;
2. с помощта на бутоните на лентата с инструменти за форматиране.
3. с помощта на прозореца "Форматиране на клетки";

След влизане определени последователностизнака, Excel автоматично форматира клетката. След това всичко впоследствие влезе в тази клетка числа в екселсе опитва да конвертира в този формат.

• дата. Ако напишете "1.2.3" или "1/2/3" в клетка, Excel ще го замени с "02/01/2003" (първия ден от втория месец на третата година). Ако напишете "1.2" или "1/2", Excel ще го замени с "01.Feb". В този случай форматът на клетката ще бъде автоматично преобразуван в "Дата";
• Процент. Ако напишете "1%" в клетка, форматът на клетката автоматично ще се промени на "Процент";
• време. Ако напишете "13:46:44" или "13:46" в клетка, форматът на клетката автоматично ще се промени на "Час";

Внимание!!!На различни компютристандартните формати за представяне на числа, валути, дати и часове може да се различават! Можете да ги конфигурирате по пътя "Контролен панел" -> "Регионални и езикови опции" -> раздел "Регионални настройки".

Промяна на формата на клетките с помощта на бутоните на лентата с инструменти за форматиране

В лентата с инструменти "Форматиране" има 5 бутона, с които можете бързо да промените формата на избраните клетки.

Описание на бутоните (отляво надясно):

• Паричен формат. Ще се използва валутата по подразбиране (вижте по-горе);
• Процентен формат. Ако клетката вече съдържа число, Excel ще го умножи по 100 и ще добави знак "%". Така е, защото 1 диня е "100%", а "0,7" диня е "70%";
• Разделен формат (числов формат). В този формат групи от цифри (стотици, стотици хиляди и т.н.) ще бъдат разделени с интервал и ще бъдат добавени 2 знака след десетичната запетая;
• Увеличете битовата дълбочина. Добавя един знак след десетичната запетая;
• Намалете битовата дълбочина. Премахва един знак след десетичната запетая.

Промяна на формата с помощта на прозореца за форматиране на клетки