جستجو و فراخوانی عکس در اکسل
فراخوانی عکس در سلول
حتما تا حالا به این موضوع برخورد کردید که موقع فراخوانی داده های مختلف، عکس متناسب با اون داده رو هم فراخوانی کنید. مثلا یک دیتابیس از کارکنان یک شرکت داریم. در صفحه ای دیگه میخوایم به محض اینکه کد پرسنلی اون شخص رو وارد کردیم، عکس پرسنلی که قبلا در دیتابیس قرار دادیم، فراخوانی بشه و اون عکس در سلول نمایش داده بشه. همونطور که میدونیم، توابع زیادی برای جستجو در اکسل وجود داره، توابعی مثل Vlookup, Match, Index و … اما این توابع فقط میتونن داده رو جستجو کنن و امکان جستجوی عکس ندارن. پس برای این کار باید از ترفند هایی استفاده کنیم که د رادامه به شرح آنها می پردازیم:
فرض کنید لیستی از اسم کشورها رو داریم با عکس پرچم های هر کشور. میخواهیم اسم هر کشوری که از لیست انتخاب کردیم، پرچم متناسب با اون کشور هم فراخوانی بشه.
شکل ۱ – فراخوانی عکس در اکسل با فرمول
برای این کار دو روش رو ارائه میدیم:
روش اول – فراخوانی عکس با استفاده از تابع Index , match در ابزار Name Manager
این روش چهار مرحله به شرح زیر داره:
مرحله اول: آماده کردن داده ها و ایجاد لیست فروریز از داده های مورد نظر
ابتدا داده ها رو در اکسل آماده میککنیم. به اینصورت که اسم کشور ها و عکس پرچم هر کدوم از اونها رو مطابق شکل ۱ در اکسل آماده میکنیم. بعد یک لیست از اسم کشورها درست میکنیم. برای این کار از Data/ Data Validation/ List رو انتخاب میکنیم و اسم کشورها رو به عنوان ورودی تعیین میکنیم. برای آشنایی بیشتر با این ابزار مقاله مربوط به Data Validation رو مطالعه کنید.
شکل ۲ – فراخوانی عکس در اکسل – ایجاد لیست
مرحله دوم: ایجاد ارتباط بین لیست و عکس های مرتبط با هر رکورد
- اول روی سلولی که عکس پرچم داخلش هست کلیک میکنیم (دقت داشته باشیم که سلول انتخاب بشه نه عکس)
- سپس کلیک راست کرده و Copy رو میزنیم. یا کلید ترکیبی Ctrl+C رو میزنیم.
- سلولی که میخواهیم عکس پرچم مرتبط با هر کشور رو نمایش بده انتخاب کرده و کلیک راست میکنیم.
- از پنجره Paste Special گزینه Linked Picture رو انتخاب میکنیم. (شکل ۳)
شکل ۳ – فراخوانی عکس در اکسل – Linked Picture
با اینکار سلول مقصد، وصل میشه به سلول مبدا و با تغییر عکس موجود در سلول کپی شده، سلول مقصد هم تغییر میکنه و عکس جدید رو نشون میده.
شکل ۴ – فراخوانی عکس در اکسل- Copy/ Paste Special
در شکل ۴، سلول B1 رو کپی کردیم و در سلول E1 link picture کردیم. و رهر چیزی که در سلول B1 قرار بگیره در سلول E1 نمایش داده خواهد شد. اما هنوز این سلول به لیست فروریز وصل نشده. پس باید کاری کنیم که این اتصال برقرار بشه. چون هدف اصلی این هست که با تغییر داده از لیست فروریز، عکس پرچم کشور مربوطه نمایش داده بشه. دقت کنیم که نتیجه linked picture یک عکسه که هر جای اکسل میتونه قرار بگیره و محدوده به سلول E1 نیست.
مرحله سوم: ایجاد محدوده نامگذاری شده با Name Manager
همونطور که دیدیم، عکس ایجاد شده فقط به یک سلول وصله. حالا باید کاری کنیم که با تغییر سلول عکس مربوطه فراخوانی بشه. برای این کار از تب Formula روی گزینه Define Name کلیک میکنیم. نام دلخواه رو تایپ کرده و در قسمت refer to فرمول زیر رو وارد کرده و Ok رو میزنیم: (شکل ۵)
=INDEX(‘Lookup Picture’!$B$1:$B$8,MATCH(‘Lookup Picture’!$D$1,’Lookup Picture’!$A$1:$A$8,0))
شکل ۵ – فراخوانی عکل در اکسل – نامگذاری محدوده
این فرمول میاد مکان سلول انتخاب شده از لیست فروریز رو در ستون نام کشور ها گیدا میکنه و سلول متناظرش در ستون پرچم رو به عنوان خروجی تعیین میکنه.
مرحله چهارم: اتصال image ایجاد شده به نام تعیین شده
حالا کافیه که Image ایجاد شده از linked picture رو انتخاب کنیم و در نوار فرمول تایپ کنیم =flag و enter رو بزنیم.
این ترفند چطور کار میکنه؟
وقتی ما یک linked picture ایجاد کردیم، فقط به سلولی متصل بود که کپی شده بود. بعد ما از طریق نامگذاری محدوده، رفرنس این Image رو تغییر دادیم. این محدوده نامگذاری شده وابسته به سلول انتخاب شده از لیست فروریز است و با فرمولی که نوشتیم، هر بار، آدرس سلول متناظر با نام کشور، در ستون پرچم رو به عنوان خروجی بهمون میده.
مثلا در D1 کشور جامائیکا رو انتخاب میکنیم، با این کار خروجی فرمولی که در Name manager نوشتیم، میشه سلول B6 و این سلول در واقع قراره تصویرش Image ایجا دشده در Linked picture نمایش داده بشه.
فرمولی که در Name manager نوشته میشه باید خروجی از جنس Reference داشته باشه که این مهم رو تابع Index به ما میده.
روش دوم: فراخوانی عکس با استفاده از Indirect و Name Manager
این روش شباهت زیادی به روش بالا داره. در واقع مرحله یک و دو و چهار مشابه روش قبل هست و فقط در مرحله سوم یعنی نامگذاری محدوده، تفاوت داره. پس اول داده ها رو آماده میکنیم و بعد از ایجاد لیست فروریز و کپی کردن یکی از سلول ها و ایجاد linked picture ، طبق روش زیر کار رو ادامه میدیم:
در این مرحله باید محدوده پرچم ها رو از طریق Name Manager نامگذاری کنیم و سلول هر پرچم نام کشور مربوطه باشه. یعنی نام سلول B1 معادل “هند” باشه و الی آخر. برای اینکه تک تک مجبور به نامگذاری نباشیم، از مسیر زیر اقدام به نامگذاری ستون پرچم ها میکنیم:
Formula/ Create from selection
شکل ۶- فراخوانی عکس در اکسل – روش دوم- Create from selection
با این کار، هر سلول، نام سلولی که در سمت چپش قرار داره رو میگیره و طبق قوانین نامگذاری، هر جا که space وجود داشته باشه، با _ پر میشه. مثل هنگ_کنگ. در شکل ۷ محدوده های نامگذاری شده نمایش داده شده.
شکل ۷- فراخوانی عکس در سلول های اکسل-نامگذاری اتوماتیک محدوده ها
حالا باید نام ها رو فراخوانی کنیم. چون میدونیم اسم کشورها به نام تبدیل شدن و هر نام داره به یک سلول ارجاع داده میشه، میتونیم از تابع Indirect برای فراخوانی مقدار داخل سلول مورد نظر استفاده کنیم. برای درک بهتر تابع Indirect حتما مقاله مربوط به این تابع مهم رو مطالعه کنید.
پس با توجه به توضیحات بالا یک نام جدید به نام Flag2 تعریف میکنیم با این فرمول:
=Indirect(D1)
اما موضوع مهمی اینجا پیش میاد، اونم اینکه برای اسم هایی که دو قسمتی هستن و فاصله دارند، باید فکری بکنیم. چون در نام تخصیص داده شده به این سلول ها، بجای Space مقدار _ گذاشته شده. برای همین داخل تابع Indirect فرمولی مینویسیم که اگر Space وجود داشت، اونو تبدیل به _ کنه. یعنی: (شکل ۸)
=INDIRECT(SUBSTITUTE($D$1,” “,”_”))
همونطور که میدونیم تابع Substitute در یک رشته متنی، یک کاراکتر رو با کاراکتری دلخواه جایگزین میکنه.
شکل ۸ – فراخوانی عکس در سلول های اکسل- ترکیب تابع Indirect و Substitute
بقیه مراحل باز مشابه روش اول هست. یعنی کافیه که linked picture رو انتخاب کنیم و در نوار فرمول نام تعیین شده یعنی flag2 رو تایپ کنیم.
پس با این دو روش که تا حد زیادی به هم مشابهت داشتند، تونستیم عکس های موجود در یک فایل اکسل رو با توابع جستجو، فراخوانی کنیم. حتما به این نکته مهم توجه داشته باشیم که عکس ها باید داخل فایل اکسل موجود باشن و برای اینکه فایل اکسل خیلی سنگین نشه، تا حد امکان عکس ها رو کوچک و کم حجم کنیم. اگر عکس ها خارج از فایل اکسل هستن، با توجه به شرایط مسئله راه حل های مختلفی وجود خواهد داشت که یکی از این راه حل ها استفاده از تابع Hyperlink هست.
فایل نمونه زیر شامل هر دو روش هست و میتونید در حین مطالعه آموزش، از فایل نمونه هم استفاده کنید.
دانلود فایل نمونه فراخوانی عکس در اکسل
برای دانلود فایل نمونه فراخوانی تصویر در اکسل
با سلام
امکان فراخوانی عکس از یک شیت به شیت دیگر هم هست؟
درود بر شما
بله فرقی نمیکنه
برای فراخوانی عکس در شیت دیگر امتحان کردم نشد.فکر کنم نیازه یک قسمتی به فرمول اضافه بشه.
فرقی نمیکنه
حواستون باشه در نام که فرمول می نویسید ، بر اساس شیتی باشه که عکس ها داخلشه
در یوزر فرم اکسل من جدولی تهیه کردم که بر اساس کد پرسنلی مشخص عکس مربوط به خودش هم فراخوانی میشه.
چطور میشه فرمولی نوشت که برای هر کد پرسنلی بیش از یک عکس بتونیم درج کنیم؟
سلام
میتونید چندین المان Image تو یوزرفرم قرار بدید و Visibility هر Image رو مشروط به داشتن تعداد عکس پرسنل کنید و بعد برای هر المان عکس مورد نظر رو فراخوانی کنید.
سلام
من میخام تو یوز فرم vba کد پرسنلی که زدم عکس طرف بیاد
میشه فرمول ش را بفرستید برام یا راهنمایی کنید
تشکر
درود
میتونید عکس ها رو با نام کد پرسنلی ذخیره کنید و در یک فولدر قرار بدید
بعدموقع سرچ برید توی فولدر و عکسی که اسمش با کد پرسنلی برابره رو پیدا کنید
سلام وقت بخیر
متاسفانه پس از انجام موارد پیغام Refrence isn’t valid میده چطور میشه درستش کرد خیلی بهش نیاز دارم تشکر
درود
نمیدونم چکار کردید
دقت کنید به فرمول هایی که داخل NAME مینویسید
یکبار با دقت عینا انحام بدید متوجه مشکل میشید
سلام, عکسی را در سلول A1 درج کرده‌ام و می‌خواهم وقتی که عدد ۱ را در سلول B1 مینویسم، عکس ظاهر باشد و وقتی که عدد ۲ را مینویسم، مخفی شود، آیا امکان دارد؟ ممنون از پاسخ جناب‌عالی
سلام
برای انجام این کار از VBA میتونید استفاده کنید.
کافیه یک روال بنویسید که حاوی شرط برای بررسی محتوای سلول B1 باشه و نهایتا با دستور زیر میتونید کامنت رو نمایش بدید یا مخفی کنید:
Range(“A1”).Comment.Visible = True/False
درود بر شما
محتوای خالی رو وقتی وصل کنید به camera خلی نشون میده دیگه
دقیقا مراحل مقاله رو طی کنید
درست میشه. if رو مطابق با نیازتون تغییر بدید
سلام، فایلی دارم که در اون یک ستون به عنوان عکس هست اما این عکس حاوی تگ img در html است می خوم اون عکس رو در اکسل نشون بدم، امکانش هست؟
سلام، باید با استفاده از VBA آدرس فایل استخراج کنید و به کنترل Image روی Image ActiveX Control اختصاص بدید.
متاسفانه vba بلد نیستم
سلام، اگر عکسهایی با شماره های ۱ تا ۵۰ در یک فولدر با آدرس ثابت داشته باشیم و در هر گزارش این عکسها تغییر کنند ولی شماره های ثابت داشته باشند و هر عکس در یک قسمت ثابت و خاص از گزارش باید قرار بگیره روش کار چی هست بخصوص اگر سایز عکسها متفاوت باشند و در گزارش نیاز به تغییر سایز عکسها لازم باشه.
درودبه نظر میرسه نیاز به کد نویسی باشه مخصوصا که نیاز به تغییر سایز فایل هست
چون فراخوانی فایل از فولدر با تابع Hyperlink مقدور هست که اونم نیاز به کلیک داره برای نمایش
سلام. بابت آموزش ارائه شده بسیار ممنونم. اگه بخوام یک عکس از اینترنت رو به یک سلول لینک بدم جوری که اگه شکل توی اینترنت با همون لینک تغییر کرد سلول من هم تغییر کنه. واسه نمودارهای بورس میخوام که روبروی نماد سهم نمودار کوچولوش رو نشون بده.
مثل این لینک:
http://www.tsetmc.com/tsev2/chart/img/Inst.aspx?i=7745894403636165
ممنونم.
درود
منطقی اینه که داده ها رو فراخوانی کنید و روی اونها نمودار رو رسم کنید
در مورد لینک به وب نهایتا میتونید هایپرلینک بزنید که هربار باید باز کنید
یا اینکه ببینید از طریق کوئری، تعریف شده که بتونید بیارید توی اکسلتون؟
سلام
از خروجی اکسل سایت بورس کانکشن گرفتم ولی داده های گذشته رو نمیده و نمیشه نمودار رسم کرد
هایپرلینکها هم وقتگیرن
میخوام توی یه نگاه رشد همه سهم ها رو ببینم.
بهر حال از کمکتون ممنونم.
جوابم رو پیدا کردم. اگه ممکنه آموزشش رو توی صفحه جداگانه ای به اطلاع بقیه نیز برسانید. این ترفند توی هیچ سایت فارسی زبانی نیست.
Sub MacroPIC()
Dim a As Range
Dim b As Range
Dim x As String
Dim y As String
Dim z As String
Set b = ActiveSheet.Range(“c6:c35″)
z = ” ”
For Each a In b
If a.Value = “” Then Exit For
x = “c” & a.Row
y = “a” & a.Row
ActiveSheet.Range(x).Select
On Error Resume Next
ActiveSheet.Shapes.AddPicture Filename:=ActiveSheet.Range(x).Value, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoCTrue, _
Left:=ActiveSheet.Range(x).Left, _
Top:=ActiveSheet.Range(x).Top + 2, _
Width:=ActiveSheet.Range(x).Width, _
Height:=ActiveSheet.Range(x).Height
z = z & ActiveSheet.Range(y).Value & ” ”
Next
توی مثال قبل آدرس لینک عکس توی سلول نوشته شده بود ولی توی خود ماکرو هم میشه گذاشت:
ActiveSheet.Shapes.AddPicture Filename:=http://www.tsetmc.com/tsev2/chart/img/Inst.aspx?i=33629260529503413, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoCTrue, _
Left:=ActiveSheet.Range(x).Left, _
Top:=ActiveSheet.Range(x).Top + 2, _
Width:=ActiveSheet.Range(x).Width, _
Height:=ActiveSheet.Range(x).Height
سلام
ممنون از مطالب مفیدتون
من هنگامی که روی پرچم نامش flag میزارم این خطا رو میدهReference is not valid
اگه میشه من راهنمایی کنید
درود
خود عکس رو نباید نامگذاری کنید
سلول ها نامگذار یمیشن و نام وصل میشه به عکس
مقاله رو دوباره مطالعه کنید
درود بسیار بر شما
مطالبتون بسیار عالی و مفید هستند
به کمکتون نیاز دارم
لطفا بزرگواری کرده و بفرمایید،
چطور میتونم در یک شیت به‌وسیله یک دکمه، یک شکل (Shape) را در سلول انتخاب شده جای‌گزاری کنم؟
توضیح اینکه:
یک فرم درست کرده‌ام (چیزی شبیه فاکتور خرید) که داری چند ردیف هست، که بنا به نیاز ، یک یا چند تای آن توسط کاربر پر خواهد شد
و در انتها نیاز هست که پایین ردیفهای پر شده خطی ایجاد شود (مثل بستن زیر فاکتور)
که این خط به طور خودکار و با فشار یک دکمه (Button) جای‌گزاری گردد.
قبلا از محبت شما سپاسگزاری میکنم
درود بر شما
اگر حتما میخواید دکمه باشه، باید کد نویسی کنید
اما اینکار و میتونید با فمرول نویسی در conditional formatting انجام بدید با این شرط که هر موقع سلول بالایی پر بود و پایینی خالی، خط رو رسم کنه
درود گرامی
بله لازم هست به شکل دکمه باشد
سپاسگزار خواهم بود در مورد کد، راهنمایی بفرمائید.
ماکرو ضبط کنید و کد رو ویرایش کنید
این مقاله مربوط به ضبط ماکرو
https://excelpedia.net/excel-macro/
درود بسیار گرامی
از راهنمایی تون سپاس فراوان دارم
پاینده باشید و سربلند
با آرزوی پیشرفت و موفقیت شما
سلام
امکان فراخوانی تصویر از یک فولدر هم هست؟
درود بر شما
از تابع hyperlink استفاده کنید
https://excelpedia.net/hyperlink-function/