Как сделать так, чтобы Excel отображал числовые значения в качестве пользовательских "валют" (например, медь, серебро, золото)?

Question

Я хотел бы иметь следующий формат в ячейке в Excel:

123g 99s 99c

100 медь (с) = 1 серебро (с)
100 серебра = 1 золото (г)

Так что, если я 12394 он будет показан как "1g 23s 94c".

Это возможно как-то?

Answer 1

Попробуйте этот пользовательский формат:

#0"g" #0"s" 00"c"

Обратите внимание, что это покажет все три "единицы" независимо от фактической суммы, поэтому 12 будет отображаться как "0g 0s 12c". Если вы хотите показать только самый большой "блок" с ненулевым количеством (таким образом, 12 будет "12c", 598 будет "5s 98c" и т.д.), Вы можете настроить его следующим образом, чтобы фактически применять различные форматы на основании значения:

[>=10000]#0"g" #0"s" 00"c";[>=100]#0"s" 00"c";#0"c"

Он не идеален, например 10245 будет отображаться как "1g 02s 45c", а не "1g 2s 45c", потому что он просто делит значение ячейки, а не делит его на 100, чтобы получить отдельные суммы, но кроме этого должен делать то, что вы хотите.

Чтобы обработать отрицательные значения, вам нужно будет объединить вышеуказанный формат с условным форматированием, потому что в Excel может быть только до 3 условий в одном числовом формате. Попробуйте следующее (я предполагаю, что Excel 2010 также должен работать в 2007 году, но не уверен в более ранних версиях):

1. Выделите ячейки и перейдите к "Условное форматирование" → «Новое правило ...» (на вкладке "Главная")
2. Выберите «Использовать формулу, чтобы определить, какие ячейки форматировать»
3. В поле формулы введите =A1>=0 , затем нажмите «Формат ...»
4. Выберите вкладку "Номер", затем "Пользовательский" и введите числовой формат сверху:
   [>=10000]#0"g" #0"s" 00"c";[>=100]#0"s" 00"c";#0"c"
5. Нажмите "ОК" дважды.
6. Повторите шаги 1 и 2.
7. В поле формулы введите =A1<0 , затем нажмите «Формат ...»
8. Выберите вкладку "Число", затем "Пользовательский" и введите следующий числовой формат (в основном такой же, как первый, за исключением обработки отрицательных значений):
   [<=-10000]-#0"g" #0"s" 00"c";[<=-100]-#0"s" 00"c";#0"c"
9. Нажмите "ОК" дважды.

Если вам нужно изменить или удалить правила форматирования или расширить их, чтобы применить к большему количеству ячеек, просто перейдите в "Условное форматирование" → "Управление правилами".

---

Другой вариант - использовать функцию VBA для форматирования значений. Нажмите Alt+F11, чтобы открыть редактор VBA, щелкните правой кнопкой мыши в окне «Проект» и выберите «Вставка» → «Модуль», а затем вставьте следующий код (разумеется, один из многих способов написания такой функции):

Function MyFormat(intValue)
    Dim strReturn As String
    If intValue < 0 Then strReturn = "-"
    intValue = Abs(intValue)

    If intValue > 10000 Then
        strReturn = strReturn & Fix(intValue / 10000) & "g"
        intValue = intValue - (Fix(intValue / 10000)) * 10000
    End If

    If intValue > 100 Then
        If Len(strReturn) > 0 Then strReturn = strReturn & " "
        strReturn = strReturn & Fix(intValue / 100) & "s"
        intValue = intValue - (Fix(intValue / 100)) * 100
    End If

    If Len(strReturn) > 0 Then strReturn = strReturn & " "
    strReturn = strReturn & intValue & "c"

    MyFormat = strReturn
End Function

Этот подход обеспечивает большую гибкость, чем использование пользовательских форматов чисел, и его проще использовать, чем управление правилами условного форматирования - просто введите =MyFormat(12345) вместо 12345 . Недостатком, однако, является то, что вы теряете возможность выполнять математические операции с данными, потому что Excel теперь видит значения в виде текста, а не чисел.

Answer 2

Следующий пользовательский формат будет делать то, что вы хотите:

??\g ??\s ??\c