Нагадаю, що числові формати можуть мати до чотирьох розділів коду, між якими ставиться крапка з комою. Ці розділи визначають формат позитивних, негативних, нульових значень і тексту. При цьому використання формул всередині формату не передбачено. Не можна, наприклад, вставити функцію ЯКЩО в код формату, і спробувати застосувати різні формати в разі різного вмісту осередків. Однак ...
... для цих цілей можна застосувати умовне форматування на основі формул.
Мал. 1. Умовне форматування на основі формул
Завантажити замітку в форматі Word або pdf. приклади в форматі Excel
Почнемо з питань Андрія і Варвари. Формат числа повинен відповідати кільком умовам:
- групи розрядів розділяються пропуском,
- після коми вказується не більше двох цифр,
- після коми вказуються тільки значущі цифри (нулі не відображаються),
- якщо число ціле, кома не відображається.
Іншими словами, треба, щоб числа, набрані, як в лівій колонці, відображалися, як в правій:
23345,10 -> 23 345,1
23345,15 -> 23 345,15
23345,146 -> 23 345,15
23345,00 -> 23 345
Найбільш точно зазначеним умовам відповідають формати:
- Загальний. але у нього два недоліки: він не поділяє групи розрядів, і його не можна використовувати всередині функції ТЕКСТ (значення; формат);
- # ## 0, ##. але він залишає «дурну» кому, якщо число ціле: 23345,00 -> 23 345,
Підібрати формат, який повністю відповідав би зазначеним умовам начебто не можна ... На допомогу приходить умовне форматування. Призначаємо необхідної області листа формат # ## 0, ##, який відпрацює вірно у всіх випадках, крім цілих чисел. Далі для цієї ж області задаємо умовне форматування з використанням формули = ОКРУГЛ (A1; 0) = A1 (рис. 1). Ця умова виконується тільки якщо число в комірці ціле. І для цього умови задаємо стандартний числовий формат з поділом розрядів на групи і нулем знаків після коми. Щоб задати умовне форматування, виділіть область на аркуші, і пройдіть по меню Головна -> Условноеформатірованіе -> Створити правило -> Використовувати формулу для визначення форматується осередків. Введіть формулу і натисніть кнопку Формат. У вікні Формат ячеек перейдіть на закладку Число. Виберіть формат Числовий. число десяткових знаків - 0, поставте галочку навпроти Роздільник груп розрядів (рис. 2). Натисніть Ok.
Мал. 2. Формат ячеек
Вуаля)) Вийшло те, що було потрібно (рис. 3).
Мал. 3. Використання формату # ## 0, ## і умовного форматування
Наступне питання прийшов від Володимира. Чи можливо для користувача форматування з умовою перевірки введеного? Наприклад, якщо вводиться ціле число, то додати "шт.". а якщо дробове, то "кг".
Знову ж, неможливо поставити перевірку целочисленности всередині призначеного для користувача формату, але можна задати один формат на аркуші, а другий за допомогою умовного форматування (як в прикладі вище), або обидва формати задати за допомогою умовного форматування. У другому випадку, умови представлені на рис. 2. Формат для дрібних (перша умова) # ## 0,00 "кг". для цілих (друга умова) - # ## 0 "шт."
Мал. 4. Два різних формату для цілих і дробових чисел
Питання Сергія. Чи можна відформатувати частину тексту в осередку по будь-якою ознакою? Наприклад, змінити колір шрифту тексту, укладеного в дужки?
На жаль немає. Ні для користувача формат, ні умовне форматування, не можуть бути застосовані до частини вмісту комірки. У всіх типах форматування (див. Перелік на рис. 1) йдеться про той чи інший форматуванні осередків цілком. Це завдання можна вирішити написанням коду VBA.
Питання від Дар'ї. Чи можна до числа в комірці за допомогою формату додати через слеш те ж саме число? Наприклад, в осередок введено 12, а відбивається 12/12.
Для цього застосовується формат @ "/" @. Нагадаю. Для того, щоб вводиться в осередок текст відображався на екрані, в відповідне місце текстового розділу формату слід помістити знак @. Якщо текстовий розділ не містить знака @, що вводиться в осередок текст на екрані відображатися не буде. Щоб текст, що вводиться доповнювався певними символами, укладіть ці символи в подвійні лапки ( ""), наприклад, "валовий дохід за" @. Якщо потрібно, щоб текст, що вводиться відображався двічі, слід використовувати символ @ два рази.
Мал. 5. Фрагмент листа з начебто порожніми осередками
Використовуємо для цієї мети умовне форматування на основі формули = ОСЕРЕДОК ( "тип"; A1) = "b" (рис. 6). Значення b відповідає порожній клітинці (докладніше див. Опис функції на сайті Microsoft). Нагадаю, що рядок формул у вікні умовного форматування повинна містити вираз (формулу), що повертає значення ІСТИНА або БРЕХНЯ. Таким чином, якщо перевіряється осередок порожня, то функція ОСЕРЕДОК поверне значення b. а формула = ОСЕРЕДОК ( "тип"; A1) = "b" - значення ІСТИНА, і осередок забарвиться в зелений колір.
Мал. 6. Виділення порожніх клітинок за допомогою умовного форматування на основі формули, що використовує функцію ОСЕРЕДОК
Виявилося, що в осередку А2 міститься формула = "". а в осередку А5 - число 5, написане шрифтом білого кольору 🙂
Мал. 7. «Підстава»: осередки, виглядають порожніми, такими не є