Нестандартні формати

Настроювані нестандартні формати створюються за допомогою вже знайомої нам вкладки Число діалогового вікна Формат осередків На ній необхідно вибрати категорію Всі формати (Custom) (при цьому вкладка приймає вид, зображений на рис 814) і ввести спеціальні коди форматування в полі Тип (Type) Excel додає все нові формати в кінець списку Тип, який починається з вбудованих форматів Щоб видалити будь-який користувацький формат, виділіть його в списку і натисніть кнопку Видалити (Delete) Вбудовані формати видалити неможливо

Створення нових форматів

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

1 Введіть число і надайте комірці вбудований формат, найбільш наближений до цільового Осередок залиште виділеної

2 На вкладці Число діалогового вікна Формат ячеек виберіть категорію Всі формати У полі Тип буде представлена ​​маска (кодова запис) формату, застосованого до осередку

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

Рис 814 Створіть новий формат на основі вже існуючого

Нехай нас цікавить формат для відображення дат в черговості: день тижня (назва), місяць (назва), день місяця і рік Почніть з введення в осередок самої дати (наприклад 25/07/04) і потім виділіть цю осередок У списку Числові формати виберіть категорію Всі формати Далі в поле Тип змініть запис цього формату на ДДДД, ММММ ДД, РРРР (З урахуванням пробілів і ком) і натисніть кнопку ОК Результат виконання цієї процедури показаний на рис 815

Рис 815 Ми створили новий формат дати, ввівши код формату в полі Тип

РАДА

При збереженні книги зберігаються і нові формати, але щоб перенести пользова-

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

Числові формати записуються за допомогою символів, наведених у табл 81

Таблиця 81 Символи, які використовуються для кодування форматів

Символ Призначення

0                Вставити контейнер (мітка) цифри Гарантує висновок заданого

кількості цифр ліворуч і праворуч від десяткової крапки Наприклад, у форматі 0,000 значення ,987 відображається як 0,987 Якщо ж застосовується маска 0,0000, воно постає як 0,9870 Коли число має більше цифр праворуч від десяткової точки, ніж задано міток О, це число округляється Так, з маскою 0,00 число ,987 відображається у вигляді 0,99, а у форматі 0,0 — округлюється до 1,0

Символ Призначення

?                Местозаполнітель цифри Цей символ діє так само, як О,

за винятком того, що замість незначних нулів з будь-якого боку від десяткової точки виводяться пробіли Ця мітка дозволяє вирівняти числа по десятковій крапці Наприклад, при застосуванні до чисел ,4 і 7,45 маски О,? десяткова точка буде мати один і той же відступ від краю осередку

#                Местозаполнітель цифри Діє подібно мітці 0, за тим винятком,

що незначущі нулі не виводяться, якщо число з будь-якого боку від десяткової точки має менше цифр, ніж задано міток # Цей символ вказує Excel, де потрібно поставити пробіли або інші символи-роздільники Наприклад, у форматі # # # # потрібно показати пробіл після кожної третьої цифри зліва від десяткової крапки

Десяткова крапка Показує, де проходить межа між цілою і дробовою частинами числа Якщо ліворуч від цього символу маска містить тільки мітки #, Excel починає все менші 1 числа з десяткової крапки Щоб уникнути цього, використовуйте в якості першої мітки цифри з коду местозаполнітель 0 Якщо ви

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

##0

%               Символ процентного формату У цьому форматі число множиться на 100 і завершується символом%

/                  Символ дрібного формату Позначає, що дробову частину слід

відображати у вигляді простого дробу Кількість міток цифр, супроводжуючих цей символ, визначає точність виведеного значення Наприклад, десяткова дріб 0,269 при присвоєнні їй формату # / округлятиметься до 1/4, але якщо задана маска # ??/??, та

ж дріб буде показана як 46/171

ПробілРоздільник груп розрядів Використовується для відділення сотень від тисяч,

тисяч від мільйонів і т д, якщо маска містить даний символ, укладений між мітками #, 0 або . Крім того, пробіл діє ще і як масштабуючий і округлює фактор Один пробіл наприкінці маски вказує Excel, що необхідно округляти і виводити числа з точністю до тисяч, два пробілу – з точністю до мільйонів Наприклад, формат

# # # # # # # (Один пробіл у кінці маски) передбачає відображення числа 4567890 як 4568, в той час як формат # # # # # # # (два пробілу в кінці маски) округлить це число і зовсім до 5

ЇЇ + її +Символи експоненціального формату Якщо маска містить хоча б один местозаполнітель 0 або # праворуч від символів Е-, Е +, еабо е +, Excel являє числа в експоненційному вигляді і вставляє символи Е або е в відображуване значення Число міток 0 або # праворуч від символів Е і е визначає мінімальну кількість цифр у показнику ступеня При використанні символів Еабо езнак показника ступеня виводиться тільки якщо він негативний, а при використанні символів Е + або е + знак показника ступеня відображається завжди

$-+/()      Звичайні символи Всі ці символи можна включати безпосередньо в маску

\                 Зворотна коса риса Додавайте зворотну похилу риску перед

будь-яким символом, що ви хочете вставити в маску, крім згаданих вище стандартних символів $-+/() \ і пробілу Зворотна коса риса при відображенні значення опускається Наприклад, код формату

# ##0&quot &quot\D-# ##0&quot &quot\C передбачає, що всі позитивні числа будуть виводитися з пропуском і буквою D в кінці числа, а всі негативні – з пропуском і буквою С Щоб вставити кілька

символів, вводите їх як літерал (див далі в таблиці)

продовженняœ

Таблиця 81 (Продовження)

Символ Призначення

Підкреслення Вставляє пропуск, рівний ширині наступного символу Наприклад, запис _) позначає додавання пробілу, по ширині рівного закриває скобці Цей символ зручний для вирівнювання числових значень у стовпцях

“ТекстРядок з букв Подвійні лапки діють аналогічно зворотного косою

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

*                 Символ повторення Наступний за ним символ повторюється доти,

поки стовпець не опиниться заповненим по всій ширині Використовуйте у записі формату тільки одну зірочку

@               Мітка тексту Якщо комірка містить текст, то у видимій значенні цей текст опиниться в тій позиції, в масці якій розташований символ @ Наприклад, якщо до осередку, що містить слово «Excel», застосувати маску &quotMicrosoft @. , В комірці зявиться текстове значення« Microsoft Excel »

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

Таблиця 82 Вбудовані коди форматів

Категорія Код формату

0,00

###0

# ##0,00

# ##0_# ##0

# # # 0_ [Червоний] – # # # 0

# ##0,00_)# ##0,00

# # # 0,00 [Червоний] – * # # 0,00 Грошовий

# # # # 0_р_; – # # # 0_р_

# # # 0_р_; [Червоний] – # # # 0_р_

# # # 0,00 _р_; – # # # 0,00 _р_

# # # 0,00 _р_; [Червоний] – * # # 0,00 _р_

Процентний 0% 0,00%

Експонентний 000Е +00

# # 0,0 Е +0

Дробний # ? /

#&quot &quot??/?

Категорія Код формату

Дата Див главу 15

Час Див главу 15

Текстовий @

Фінансовий (російська) _-* # # # 0р_- – * # # # 0р_- _-* – р_- _-@ _-

    * # # # 0_р__- – * # # # 0_р__- _-* – _Р_-_- _-@ _-# # # 0,00 р_- – * #

# # 0,00 р_- _-* -?? Р_- _-@ _-

                                     _-* # # # 0,00 _р__- – * # # # 0,00 _р__- _-* -?? _Р_-_- _-@ _-

Багатоваріантні формати

У межах кожного визначення формату можна створити повністю відрізняються один від одного варіанти для відображенняпозитивних, негативних, нульовихі текстових Значень Коди для кожного з варіантів в цьому випадку відокремлюються один від одного крапкою з комою і записуються в порядку, продемонстрованому на рис 816

Рис 816 Excel допускає запис форматів з чотирьох різних частин

Серед всіх вбудованих форматів всі чотири частини використовуються тільки у фінансових – це видно з рис 816 Нижче наводяться основні правила запису коду багатоваріантних форматів:

► якщо код записи містить тільки одну маску, Excel застосовує її до всіх по-

ложітельним, негативним і нульовим значенням

► якщо ж формат складається з двох частин, то перша застосовується до всіх позитивних і нульових значень, а друга – тільки до негативних

► третя секція формату відноситься тільки до нульових значень

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

РАДА

При бажанні на робочому листі можна приховати всі нульові значення У меню Сервіс виберіть команду Параметри та в діалоговому вікні перейдіть на вкладку Вид Зніміть прапорець Нульові значення (Zero Values) у розділі Параметри вікна (Window Options)

Припустимо, ви хочете створити нестандартний фінансовий формат, де в залежності від значення грошові суми виводилися б по-різному Приклад такого користувальницького формату наведено на рис 816 для нього був написаний наступний код:

“Перераховано: # # # 0,00 р; Кредит: – # # # 0,00 р; Чистий рахунок”; Зверніть увагу: @

Або, припустимо, ви створюєте робочий лист для обліку матеріальних цінностей і хочете, щоб всі числові значення в одному з стовпців відображалися у форматі Інв № ХХХХХХХ, як на рис 816 У цьому разі скористайтеся кодом:

“Інв № # # # – # # # #

Фарбування форматованих даних

Числові формати, що містять код для зміни кольору, впливають на колір значень виділених осередків Це дозволяє, наприклад, виділити окремі проміжні результати або підсумки Можна навіть створювати формати, в яких, наприклад, значення більше будь-якої величини будуть записуватися одним кольором, а менше цієї величини

– іншим

РАДА

Можна написати код, що забезпечує різну забарвлення залежно від самих значень, але набагато простіше скористатися для цього вбудованим засобом Excel – командою Умовне форматування (Conditional Formatting) меню Формат Докладніше про це розповідається в розділі «Умовне форматування» глави 9

Для зміни кольору значення вставте в код формату в початок відповідної маски назва нового кольору у квадратних дужках Наприклад, якщо потрібно створити формат Грошовий «синього кольору» з двома знаками після десяткової точки, треба редагувати один з наявних грошових кодів

_-$# ##0,00:_$# ##0,00

наступним чином:

[Синій] _-$ # # # 0,00 – $ # # # 0,00

В результаті застосування цього формату до аркуша позитивні і нульові значення будуть виділені синім кольором, а текстові і негативні залишаться чорними (за замовчуванням) А наведений нижче формат виводить всі позитивні значення синім кольором, негативні – червоним, нульові – жовтим, а текст – зеленим кольором:

[Синій] [Червоний} [Жовтий] [Зелений]

У масках форматів вживаються такі назви кольорів: Чорний (Black), Синій (Blue), Блакитний (Cyan), Зелений (Green), Рожевий (Magenta), Червоний (Red), Білий (White) і Жовтий (Yellow) Крім цього, колір можна задавати кодом колір, де п – Будь-який індекс з діапазону від 1 до 16 Excel в цьому випадку вибирає відповідний колір з поточної 16-кольоровий палітри Якщо вибраний колір виходить змішуванням декількох чистих кольорів, використовується найближчий чистий колір палітри

ПРИМІТКА-:-

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

утворює його змішуванням точок чистих кольорів Отримані відтінки називаються

«Змішаними» (dithered) і прекрасно підходять для заливок Але для тексту і ліній Excel використовує тільки чисті кольори, вибираючи замість змішаного найближчий до нього чистий відтінок

ДИВИСЬ ТАКОЖ

Створення і використання користувальницьких форматів описується в розділі «Не-

стандартні формати »даної глави

ВИРІШЕННЯ ПРОБЛЕМ

Іноді при роботі з грошовими форматами, такими як канадський долар або рубль (де символ валюти – кінцевий), немає необхідності ставити після кожного числового значення символ валюти У практиці GAAP буває досить помістити цей знак зверху чи знизу колонки чисел Оскільки в такому випадку символи валюти не відображаються в кожному осередку, виникає питання: як вирівняти всі числа по десятковій точці

Одним із способів подолання проблеми є створення нового формату, в якому буде відсутня символ валюти Знак підкреслення (_) в масці повідомляє програмі про те, що потрібно залишати порожній проміжок, рівний ширині символу, наступного за знаком підкреслення Наприклад, код _ $ означає, що потрібно залишити простір, рівний ширині знаку долара Таким чином, наше завдання можна вирішити, записавши наступний код:

# # # 0,00 _ $ . [Червоний] * # # 0,00 _ $

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

Використання умов в кодах форматів

Крім усього іншого, Excel дозволяє керувати відображенням чисел в осередках залежно від виконання (або невиконання) певних умов Для цього необхідно додати умови в дві перші маски стандартного че-тирехваріантного формату, які за відсутності умов відповідають позитивним і негативним значенням Тоді в третьому масці буде описуватися формат всіх значень, що не задовольняють умовам перших двох При визначенні форматів з умовами використовуються оператори порівняння <,>, =, <=,> = і <> в поєднанні з будь-якими числами

Покладемо, ви підводите баланс за рахунками Щоб вивести на екран рахунку з балансом,

що перевищує $ 50 000, синім кольором, негативні значення – червоним, а все

інші значення – кольором, використовуваним за замовчуванням, створіть наступний формат:

[Синій] [> 50000] _-$ # # # 0,00 [Червоний] [<0] - $ # # # 0,00; _-$ # # # 0.00

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

[> 999 999] # # # 0 _м кл; [> 999] # 0 _к_м л; # _к мл

Тоді, якщо ви введете в комірку невелике значення 72, показано буде 72 мл При введенні великого числа (наприклад, 7286957), воно буде виражено вже в кілолітр (відповідно 7 кл) Проміжні значення переведуться в літри (наприклад: 7632 – 8 л)

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

Приховування значень за допомогою форматування

Щоб приховати будь-яку величину на робочому листі, можна застосувати до неї «порожній» формат Робиться це дуже просто – потрібно з відповідної величиною частини коду формату прибрати всі символи, залишивши тільки роздільники масок (коми) Наприклад, приховати тільки негативні і нульові значення дозволяє такий формат:

$# ##0,00;

Щоб приховати будь-які записи в комірці, зведіть код до його простій формі:

;;;

Порожня маска приховує значення на аркуші, але воно залишається як і раніше видимим в рядку формул і доступним за посиланнями з формул Якщо необхідно сховати вміст комірок так, щоб «слід» не залишався ні в осередках, ні в рядку формул, скористайтеся куди більш надійним засобом – захистом осередків Більш докладно про неї розповідається в розділі «Захист даних» глави 5

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*