Как поменять цифры на буквы в столбцах Excel
Иногда после запуска программы Excel пользователи обнаруживают, что в заголовках столбцов указаны не буквы, а цифры. Как правило, такое происходит после того, как пользователь открыл Excel файл с соответствующими настройками либо кто-то изменил настройки и не сообщил об этом.
В данной статье мы расскажем о том, как поменять цифры на буквы в заголовках столбцов Excel. Статья будет полезной для любой версии табличного процессора Excel, включая Excel 2003, 2007, 2010, 2013 и 2016.
Стиль ссылок R1C1
Перед тем, как перейти непосредственно к решению проблемы дадим немного теории. Если в заголовках ваших столбцов указываются не буквы, а цифры (как на скриншоте внизу), то это означает, что у вас включен стиль ссылок R1C1.
Стиль R1C1 – это альтернативный способ записи адреса ячейки. В данном способе вместо цифр для строк и букв для столбцов используются только цифры. При этом адрес ячейки записывается в формате R3C2, где R3 – это третья сторока (R – row или строка), а C2 – это второй столбец (C – column или столбец).
Выше скриншот показывающий, как будет выглядеть формула, высчитывающая сумму двух ячеек, если включен стиль R1C1.
Как отключить стиль R1C1
Теперь перейдем непосредственно к отключению данной функции и возврату к привычному стилю адресации ячеек.
Если вы пользуетесь Excel 2003 , то для того чтобы поменять цифры на буквы вам нужно открыть меню « Сервис – Параметры » и в открывшемся окне перейти на вкладку « Общие ». Здесь будет доступна функция « Стиль ссылок R1C1 », отключите ее, и вы вернетесь к обычному стилю.
Если же вы пользуетесь Excel 2007, 2010, 2013 или 201 6, то вам нужно открыть меню « Файл » и перейти в « Параметры ».
После этого откроется окно « Параметры Excel ». Здесь нужно перейти в раздел « Формулы » и отключить функцию « Стиль ссылок R1C1 ».
После сохранения настроек с помощью кнопки « ОК » адресация столбцов поменяется с букв на цифры и вы сможете пользоваться стандартным стилем Excel.
- Как построить график в Excel
- Как закрепить область в Экселе
- Чем открыть XLS
- Чем открыть XLSX
- Как вставить формулу в Word
Создатель сайта comp-security.net, автор более 2000 статей о ремонте компьютеров, работе с программами, настройке операционных систем.
Задайте вопрос в комментариях под статьей или на странице «Задать вопрос» и вы обязательно получите ответ.
Как перевести сумму или число прописью в Excel
Часто нужно перевести число в текст в Excel так, чтобы оно отображалось прописью (словами) на русском или других языках. Так как по умолчанию нет готовой функции, создадим свою пользовательскую функцию с помощью макросов.
Пример использования пользовательской функции для преобразования числа суммы в текстовые слова, которую можно скачать в конце статьи:
Для создания пользовательской функции, которая сможет перевести число в текст прописью , нам нужно выполнить 3 простых шага:
- Открыть редактор макросов ALT+F11.
- Создать новый модуль и в нем нужно написать функцию особенным способом: Function вместо Sub . Тогда наша функция «ЧислоПропись» будет отображаться в списке мастера функций (SHIFT+F3), в категории «Определенные пользователем».
- Вставить в модуль следующий код и сохранить:
Function ЧислоПропись(Число As Currency ) As String
‘до 999 999 999 999
On Error GoTo Число_Error
Dim strМиллиарды As String , strМиллионы As String , strТысячи As String , strЕдиницы As String , strСотые As String
Dim Поз As Integer
strЧисло = Format(Int(Число), "000000000000" )
‘Миллиарды’
Поз = 1
strМиллиарды = Сотни(Mid(strЧисло, Поз, 1))
strМиллиарды = strМиллиарды & Десятки(Mid(strЧисло, Поз + 1, 2), "м" )
strМиллиарды = strМиллиарды & ИмяРазряда(strМиллиарды, Mid(strЧисло, Поз + 1, 2), "миллиард " , "миллиарда " , "миллиардов " )
‘Миллионы’
Поз = 4
strМиллионы = Сотни(Mid(strЧисло, Поз, 1))
strМиллионы = strМиллионы & Десятки(Mid(strЧисло, Поз + 1, 2), "м" )
strМиллионы = strМиллионы & ИмяРазряда(strМиллионы, Mid(strЧисло, Поз + 1, 2), "миллион " , "миллиона " , "миллионов " )
‘Тысячи’
Поз = 7
strТысячи = Сотни(Mid(strЧисло, Поз, 1))
strТысячи = strТысячи & Десятки(Mid(strЧисло, Поз + 1, 2), "ж" )
strТысячи = strТысячи & ИмяРазряда(strТысячи, Mid(strЧисло, Поз + 1, 2), "тысяча " , "тысячи " , "тысяч " )
‘Единицы’
Поз = 10
strЕдиницы = Сотни(Mid(strЧисло, Поз, 1))
strЕдиницы = strЕдиницы & Десятки(Mid(strЧисло, Поз + 1, 2), "м" )
If strМиллиарды & strМиллионы & strТысячи & strЕдиницы = "" Then strЕдиницы = "ноль "
‘strЕдиницы = strЕдиницы & ИмяРазряда(" ", Mid(strЧисло, Поз + 1, 2), "рубль ", "рубля ", "рублей ")
‘Сотые’
‘strСотые = strКопейки & " " & ИмяРазряда(strКопейки, Right(strКопейки, 2), ‘"копейка", "копейки", "копеек")
ЧислоПропись = strМиллиарды & strМиллионы & strТысячи & strЕдиницы
ЧислоПропись = UCase(Left(ЧислоПропись, 1)) & Right(ЧислоПропись, Len(ЧислоПропись) — 1)
Число_Error:
MsgBox Err.Description
End Function
Function Сотни(n As String ) As String
Сотни = ""
Select Case n
Case 0: Сотни = ""
Case 1: Сотни = "сто "
Case 2: Сотни = "двести "
Case 3: Сотни = "триста "
Case 4: Сотни = "четыреста "
Case 5: Сотни = "пятьсот "
Case 6: Сотни = "шестьсот "
Case 7: Сотни = "семьсот "
Case 8: Сотни = "восемьсот "
Case 9: Сотни = "девятьсот "
End Select
End Function
Function Десятки(n As String , Sex As String ) As String
Десятки = ""
Select Case Left(n, 1)
Case "0" : Десятки = "" : n = Right(n, 1)
Case "1" : Десятки = ""
Case "2" : Десятки = "двадцать " : n = Right(n, 1)
Case "3" : Десятки = "тридцать " : n = Right(n, 1)
Case "4" : Десятки = "сорок " : n = Right(n, 1)
Case "5" : Десятки = "пятьдесят " : n = Right(n, 1)
Case "6" : Десятки = "шестьдесят " : n = Right(n, 1)
Case "7" : Десятки = "семьдесят " : n = Right(n, 1)
Case "8" : Десятки = "восемьдесят " : n = Right(n, 1)
Case "9" : Десятки = "девяносто " : n = Right(n, 1)
End Select
Dim Двадцатка As String
Двадцатка = ""
Select Case n
Case "0" : Двадцатка = ""
Case "1"
Select Case Sex
Case "м" : Двадцатка = "один "
Case "ж" : Двадцатка = "одна "
Case "с" : Двадцатка = "одно "
End Select
Case "2" :
Select Case Sex
Case "м" : Двадцатка = "два "
Case "ж" : Двадцатка = "две "
Case "с" : Двадцатка = "два "
End Select
Case "3" : Двадцатка = "три "
Case "4" : Двадцатка = "четыре "
Case "5" : Двадцатка = "пять "
Case "6" : Двадцатка = "шесть "
Case "7" : Двадцатка = "семь "
Case "8" : Двадцатка = "восемь "
Case "9" : Двадцатка = "девять "
Case "10" : Двадцатка = "десять "
Case "11" : Двадцатка = "одиннадцать "
Case "12" : Двадцатка = "двенадцать "
Case "13" : Двадцатка = "тринадцать "
Case "14" : Двадцатка = "четырнадцать "
Case "15" : Двадцатка = "пятнадцать "
Case "16" : Двадцатка = "шестнадцать "
Case "17" : Двадцатка = "семнадцать "
Case "18" : Двадцатка = "восемнадцать "
Case "19" : Двадцатка = "девятнадцать "
End Select
Десятки = Десятки & Двадцатка
End Function
Function ИмяРазряда(Строка As String , n As String , Имя1 As String , Имя24 As String , ИмяПроч As String ) As String
Select Case n
Case "1" : ИмяРазряда = Имя1
Case "2" , "3" , "4" : ИмяРазряда = Имя24
Case Else : ИмяРазряда = ИмяПроч
End Select
End If
Можно написать алгоритм макро программы по-другому и еще сделать так, чтобы она дописывала валюту суммы прописью. Для этого создайте Module2 и введите в него следующий код:
Function ЧислоПрописьюВалюта(Число As Double , Optional Валюта As Integer = 1, Optional Копейки As Integer = 1)
Attribute ЧислоПрописьюВалюта.VB_Description = "Функция преобразовывает число суммы текстовыми словами"
Attribute ЧислоПрописьюВалюта.VB_ProcData.VB_Invoke_Func = " \n1"
Dim Edinicy(0 To 19) As String : Dim EdinicyPoslednie(0 To 19) As String
Dim Desyatki(0 To 9) As String : Dim Sotni(0 To 9) As String : Dim mlrd(0 To 9) As String
Dim mln(0 To 9) As String : Dim tys(0 To 9) As String
Dim SumInt, x, shag, vl As Integer : Dim txt, Sclon_Tys As String
‘———————————————
Application.Volatile
‘———————————————
Edinicy(0) = "" : EdinicyPoslednie(0) = IIf(Валюта = 0, "евро" , IIf(Валюта = 1, "рублей" , "долларов" ))
Edinicy(1) = "один " : EdinicyPoslednie(1) = IIf(Валюта = 0, "один евро" , IIf(Валюта = 1, "один рубль" , "один доллар" ))
Edinicy(2) = "два " : EdinicyPoslednie(2) = IIf(Валюта = 0, "два евро" , IIf(Валюта = 1, "два рубля" , "два доллара" ))
Edinicy(3) = "три " : EdinicyPoslednie(3) = IIf(Валюта = 0, "три евро" , IIf(Валюта = 1, "три рубля" , "три доллара" ))
Edinicy(4) = "четыре " : EdinicyPoslednie(4) = IIf(Валюта = 0, "четыре евро" , IIf(Валюта = 1, "четыре рубля" , "четыре доллара" ))
Edinicy(5) = "пять " : EdinicyPoslednie(5) = IIf(Валюта = 0, "пять евро" , IIf(Валюта = 1, "пять рублей" , "пять долларов" ))
Edinicy(6) = "шесть " : EdinicyPoslednie(6) = IIf(Валюта = 0, "шесть евро" , IIf(Валюта = 1, "шесть рублей" , "шесть долларов" ))
Edinicy(7) = "семь " : EdinicyPoslednie(7) = IIf(Валюта = 0, "семь евро" , IIf(Валюта = 1, "семь рублей" , "семь долларов" ))
Edinicy(8) = "восемь " : EdinicyPoslednie(8) = IIf(Валюта = 0, "восемь евро" , IIf(Валюта = 1, "восемь рублей" , "восемь долларов" ))
Edinicy(9) = "девять " : EdinicyPoslednie(9) = IIf(Валюта = 0, "девять евро" , IIf(Валюта = 1, "девять рублей" , "девять долларов" ))
Edinicy(11) = "одиннадцать " : EdinicyPoslednie(11) = IIf(Валюта = 0, "одиннадцать евро" , IIf(Валюта = 1, "одиннадцать рублей" , "одиннадцать долларов" ))
Edinicy(12) = "надцать " : EdinicyPoslednie(12) = IIf(Валюта = 0, "надцать евро" , IIf(Валюта = 1, "надцать рублей" , "надцать долларов" ))
Edinicy(13) = "тринадцать " : EdinicyPoslednie(13) = IIf(Валюта = 0, "тринадцать евро" , IIf(Валюта = 1, "тринадцать рублей" , "тринадцать долларов" ))
Edinicy(14) = "четырнадцать " : EdinicyPoslednie(14) = IIf(Валюта = 0, "четырнадцать евро" , IIf(Валюта = 1, "четырнадцать рублей" , "четырнадцать долларов" ))
Edinicy(15) = "пятнадцать " : EdinicyPoslednie(15) = IIf(Валюта = 0, "пятнадцать евро" , IIf(Валюта = 1, "пятнадцать рублей" , "пятнадцать долларов" ))
Edinicy(16) = "шестнадцать " : EdinicyPoslednie(16) = IIf(Валюта = 0, "шестнадцать евро" , IIf(Валюта = 1, "шестнадцать рублей" , "шестнадцать долларов" ))
Edinicy(17) = "семнадцать " : EdinicyPoslednie(17) = IIf(Валюта = 0, "семнадцать евро" , IIf(Валюта = 1, "семнадцать рублей" , "семнадцать долларов" ))
Edinicy(18) = "восемнадцать " : EdinicyPoslednie(18) = IIf(Валюта = 0, "восемнадцать евро" , IIf(Валюта = 1, "восемнадцать рублей" , "восемнадцать долларов" ))
Edinicy(19) = "девятнадцать " : EdinicyPoslednie(19) = IIf(Валюта = 0, "девятнадцать евро" , IIf(Валюта = 1, "девятнадцать рублей" , "девятнадцать долларов" ))
»———————————————
Desyatki(0) = "" : Sotni(0) = "" : tys(0) = "тисячь " : mln(0) = "миллионов " : mlrd(0) = "миллиардов "
Desyatki(1) = "десять " : Sotni(1) = "сто " : tys(1) = "тысяча " : mln(1) = "миллион " : mlrd(1) = "миллиарда "
Desyatki(2) = "двадцать " : Sotni(2) = "двести " : tys(2) = "тысячи " : mln(2) = "миллиона " : mlrd(2) = "миллиарда "
Desyatki(3) = "тридцать " : Sotni(3) = "триста " : tys(3) = "тысячи " : mln(3) = "миллиона " : mlrd(3) = "миллиарда "
Desyatki(4) = "сорок " : Sotni(4) = "четыреста " : tys(4) = "тысячи " : mln(4) = "миллиона " : mlrd(4) = "миллиарда "
Desyatki(5) = "пятьдесят " : Sotni(5) = "пятьсот " : tys(5) = "тысяч " : mln(5) = "миллионов " : mlrd(5) = "миллиардов "
Desyatki(6) = "шестьдесят " : Sotni(6) = "шестьсот " : tys(6) = "тысяч " : mln(6) = "миллионов " : mlrd(6) = "миллиардов "
Desyatki(7) = "семьдесят " : Sotni(7) = "семьсот " : tys(7) = "тысяч " : mln(7) = "миллионов " : mlrd(7) = "миллиардов "
Desyatki(8) = "восемьдесят " : Sotni(8) = "восемьсот " : tys(8) = "тысяч " : mln(8) = "миллионов " : mlrd(8) = "миллиардов "
Desyatki(9) = "девяносто " : Sotni(9) = "девятьсот " : tys(9) = "тысяч " : mln(9) = "миллионов " : mlrd(9) = "миллиардов "
‘———————————————
On Error Resume Next
SumInt = Int(Число)
For x = Len(SumInt) To 1 Step -1
shag = shag + 1
Select Case x
Case 12 ‘ — сотни миллиардов
vl = Mid(SumInt, shag, 1)
txt = txt & Sotni(vl)
Case 11 ‘ — десятки миллиардов
vl = Mid(SumInt, shag, 1)
If vl = "1" And Mid(SumInt, shag + 1, 1) <> 0 Then GoTo 10 Else txt = txt & Desyatki(vl) ‘ — если конец триады от 11 до 19 то перескакиваем на единицы, иначе — формируем десятки
Case 10 ‘ — единицы миллиардов
vl = Mid(SumInt, shag, 1)
If shag > 1 Then
If Mid(SumInt, shag — 1, 1) = 1 Then txt = txt & Edinicy(Mid(SumInt, shag — 1, 2)) & "миллиарда " Else txt = txt & Edinicy(vl) & mlrd(vl) ‘числа в диапозоне от 11 до 19 склоняются на "мільярдов" независимо от последнего числа триады
Else
txt = txt & Edinicy(vl) & mlrd(vl)
End If
Case 9 ‘ — сотни миллионов
vl = Mid(SumInt, shag, 1)
txt = txt & Sotni(vl)
Case 8 ‘ — десятки миллионов
vl = Mid(SumInt, shag, 1)
If vl = "1" And Mid(SumInt, shag + 1, 1) <> 0 Then GoTo 10 Else txt = txt & Desyatki(vl) ‘ — если конец триады от 11 до 19 то перескакиваем на единицы, иначе — формируем десятки
Case 7 ‘ — единицы миллионов
vl = Mid(SumInt, shag, 1)
If shag > 2 Then
If (Mid(SumInt, shag — 2, 1) = 0 And Mid(SumInt, shag — 1, 1) = 0 And vl = "0" ) Then GoTo 10
End If
If shag > 1 Then
If Mid(SumInt, shag — 1, 1) = 1 Then txt = txt & Edinicy(Mid(SumInt, shag — 1, 2)) & "миллиона " Else : txt = txt & Edinicy(vl) & mln(vl) ‘числа в диапозоне от 11 до 19 склоняются на "миллиардов" независимо от последнего числа триады
Else
txt = txt & Edinicy(vl) & mln(vl)
End If
‘-КОНЕЦ БЛОКА_______________________
Case 6 ‘ — сотни тысяч
vl = Mid(SumInt, shag, 1)
txt = txt & Sotni(vl)
Case 5 ‘ — десятки тысяч
vl = Mid(SumInt, shag, 1)
If vl = 1 And Mid(SumInt, shag + 1, 1) <> 0 Then GoTo 10 Else txt = txt & Desyatki(vl) ‘ — если конец триады от 11 до 19 то перескакиваем на единицы, иначе — формируем десятки
Case 4 ‘ — единицы тысяч
vl = Mid(SumInt, shag, 1)
If shag > 2 Then
If (Mid(SumInt, shag — 2, 1) = 0 And Mid(SumInt, shag — 1, 1) = 0 And vl = "0" ) Then GoTo 10
End If
Sclon_Tys = Edinicy(vl) & tys(vl) ‘ — вводим переменную Sclon_Tys из-за иного склонения тысяч в русском языке
If vl = 1 Then Sclon_Tys = "одна " & tys(vl) ‘ — для тысяч склонение "один" и "два" неприменимо ( поэтому вводим переменную Sclon_Tys )
If vl = 2 Then Sclon_Tys = "две " & tys(vl) ‘ — для тысяч склонение "один" и "два" неприменимо ( поэтому вводим переменную Sclon_Tys )
If shag > 1 Then
If Mid(SumInt, shag — 1, 1) = 1 Then Sclon_Tys = Edinicy(Mid(SumInt, shag — 1, 2)) & "тисяч "
End If
txt = txt & Sclon_Tys
‘-КОНЕЦ БЛОКА_______________________
Case 3 ‘ — сотни
vl = Mid(SumInt, shag, 1)
txt = txt & Sotni(vl)
Case 2 ‘ — десятки
vl = Mid(SumInt, shag, 1)
If vl = "1" And Mid(SumInt, shag + 1, 1) <> 0 Then GoTo 10 Else txt = txt & Desyatki(vl) ‘ — если конец триады от 11 до 19 то перескакиваем на единицы, иначе — формируем десятки
Case 1 ‘ — единицы
If Mid(SumInt, shag — 1, 1) <> 1 Or Mid(SumInt, shag — 1, 2) = "10" Then vl = Mid(SumInt, shag, 1) Else vl = Mid(SumInt, shag — 1, 2)
txt = txt & EdinicyPoslednie(vl)
End Select
10: Next x
a = Число
b = Int(a)
c = (Round(a — b, 2)) * 100
If c < 10 And c >= 1 Then c = "0" + CStr(c)
If c = 0 Then c = CStr(c) + "0"
d = ""
If Валюта = 1 Then d = "коп." Else d = "цен."
If Валюта > 2 Or Валюта < 0 Then MsgBox "Укажите параметр 0-2"
If Валюта > 2 Or Валюта < 0 Then GoTo 11
If Копейки = 0 Then
d = ""
c = ""
End If
If Копейки = 2 Then d = ""
If Копейки > 2 Or Копейи < 0 Then MsgBox "Укажите параметр 0, 1 или 2"
If Копейки > 2 Or Копейки < 0 Then GoTo 11
ЧислоПрописьюВалюта = UCase(Left(txt, 1)) & LCase(Mid(txt, 2)) + " " + CStr(c) + d
11:
End Function
Sub DescribeFunction()
Dim FuncName As String
Dim FuncDesc As String
Dim Category As String
Dim ArgDesc(1 To 3) As String
FuncName = "ЧислоПрописьюВалюта"
FuncDesc = "Функция преобразовывает число суммы текстовыми словами"
Category = 1 ‘Text category
ArgDesc(1) = "Исходная сумма"
ArgDesc(2) = "(необязательный) Тип отображаемой валюты 0-Евро, 1-Рубли, 2-Доллары."
ArgDesc(3) = "(необязательный) Нужны ли копейки: 0-нет, 1-отображать копейи стандартно, 2-отображать только дробную часть (без слов)."
Application.MacroOptions _
Macro:=FuncName, _
Description:=FuncDesc, _
Category:=Category, _
ArgumentDescriptions:=ArgDesc
End Sub
Также не забудьте добавить в рабочую книгу код вызова макроса регистрации DescribeFunction, чтобы отображать описание атрибутов для пользовательской функции:
Кроме того благодаря данному макросу DescribeFunction функция будет доступна в группе: «ФОРМУЛЫ»-«Библиотека функций»-«Финансовые»-«ЧислоПрописьюВалюта»
Если мы указываем число (от 0 до 2)в параметре второй функции «ЧислоПрописьюВалюта» то функция автоматически подставит нужную валюту в сумме прописью:
- 1-рубли;
- 2-доллары;
- 0-евро;
Как видите, этот VBA-код макроса преобразует числа в слова. После вставки данного кода в модуль редактора макросов, у нас работает новая функция, которую можно вызвать из мастера (кнопка fx возле строки формул).
Украинская версия функции ЧислоСловоВалюта для перевода сумм в гривны находиться в следующем файле:
Теперь вы можете быстро перевести сумму в слова прописью. Чтобы воспользоваться готовым решением рекомендуем скачать пример числа прописью в Excel. Данный файл содержит уже готовую пользовательскую функцию и VBA-код макроса, который доступен в модуле из редактора.
Сумма прописью в Microsoft Excel
При заполнении различных финансовых документов часто требуется прописать сумму не только числом, но и прописью. Конечно, это занимает гораздо больше времени, чем обычное написание с помощью цифр. Если подобным образом нужно заполнить не одно, а много документов, то временные потери становятся огромными. Кроме того, именно в записи суммы прописью наиболее часто встречаются грамматические ошибки. Давайте выясним, как сделать так, чтобы числа прописью вносились автоматически.
Использование надстройки
В Эксель нет встроенного инструмента, который бы помогал автоматически переводить цифры в слова. Поэтому для решения поставленной задачи используют специальные надстройки.
Одной из самых удобных является надстройка NUM2TEXT. Она позволяет поменять цифры на буквы через Мастер функций.
-
Открываем программу Excel и переходим во вкладку «Файл».
Можно в поле занести адрес любой ячейки. Это делается либо путем ручной записи координат этой ячейки, либо простым кликом по ней в то время, когда курсор находится в поле параметра «Сумма». Жмем на кнопку «OK».
Функцию можно также записать и вручную без вызова мастера функций. Она имеет синтаксис Сумма_прописью(сумма) или Сумма_прописью(координаты_ячейки). Таким образом, если вы в ячейке запишите формулу =Сумма_прописью(5) , то после нажатия кнопки ENTER в этой ячейке отобразиться надпись «Пять рублей 00 копеек».
Если вы введете в ячейку формулу =Сумма_прописью(A2) , то в таком случае, любое число, внесенное в ячейку A2 будет отображаться тут денежной суммой прописью.
Как видим, несмотря на то, что в Excel нет встроенного инструмента для преобразования чисел в сумму прописью, эту возможность можно довольно легко получить, просто установив необходимую надстройку в программу.
Числа прописью в Excel быстро и без вспомогательных инструментов!
Зачастую бывают такие ситуации, когда записанные при помощи цифр числа в Excel должны быть представлены в виде прописи, например, подобное потребоваться при составлении договоров, чеков, накладных и так далее.
Естественно, данную задачу можно легко решить при помощи VBA, либо же различных Add-In-ов, однако, в таком случае эти самые Add-In-ы придется устанавливать, либо же интегрировать макросы VBA. В этом же посте я хочу поделиться интересным решением данной задачи при помощи всего одной формулы.
Вот пример её действия:
Файл с формулой можно скачать по следующей ссылке:
Как можете заметить, в функции очень много ссылок на одну и ту же ячейку – H6. Естественно, вручную перетаскивать все ссылки H6 не вариант (их 97!). Поэтому вот как работаем с этой формулой:
1. Её текст можно скопировать из файла по ссылке и вставить в ячейку, где в будущем нужна пропись цифры
2. Выбираем две ячейки, включая ячейку с формулой (показываю на примере самого файла по ссылке – в формуле с выводом сотых заменим ссылку на новое значение в ячейке C6):
Лучше всего, чтобы вторая выбранная ячейка была пустой, или в крайнем случае не содержала той же самой ссылки, так как мы её сейчас будем автоматически менять.
3. Вызываем окно поиска и замены с помощью CTRL+H
4. В «Найти» вписываем заменяемый адрес – H6, в «Заменить на» — адрес новой нужной ячейки – например, C6
5. Нажимаем на «Заменить все». Замена происходит лишь в обеих выбранных ячейках (именно для этого мы и выбирали вторую ячейку, чтобы ограничить замену лишь на выбранный диапазон). Готово! Формула сразу ссылается на нужное значение:
Из особенностей формулы:
1. Формула абсолютно самостоятельна и не использует VBA или сторонние Add-Ins
2. Формула работает с числами до 999.999.999
3. Формула правильно склоняет слова
4. Вся формула в свою очередь состоит в основном из формул ЛЕВСИМВ, ПСТР, ЕСЛИ, ТЕКСТ, И, ВЫБОР
5. Количество символов в формуле с прописью целых и сотых: 6034
Вот в этом видео разобран принцип работы формулы, то, как её использовать, а также рассказаны еще некоторые детали, обязательно советую его посмотреть:
633 поста 14.4K подписчиков
Правила сообщества
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.
Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.
полагаю, в данном случае намного эффективнее использовать макрос. его можно импортировать несколькими движениями и создать кнопочку на панели быстрого доступа, либо создать пользовательскую функцию. если кто-то захочет использовать много таких формул в документе, он начнет безбожно глючить
ты текстом формулу выложить не мог?
Решение интересное. Но макросом тут все же проще.
Осталось только осознать тот факт, что бывают не только сотые но и десятые)
EXCEL — ЭТИ СТРАШНЫЕ МАКРОСЫ – НАЧАЛО
Я решил с двух ног ворваться в тему макросов.
Кто-то про них слышал, кто-то даже видел, отдельные сверхразумы их даже использовали. Сегодня будет ознакомительный пост: что это вообще такое и как с этим начать работать. Обратите внимание – этот пост тех, кто не знает, что такое макросы и никогда с ними не работал
Первым делом нужно включить вкладку «Разработчик». По умолчанию в Excel ее спрятали, чтобы не взорвать мозг юзерам. Идем в Параметры -> Настройка ленты -> Основные вкладки -> Разработчик (поставить галочку).
Теперь идем в эту вкладку, нажимаем «Записать макрос» выбираем имя жмакаем «ок». Все, теперь любые действия в Excel надежным образом записываются.
Давайте теперь что-то сделаем. На пример поменяем заливку ячейки А1, в ячейку A2 напишем значение «Мама, я программист», а в ячейке А3 пропишем формулу текущей даты «=Сегодня()»
Останавливаем запись макроса. Нажимаем иконку «Макросы», выбираем наш макрос как мы его обозвали, нажимаем кнопку «изменить».
Появляется окно Microsoft Visual Basic for Applications. Кстати оно также вызывается комбинацией клавиш (Alt + F11) У меня почему-то вызывается только левым Altом, а правым нет, видимо намекая на то что для написания макросов лучше иметь 2 руки (хотя я и одной нажать могу). Появился редактор языка VBA – это язык, который написан специально под офис чтобы на нем писать макросы. В основном окне видим саму эту запись, которую автоматически сделал Excel.
Sub Макрос2()
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range(«A2»).Select
ActiveCell.FormulaR1C1 = «Мама, я программист»
Range(«A3»).Select
ActiveCell.FormulaR1C1 = «=TODAY()»
Range(«A4»).Select
End Sub
Такой инструмент автоматической записи называется макрорекордер, по началу он хорошо помогает вообще понять, что происходит. Теперь давайте разберемся как выглядит записанный макрос (записанный макрос у автора этой статьи может различаться с записанным макросом у вас поскольку форма записи с макроса зависит от последовательности нажатий, способах ввода информации и криворукости пользователь, в любом случае автор этой статьи снимает с себя всю ответственность за любые действия которые привели или могут привести к изменению годовых отчетов, искажению пространственное – временного континуума и прочих непредвиденных вымираний.)
Теперь давайте разбираться что делает этот макрос
Sub Макрос2()
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
(Весь этот кусок от начала говорит нам о том, что с тем элементом что был выделен ранее происходит некоторое дерьмо, в том числе изменение цвета. Вот там, где Color = 255. Все остальное это параметры заливки, которые по итогу не менялись, но макрорекордер решил их тоже записать, на всякий. Это связано с внутренними особенностями работы excel как я понял. Вообще привыкайте к тому что макрорекордер пишет много того что потом вообще можно удалить. Конструкция With – End With позволяет делать несколько действий с одним объектом, на пример выше берется объект Selection.Interior, то есть фон выбранной области и ряду параметров этой заливки назначаются конкретные значения. То есть With нужен для облегчения записи кода, чтобы Selection.Interior не писать вначале каждой строчки.
Range(«A2»).Select –выделяем ячейку «A2»
ActiveCell.FormulaR1C1 = «Мама, я программист» – пишем в ячейку значение
Range(«A3»).Select – выделяем ячейку «А3»
ActiveCell.FormulaR1C1 = «=TODAY()» –пишем в ячейку формулу
Range(«A4»).Select – зачем то выделяем ячейку А4.
End Sub
Теперь при создании нового листа и запуске этого макроса он будет делать все то же самое.
Тут стоит понимать, что половину того что записал макрос можно опустить, так как нам важен результат, а не путь по которому к этому результату пришли, а макрорекордер записывает именно путь. На пример вместо всей конструкции With можно записать
так как мы точно знаем, что хотим поменять только цвет в ячейке А1. Также вместо того чтобы выделять ячейку, а потом в активной ячейке писать формулу можно сразу записать нужное значение в ячейку
Range(“A2”).Value = ”Мама, я программист”
или писать формулу как в третей ячейке
С формулами и значениями лично мне не понятно, как excel их интерпретирует, но в макрорекордре он записывает любой ввод в ячейку как ввод формулы. Благо лично у меня при написании макросов не возникает необходимости писать формулы в ячейки. На пример вместо вставки формулы как это было выше можно написать Range(“A3”).Value = Date(), тогда макрос вставит сразу текущую дату в ячейку как значение.
Опытные макроделы пишут макросы сразу без их записи макрорекордером, но это полезный инструмент для самостоятельного изучения при написании макросов: если не знаешь, что как делается в VBА то запускаешь и делаешь, потом смотришь что он там написал.
Теперь давайте напишем какой ни будь полезный макрос, я буду писать строчки и как можно подробнее их комментировать. Комментарии от программы отделяются символом «‘», он ставится вначале комментария. Прошу учесть, что я самоучка и многого не знаю, и просто напишу так ка делаю это сам, возможно есть более изящные решения. Напоминаю, что это просто ознакомительный опус для тех, кто не в зуб ногой.
Sub Colorization()
‘начало нашего макроса и его название
Dim x As Integer
‘объявляем переменную х типа интеджер, это тип для целых чисел от -32 768 до 32 767 (2 байта),
‘она нам нужна для перебора ячеек
For x = 1 To ActiveSheet.UsedRange.Rows.Count
‘перебираем х от 1 до конца использованной части листа, то есть не весь лист, а там где есть данные.
‘Тут цикл For повторяется от этой строки до строки Next x, которая прописана ниже
If Cells(x, 1).Value = «красный» Then Cells(x, 1).Interior.Color = RGB(255, 0, 0)
‘если значение в ячейке равно «красный» то закрашиваем ячейку в красный цвет. Функция If выполняет часть
‘после Then если условие между If и Then верно. Так как у нас необходимое действие занимает одну
‘строку можно писать в таком виде, если же действий несколько применяется конструкция:
‘If … Then
‘…
‘…
‘End If
If Cells(x, 1).Value = «зеленый» Then Cells(x, 1).Interior.Color = RGB(0, 255, 0)
‘как выше только в зеленый цвет
If Cells(x, 1).Value = «синий» Then Cells(x, 1).Interior.Color = RGB(0, 0, 255)
‘в синий цвет
Next x ‘берем следующее значение х, конец цикла For, который мы начали выше
End Sub ‘конец макроса
Как работает этот макрос: берет первый столбец, сначала 1 ячейку, смотрит что в ней написано, и если это равно «красный», «зеленый» или «синий», то красит фон ячейки в этот цвет, если нет по пропускает. Потом берет вторую и т. д. до конца активной части текущего листа.
Для проверки работы макроса нам нужен лист, где в первом столбце будут случайным образом прописаны цвета «красный», «зеленый», «синий». Запускаем макрос – когда он отработает ячейки будут раскрашены:
Некоторые пояснения: если не писать просто Cells то макрос будет делать все в активном листе активного окна. Но макрос может идти и в другие листы, файлы, даже в другие приложения офиса, но об этом не сегодня.
После того, как макрос поработал с документом, не удастся кнопкой «назад» вернуть все изменения, так что, если данные важны, то сохраняйте их отдельно и макросом работайте с копией, макросы беспощадны.
Итак, на этом пока все. Надеюсь теперь те, кто никогда не видел макросов получат о них начальное представление. Дальше буду писать про более практичное применение.