Как сделать перевод в эксель

Добавил пользователь Дмитрий К.
Обновлено: 04.10.2024

Внимание: периодически Google вводит ограничение и изменяет алгоритмы автоматического перевода (так же иногда для получения перевода необходимо вводить каптчу). Поэтому временами функция может не работать. Некоторое время назад можно было наблюдать это. Сейчас функция работает.

На днях проводил ревизию кодов и различных примеров в файлах и нашел довольно много интересного. В том числе функцию перевода текста из ячейки Excel на разные языки при помощи сервиса Google. Посмотрел и решил её переработать и оптимизировать, т.к. в том виде, в котором она была у меня она никуда не годилась: переводила коряво, т.к. Google поменял алгоритм в формат выходных данных. Плюс надо было решить вопрос некорректной замены различных символов внутри текста. Подозреваю, что и сейчас функция далеко не идеальна. Поэтому если у кого-то возникнут ошибки - пишите комментарии, попробуем разобраться.

Option Explicit '--------------------------------------------------------------------------------------- ' Procedure : GoogleTranslate ' DateTime : 04.09.2013 22:55 ' Author : The_Prist(Щербаков Дмитрий) ' http://www.excel-vba.ru ' Purpose : ' Функция переводит текст, используя сервис переводов Google Translate ' Аргументы: ' sText - текст для перевода. Непосредственно текст или ссылка на ячейку. ' sResLang - код языка, на который осуществлять перевод ' sSourceLang - код языка, с которого переводить. ' Если не указывать, Google автоматом определяет язык введенного текста ' Доступно 74 кода языков. '--------------------------------------------------------------------------------------- Dim objRegExp As Object Function GoogleTranslate(ByVal sText As String, ByVal sResLang As String, _ Optional ByVal sSourceLang As String = "") Dim sGoogleURL As String, sAllTxt As String, sTmpStr As String, sRes As String, sTextToTranslate As String Dim lByte As Long, alByteToEncode, li As Long Dim asTmp, lPos As Long 'раскомментировать при необходимости пересчета функции при любом изменении в книге 'Application.Volatile True If objRegExp Is Nothing Then Set objRegExp = CreateObject("VBScript.RegExp") With objRegExp .MultiLine = True: .ignorecase = True: .Global = True .Pattern = "[\n;]" End With End If sTextToTranslate = Application.Trim(objRegExp.Replace(sText, " ")) With CreateObject("ADODB.Stream") .Charset = "utf-8": .Mode = 3: .Type = 2: .Open .WriteText sTextToTranslate .Flush: .Position = 0: .Type = 1: .Read 3 alByteToEncode = .Read(): .Close End With 'переводим текст в кодировку, понятную Google For li = 0 To UBound(alByteToEncode) lByte = alByteToEncode(li) Select Case lByte Case 32: sTmpStr = "+" Case 48 To 57, 65 To 90, 97 To 122: sTmpStr = Chr(alByteToEncode(li)) Case Else: sTmpStr = "%" & Hex(lByte) End Select sAllTxt = sAllTxt & sTmpStr Next li 'формируем ссылку для Google sGoogleURL = "http://translate.google.com.ua/translate_a/t?client=json&text=" & _ sAllTxt & "&hl=" & sResLang & "&sl=" & sSourceLang sGoogleURL = Replace(sGoogleURL, "\", "/") '" 'получаем ответ от Google With CreateObject("Microsoft.XMLHTTP") .Open "GET", sGoogleURL, "False": .send If .statustext = "OK" Then sTmpStr = .responsetext 'отбираем только переведенный текст asTmp = Split(sTmpStr, "<""trans"":""") For li = LBound(asTmp) To UBound(asTmp) lPos = InStr(1, asTmp(li), """,""orig"":", 1) If lPos Then sRes = sRes & Mid(asTmp(li), 1, lPos - 1) Next li If sRes = "" Then sRes = "Не удалось перевести" End If End With GoogleTranslate = sRes End Function

Выложенный выше код является функцией пользователя для вызова из ячейки листа.

Синтаксис функции:
с английского на русский (в A1 записан текст на английском - Google автоматом определяет язык):
=GoogleTranslate( A1 ;"ru")
с английского на русский - текст задан константой:
=GoogleTranslate("translation"; "ru")

sText - текст для перевода. Непосредственно текст или ссылка на ячейку.
sResLang - код языка, на который осуществлять перевод.
sSourceLang - Необязательный аргумент. Указывается код языка, с которого переводить. Если не указывать, Google автоматом определяет язык введенного текста.

Всего доступно 74 языка:

en - английский
ru - русский
az - азербайджанский
sq - албанский
en - английский
ar - арабский
hy - армянский
af - африкаанс
eu - баскский
be - белорусский
bn - бенгальский
bg - болгарский
bs - боснийский
cy - валлийский
hu - венгерский
vi - вьетнамский
gl - галисийский
nl - голландский
el - греческий
ka - грузинский
gu - гуджарати
da - датский
iw - иврит
yi - идиш
id - индонезийский
ga - ирландский
is - исландский
es - испанский
it - итальянский
kn - каннада
ca - каталанский
zh-TW - китайский (традиционный)
zh-CN - китайский (упрощенный)
ko - корейский
ht - креольский (Гаити)
km - кхмерский
lo - лаосский
la - латынь
lv - латышский
lt - литовский
mk - македонский
ms - малайский
mt - мальтийский
mr - маратхи
de - немецкий
no - норвежский
fa - персидский
pl - польский
pt - португальский
ro - румынский
ru - русский
ceb - себуанский
sr - сербский
sk - словацкий
sl - словенский
sw - суахили
tl - тагальский
th - тайский
ta - тамильский
te - телугу
tr - турецкий
uk - украинский
ur - урду
fi - финский
fr - французский
hi - хинди
hmn - хмонг
hr - хорватский
cs - чешский
sv - шведский
eo - эсперанто
et - эстонский
jw - яванский
ja - японский

Функция будет работать быстрее, если перед переводом запустить InternetExplorer. Это заставит функцию использовать уже запущенный экземпляр IE, а не запускать для каждой ячейки новый(что отнимает немало времени).

Работающие в иностранных компаниях сотрудники часто вынуждены готовить по два одинаковых отчета для отечественного и зарубежного своего начальства - на русском и английском языках. Цифры в этих отчетах, естественно, одинаковые, а меняются только подписи, заголовки и другой текст.

Такую задачу можно решить более изящно. Создаем новый лист в таком отчете и называем его "Словарь". Выписываем на этот лист в любом порядке все слова, которые надо автоматически переводить. Для каждого языка добавляем новый столбец, например, так:

Потом переходим в редактор Visual Basic (меню Сервис - Макрос - Редактор Visual Basic), вставляем новый модуль (меню Insert - Module) и копируем туда текст вот этих двух макросов:

Теперь можно вернуться на лист с отчетом и запустить макрос Translate через меню Сервис - Макрос - Макросы (Tools - Macro - Macros) или нажав ALT+F8. Все слова из вашего отчета, найденные в на листе Словарь, будут заменены на слова из соседней колонки, т.е. переведены на другой язык. Запуская макрос несколько раз, мы будем по кругу переводить наш отчет последовательно на русский-английский-немецкий-русский-английский-и т.д. Выглядеть это будет так:

Praktisch, nicht wahr? :)

Ссылки по теме

А формулы с русского на аглицкий он переводит? Вообще ИМХО это очень сглупил Микрософт, что функции сделал разноязычными, он бы еще макросы команды Visual Basic тоже для каждого языка свои установил:)
А даже если переведёт функции, они будут работать только в английском Экселе, да? Можно ли как-нибудь сделать так, чтобы функции работали в Экселях всех языков?

Этот макрос переводит только ячейки с константами - формулы и функции переводить не надо, они сами

Я это проблему решаю подругому! Допустим у нас есть поля которые нужно перевести. Мы создаем в одной ячейки А1 всплывающую как бы кнопочку с двумя надписями русский и english например. И к каждой ячейки где мы хотим менять язык ставим условия. Н-р: =если(А1="русский"; А2="привет"; если(А1="english"; A2="hello"; Если А1=""; А2="привет"))

Тоже самое ко всем надписям.
Вуоля! Вы просто сверху меняете язык, а он вам меняет значение ячейки.

возник вопрос: сократила количество языков до 2х, русс и англ.
изначально все заполнено на русском. запускаю макрос - отлично все на англ. а вот обратно некоторые позиции не перевелись на русский и получились пустые ячейки.
как так?))))

Данная VBA функция позволяет перевести текст с любого языка на другой
Язык исходного текста можно не указывать - Google распознает его самостоятельно.
(т.е. вызовы res$ = Translate(txt$, "en", "ru") и res$ = Translate(txt$, "en") равнозначны)

Список кодов доступных языков для перевода:
(используются в качестве параметров функции Translate)

ar - арабский; bg - болгарский; cs - чешский;
da - датский ; de - немецкий; el - греческий;
en - английский; es - испанский; fi - финский;
fr - французский; hi - хинди; hr - хорватский;
it - итальянский; ja - японский; ko - корейский;
nl - голландский; no - норвежский; pl - польский;
pt - португальский; ro - румынский; ru - русский;
sv - шведский; zh-cn - китайский упрощенный; zh-tw - китайский традиционный

Код функции перевода:

(добавлено позже)
Видоизменил функцию - теперь перевод снова работает
(пример использования - во втором прикреплённом файле)

Если, в дальнейшем, появится необходимость разработать макрос перевода на базе другого веб-сервера (где не будет капчи),
то выложу новый макрос. (увы, кроме гугла, вариантов особо нет. остальные переводчики оставляют желать лучшего)

Ну очень нужно перевести информацию с тайского. Массив огромный, поэтому GoogleTranslate крайне необходим. Порекомендуйте, как решить проблему, где поискать, когда доработаете макрос? С искренним уважением.

Гугл недавно что-то там у себя поменял - теперь макрос перевода не работает.
Если будет время свободное, - доработаю, выложу исправленную версию.

При попытке перевода с любого языка на любой выдает ЗНАЧ. В чем может быть проблема? даже в вашем новом файле.

А в чем проблема перевода русский-украинский?
Гугл множество языков знает, - надо только указать соответствующие коды
Код русского языка - ru, код украинского - uk

разобрался, получилось, спасибо
но у меня вопрос - языки перевода укр-рус или наоборот, приходится сталкиваться

Кстати, и всякие "сложные" символы обрабатываются некорректно (аксантегю в французком итп). В целом, применение в данном виде возможно только в очень узкой области.
Приходится все эти "е с штрихами" вначале на обычные e менять итп.
Мне не подошел из-за длины, часто режет.

Сергей, мой код ничего специально не обрезает.
Либо это ограничение Google, либо мой код некорректно обрабатывает ответ сервера.

Переводит, но почему-то только первые 100-200 символов. Т.е. переводы обрезаются.
Посмотрел по коду, не нашел где :(

Не знаю, в чем проблема.
У меня всё работает, - только что проверил прикреплённый файл GoogleTranslate_New.xls
Всё корректно переводится на разные языки.

Указанная вами фраза, - перевод на датский язык. Почему у вас так работает, - не знаю.

Игорь, спасибо за доработку очень полезной функции. Давно сюда не заглядывал, т.к. "мыло" об ответе на мой предыдущий вопрос почему-то не пришло.
Теперь UDF работает отлично даже из корпоративной сети, "кастрированный" проксями, файерволлами и антифирусами.
Одна проблема: если переводимый текст состоит из нескольких предложений, оканчивающихся в соответствии с правилами грамматики точкой, восклицательным или вопросительным знаками, то переводится только первое предложение. А всё остальное игнорируется :(
Например, из текста "Привет! Это функция перевода текста на иностранный язык", который брался в качестве примера в первом выложенном здесь варианте, на английский переведётся только "Hi!"
А если поставить вместо восклицательного знака запятую: "Привет, Это функция перевода текста на иностранный язык", то переведётся полностью :"Hi, This function translation into a foreign language"
Не хорошо это.
Может быть запрос можно как-то изменить?

Если нет, то придётся мне самому "допиливать" функцию (наверное, разобью дополнительной обвеской кодом текст на предложения, переведу по одному и сцеплю в результат полного перевода, хотя это, конечно, будут "протезы" )

В связи с тем, что прежняя функция Translate давно перестала работать,
внёс в неё заметные изменения.
(см. исправленный вариант функции перевода в конце статьи)

Во втором прикреплённом файле - работающий пример перевода текста на разные языки, с использованием сервиса Google Translate

С октября 2011 Google Translate ввел ограничения на машинный перевод через API-Google v1 и v2. На примере, приведенном выше, будет переведена лишь первая строка. А остальные - "не переведено". Они захотели денег. Теперь есть расценка за определенное число переводов. Нужно получить КЛЮЧ от Google, который необходимо добавлять в ЗАПРОС (в URL-адрес).

Кто работает через прокси-сервер, не запускайте файл примера - ЗАВЕСИТЕ ЁКСЕЛЬ НАДОЛГО!
Ёксель не будет реагировать ни на что пока пока не окончатся выходы по 10-секундному таймауту для каждой из 24-х функций на листе.

Разберем пользовательскую функцию для транслитерации текста в Excel из кириллицы (русского текста) на латиницу (английский транслит), к примеру "Слон" в "Slon".

Иногда возникают ситуации, когда нам необходимо преобразовать текст на кириллице в транслит и наоборот.
Если это разовое преобразование, то можно воспользоваться одним из множества сервисов в интернете, однако если перевод носит систематический характер и имеет определенную специфику использования в Excel, то такой вариант уже не подойдет.

Рассмотрим 2 различных способа транслитерации текста:

Формула. Транслит текста можно получить с помощью функции ПОДСТАВИТЬ и последовательной замены символов из кириллицы на символ из латиницы (например, замена "б" на "b", "в" на "v", "г" на "g" и т.д.);
Пользовательская функция (UDF), макрос. Аналогичная процедура написанная на языке VBA.

В первом случае (вариант формулы) нам придется воспользоваться функцией ПОДСТАВИТЬ 66 раз (для преобразования 33 букв из русского алфавита в нижнем и верхнем регистрах), что делает применение данного варианта неудобным из-за размера применяемой формулы, поэтому подробно остановимся на варианте с пользовательской функцией.

Функция транслита в Excel

Как и всегда при создании пользовательской функции, сначала переходим в редактор Visual Basic (сочетание клавиш Alt + F11), затем создаем новый модуль и вставляем в него следующий код:

Одной из частых задач, с которыми сталкиваются пользователи программы Эксель, является преобразования числовых выражений в текстовый формат и обратно. Этот вопрос часто заставляет потратить на решение много времени, если юзер не знает четкого алгоритма действий. Давайте разберемся, как можно решить обе задачи различными способами.

Конвертация числа в текстовый вид

Все ячейки в Экселе имеют определенный формат, который задает программе, как ей рассматривать то или иное выражение. Например, даже если в них будут записаны цифры, но формат выставлен текстовый, то приложение будет рассматривать их, как простой текст, и не сможет проводить с такими данными математические вычисления. Для того, чтобы Excel воспринимал цифры именно как число, они должны быть вписаны в элемент листа с общим или числовым форматом.

Для начала рассмотрим различные варианты решения задачи конвертации чисел в текстовый вид.

Способ 1: форматирование через контекстное меню

Чаще всего пользователи выполняют форматирование числовых выражений в текстовые через контекстное меню.

Способ 2: инструменты на ленте

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

Данные преобразовываются в текстовый вариант.

Способ 3: использование функции

Ещё одним вариантом преобразования числовых данных в тестовые в Экселе является применение специальной функции, которая так и называется – ТЕКСТ. Данный способ подойдёт, в первую очередь, если вы хотите перенести числа как текст в отдельный столбец. Кроме того, он позволит сэкономить время на преобразовании, если объем данных слишком большой. Ведь, согласитесь, что перещелкивать каждую ячейку в диапазоне, насчитывающем сотни или тысячи строк – это не самый лучший выход.

На этом процедуру преобразования можно считать оконченной.

Конвертация текста в число

Теперь давайте разберемся, какими способами можно выполнить обратную задачу, а именно как преобразовать текст в число в Excel.

Способ 1: преобразование с помощью значка об ошибке

Проще и быстрее всего выполнить конвертацию текстового варианта с помощью специального значка, который сообщает об ошибке. Этот значок имеет вид восклицательного знака, вписанного в пиктограмму в виде ромба. Он появляется при выделении ячеек, которые имеют пометку в левом верхнем углу зеленым цветом, обсуждаемую нами ранее. Эта пометка ещё не свидетельствует о том, что данные находящиеся в ячейке обязательно ошибочные. Но цифры, расположенные в ячейке имеющей текстовый вид, вызывают подозрения у программы в том, что данные могут быть внесены некорректно. Поэтому на всякий случай она их помечает, чтобы пользователь обратил внимание. Но, к сожалению, такие пометки Эксель выдает не всегда даже тогда, когда цифры представлены в текстовом виде, поэтому ниже описанный способ подходит не для всех случаев.

Если подобных текстовых значений, которые следует преобразовать, не одно, а множество, то в этом случае можно ускорить процедуру преобразования.

Все данные массива будут преобразованы в указанный вид.

Способ 2: конвертация при помощи окна форматирования

Как и для преобразования данных из числового вида в текст, в Экселе существует возможность обратного конвертирования через окно форматирования.

После выполнения этих действий все значения выбранного диапазона преобразуются в нужный нам вид.

Способ 3: конвертация посредством инструментов на ленте

Перевести текстовые данные в числовые можно, воспользовавшись специальным полем на ленте инструментов.

Значения в диапазоне будут преобразованы из текстовых в числовые.

Способ 4: применение формулы

Также для преобразования текстовых значений в числовые можно использовать специальные формулы. Рассмотрим, как это сделать на практике.

Способ 5: применение специальной вставки

Следующий способ по принципу действия очень похож на предыдущий с той лишь разницей, что для его использования не нужно создавать дополнительный столбец.

Способ 7: применение макросов

Если вам часто приходится преобразовывать большие области данных из текстового формата в числовой, то имеется смысл в этих целях записать специальный макрос, который будет использоваться при необходимости. Но для того, чтобы это выполнить, прежде всего, нужно в своей версии Экселя включить макросы и панель разработчика, если это до сих пор не сделано.