راهنمای ترجمه متن با اکسل(ترکیب قدرت Google SheetsوAPI گوگل)
فرض کنید درون سلول های اکسل، یک متن یا کلماتی رو دارید و میخواهید این متن یا کلمات را بصورت داینامیک ترجمه کنید یعنی اگر کلمه شما تغییر کرد بلافاصله ترجمه جدید نوشته شود.
احتمالا میدانید که درون اکسل تابعی برای اینکار تعریف نشده است، اما با کمک گوگل شیت می توانید براحتی اینکار رو انجام بدهید چون در گوگل شیت تابعی با عنوان GoogleTranslate وجود دارد که براحتی می توانید متن یا کلمات درون سلول ها را به زبان های مختلف ترجمه کنید.
آموزش استفاده از گوگل شیت
برای استفاده از گوگل شیت فقط یک اکانت گوگل نیاز دارید. و برای ورود به صفحه گوگل شیت کافی است در مرورگر خود عبارت Sheet.New را تایپ کنید.
پس از این مرحله اگر قبلا وارد حساب گوگل شده باشید مستقیما صفحه گوگل شیت برایتان باز می شود و در غیر اینصورت نیاز دارید اطلاعات حسابتان را وارد کنید یا یک حساب جدید بسازید.
ترجمه متن با اکسل با تابع GOOGLETRANSLATE
در صفحه باز شده گوگل شیت کافی است ، با کمک تابع GOOGLETRANSLATE به راحتی می توانید ترجمه متن مورد نظر را داشته باشید.
در قسمت اول تابع باید سلولی که حاوی متن مورد نظر شماست انتخاب کنید
در قسمت دوم کد مربوط به زبان متن وارد شده را وارد کنید
در قسمت سوم کد مربوط به زبان مقصد که میخواهید را مانند تصویر زیر وارد کنید.
اما این موضوع همه چیزی که میخواستم بگم نیست!!
بلکه به عنوان کسی که عاشق آموزش اکسل هست همیشه هر قابلیتی که در گوگل شیت میبینم رو دوست دارم توی اکسل هم داشته باشم، پس اجازه بدید تا در ادامه این کار رو با وجود اینکه در اکسل مستقیما تابعی برای این کار نداریم با استفاده از API گوگل انجام بدهیم.
ترجمه متن با اکسل با API گوگل
بعد از کلی تحقیق راجع به این موضوع در نهایت به لینکی رسیدم که اگر داخلش متن و کد زبان مبدا و زبان مقصد رو بهش بدین میتوانید یک فایل JSON حاوی ترجمه داده بدست بیارید.
https://translate.googleapis.com/translate_a/single?client=gtx&dt=t&sl=en&tl=fa&q=Hello
اگر به لینک بالا دقت کنید سه متغیری که تابع GOOGLETRANSLATE داشت رو میتوانید پیدا کنید و در نتیجه فایل حاوی ترجمه رو داشته باشید.
اما چطور از داخل این فایل با توابع اکسل متن مورد نظر خودمون رو استخراج کنیم؟
با استفاده از تابع WEBSERVICE و قراردادن لینک فوق درون این تابع میتونید بجای فایل متن درون فایل رو داشته باشید.
=WEBSERVICE("https://translate.googleapis.com/translate_a/single?client=gtx&dt=t&sl=en&tl=fa&q=Hello")
اما این متن چون با ساختار JSON هست کاراکترهای اضافه داره ولی با یک نگاه میتونید متوجه بشید که ترجمه کلمه مورد نظر شما درون اولین دبل کوتیشن (“) وجود داره، در نتیجه شما میتونید با توابع اکسل متن درون دبل کوتیشن رو استخراج کنید.
به عنوان مثال تابع زیر میتونه جایگاه اولین کاراکتر دبل کوتیشن رو بهتون بده.
=MID(json, quotePos, SEARCH("""", json, quotePos + 1) - quotePos)
اما پیدا کردن جایگاه دومین کاراکتر دبل کوتیشن یکم سخت تر هست و نیاز با خروجی قسمت قبل هم که اسمش رو گذاشتم quotePos داریم.
=MID(json, quotePos, SEARCH("""", json, quotePos + 1) - quotePos)
در نهایت اگر قسمت های مختلف رو درون تابع LET تعریف کنیم میتونیم تابعی شبیه تابع GOOGLETRANSLATE بصورت زیر بسازیم.(با فرض اینکه متن انگلیسی درون سلول B2 باشد)
=LET(
phrase,B2,
from, "en",
to, "fa",
json, WEBSERVICE("https://translate.googleapis.com/translate_a/single?client=gtx&dt=t&sl="&from&"&tl="&to&"&q="&phrase),
quotePos, SEARCH("""", json) + 1,
result,MID(json, quotePos, SEARCH("""", json, quotePos + 1) - quotePos),result
)
البته این کار با توابع جدیدی مثل TEXTSPLIT که در آفیس 365 وجود داره شاید راحت تر انجام بشه اما چون میخوام در ورژن 2021 آفیس که در دسترس تر هست قابل استفاده باشه از این تابع استفاده نکردم.
=LET(phrase,B5,from,"en",to,"fa",endpoint,"https://translate.googleapis.com/translate_a/single?client=gtx&dt=t&sl=",
api,endpoint&from&"&tl="&to&"&q="&phrase,json,WEBSERVICE(api),INDEX(TEXTSPLIT(json,CHAR(34)),2))
دقت کنید که برای استفاده از این تابع باید ارتباط اینترنتی شما متصل باشه و همینطور اگر متن درون سلول شما حاوی نقطه باشه چون در خروجی JSON درون کوتیشن بعدی قرار میگیره نیاز هست که فرمول رو متناسب با اون تغییر بدید و چه بسا که در اون حالت فرمول خیلی پیچیده تر میشه.
در نهایت اگر تابع LAMBDA رو دارید میتونید بدون برنامه نویسی درون خود اکسل با قراردادن فرمول زیر درون قسمت NAME MANAGER تابعی شبیه تابع GOOGLETRANSLATE داشته باشید.
=LAMBDA(text,from,to,
LET(phrase,text,from,from,to,to,endpoint,"https://translate.googleapis.com/translate_a/single?client=gtx&dt=t&sl=",
api,endpoint&from&"&tl="&to&"&q="&phrase,json,WEBSERVICE(api),INDEX(TEXTSPLIT(json,CHAR(34)),2)))
در نهایت این تابع رو برای پاورکوئری هم تعریف کردم که از طریق لینک زیر میتونید کد مربوط به تابع رو استفاده کنید.
https://github.com/Softwaretrain/M-Functions/blob/main/fnTranslate
همچنین فایل حاوی تابع اکسل و پاورکوئری رو میتونید از طریق لینک زیر دانلود کنید.
4 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
درود بر مهندس عزیز دمت گرم مثل همیشه عالی و پر قدرت ????????
مطعنم اگه این مقاله رو نمیخوندم هیچ وقت فکرشم نمیکردم با اکسل بشه ترجمه کرد
سلام استاد
خیلی زحمت کشیدین واقعا مشخصه
ولی من فایل رو که باز کردم و یک سلول ترجمه شده رو به حالت ادیت فرمول در آوردم و اینتر زدم ارور value داد
نمیدونم چرا. اکسلم هم 2021 هست
سلام تشکر
این موردی که فرمودید چک شد ولی اوکی بود !