سبد خرید
0

سبد خرید شما خالی است.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

جستجو و فراخوانی عکس در اکسل

انتخاب عکس در سلول
۴.۳/۵ - (۶ امتیاز)

فراخوانی عکس در سلول

حتما تا حالا به این موضوع برخورد کردید که موقع فراخوانی داده های مختلف، عکس متناسب با اون داده رو هم فراخوانی کنید. مثلا یک دیتابیس از کارکنان یک شرکت داریم. در صفحه ای دیگه میخوایم به محض اینکه کد پرسنلی اون شخص رو وارد کردیم، عکس پرسنلی که قبلا در دیتابیس قرار دادیم، فراخوانی بشه و اون عکس در سلول نمایش داده بشه. همونطور که میدونیم، توابع زیادی برای جستجو در اکسل وجود داره، توابعی مثل Vlookup, Match, Index و … اما این توابع فقط میتونن داده رو جستجو کنن و امکان جستجوی عکس ندارن. پس برای این کار باید از ترفند هایی استفاده کنیم که د رادامه به شرح آنها می پردازیم:

فرض کنید لیستی از اسم کشورها رو داریم با عکس پرچم های هر کشور. میخواهیم اسم هر کشوری که از لیست انتخاب کردیم، پرچم متناسب با اون کشور هم فراخوانی بشه.

فراخوانی

شکل ۱ – فراخوانی عکس در اکسل با فرمول

برای این کار دو روش رو ارائه میدیم:

روش اول فراخوانی عکس با استفاده از تابع Index , match در ابزار Name Manager

این روش چهار مرحله به شرح زیر داره:

مرحله اول: آماده کردن داده ها و ایجاد لیست فروریز  از داده های مورد نظر

ابتدا داده ها رو در اکسل آماده میککنیم. به اینصورت که اسم کشور ها و عکس پرچم هر کدوم از اونها رو مطابق شکل ۱ در اکسل آماده میکنیم. بعد یک لیست از اسم کشورها درست میکنیم. برای این کار از Data/ Data Validation/ List رو انتخاب میکنیم و اسم کشورها رو به عنوان ورودی تعیین میکنیم. برای آشنایی بیشتر با این ابزار مقاله مربوط به Data Validation رو مطالعه کنید.

فراخوانی عکس در اکسل - ایجاد لیست

شکل ۲ – فراخوانی عکس در اکسل – ایجاد لیست

مرحله دوم: ایجاد ارتباط بین لیست و عکس های مرتبط با هر رکورد

  • اول روی سلولی که عکس پرچم داخلش هست کلیک میکنیم (دقت داشته باشیم که سلول انتخاب بشه نه عکس)
  • سپس کلیک راست کرده و Copy رو میزنیم. یا کلید ترکیبی Ctrl+C رو میزنیم.
  • سلولی که میخواهیم عکس پرچم مرتبط با هر کشور رو نمایش بده انتخاب کرده و کلیک راست میکنیم.
  • از پنجره Paste Special گزینه Linked Picture رو انتخاب میکنیم. (شکل ۳)

فراخوانی عکس در اکسل – Linked Picture

شکل ۳ – فراخوانی عکس در اکسل – Linked Picture

با اینکار سلول مقصد، وصل میشه به سلول مبدا و با تغییر عکس موجود در سلول کپی شده، سلول مقصد هم تغییر میکنه و عکس جدید رو نشون میده.

فراخوانی عکس در اکسل- Copy/ Paste Special

شکل ۴ – فراخوانی عکس در اکسل- 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

شکل ۶- فراخوانی عکس در اکسل – روش دوم- Create from selection

با این کار، هر سلول، نام سلولی که در سمت چپش قرار داره رو میگیره و طبق قوانین نامگذاری، هر جا که space وجود داشته باشه، با _ پر میشه. مثل هنگ_کنگ. در شکل ۷ محدوده های نامگذاری شده نمایش داده شده.

فراخوانی عکس در فایل اکسل-نامگذاری اتوماتیک محدوده ها

شکل ۷- فراخوانی عکس در سلول های اکسل-نامگذاری اتوماتیک محدوده ها

حالا باید نام ها رو فراخوانی کنیم. چون میدونیم اسم کشورها به نام تبدیل شدن و هر نام داره به یک سلول ارجاع داده میشه، میتونیم از تابع Indirect برای فراخوانی مقدار داخل سلول مورد نظر استفاده کنیم. برای درک بهتر تابع Indirect حتما مقاله مربوط به این تابع مهم رو مطالعه کنید.

پس با توجه به توضیحات بالا یک نام جدید به نام Flag2 تعریف میکنیم با این فرمول:

=Indirect(D1)

اما موضوع مهمی اینجا پیش میاد، اونم اینکه برای اسم هایی که دو قسمتی هستن و فاصله دارند، باید فکری بکنیم. چون در نام تخصیص داده شده به این سلول ها، بجای Space مقدار _ گذاشته شده. برای همین داخل تابع Indirect فرمولی مینویسیم که اگر Space وجود داشت، اونو تبدیل به _ کنه. یعنی: (شکل ۸)

=INDIRECT(SUBSTITUTE($D$1,” “,”_”))

همونطور که میدونیم تابع Substitute در یک رشته متنی، یک کاراکتر رو با کاراکتری دلخواه جایگزین میکنه.

فراخوانی عکس در اکسل- ترکیب تابع indirect و substitute

شکل ۸ – فراخوانی عکس در سلول های اکسل- ترکیب تابع Indirect و Substitute

بقیه مراحل باز مشابه روش اول هست. یعنی کافیه که linked picture رو انتخاب کنیم و در نوار فرمول نام تعیین شده یعنی flag2 رو تایپ کنیم.

پس با این دو روش که تا حد زیادی به هم مشابهت داشتند، تونستیم عکس های موجود در یک فایل اکسل رو با توابع جستجو، فراخوانی کنیم. حتما به این نکته مهم توجه داشته باشیم که عکس ها باید داخل فایل اکسل موجود باشن و برای اینکه فایل اکسل خیلی سنگین نشه، تا حد امکان عکس ها رو کوچک و کم حجم کنیم. اگر عکس ها خارج از فایل اکسل هستن، با توجه به شرایط مسئله راه حل های مختلفی وجود خواهد داشت که یکی از این راه حل ها استفاده از تابع Hyperlink هست.

فایل نمونه زیر شامل هر دو روش هست و میتونید در حین مطالعه آموزش، از فایل نمونه هم استفاده کنید.

دانلود فایل نمونه فراخوانی عکس در اکسل

برای دانلود فایل نمونه فراخوانی تصویر در اکسل

آواتار
127

فارغ التحصیل لیسانس مهندسی صنایع، ارشد مدیریت صنعتی از دانشگاه تربیت مدرس و عاشق اکسل هستم. از سال 1388 که ترم 2 لیسانس بودم، به توصیه استاد مشاورم شروع به خوندن اکسل بصورت حرفه ای کردم و همچنان در حال مطالعه و یادگیری و البته آموزش به بقیه هستم.

دیدگاه کاربران
  • 989171927987 ۲۶ تیر ۱۴۰۱ / ۸:۲۹ ب٫ظ

    در یوزر فرم اکسل من جدولی تهیه کردم که بر اساس کد پرسنلی مشخص عکس مربوط به خودش هم فراخوانی میشه.
    چطور میشه فرمولی نوشت که برای هر کد پرسنلی بیش از یک عکس بتونیم درج کنیم؟

    • سامان چراغی ۲ مرداد ۱۴۰۱ / ۸:۱۹ ق٫ظ

      سلام
      میتونید چندین المان Image تو یوزرفرم قرار بدید و Visibility هر Image رو مشروط به داشتن تعداد عکس پرسنل کنید و بعد برای هر المان عکس مورد نظر رو فراخوانی کنید.

  • حمید ۲۶ اردیبهشت ۱۴۰۱ / ۱۰:۰۳ ب٫ظ

    سلام وقت بخیر
    متاسفانه پس از انجام موارد پیغام Refrence isn’t valid میده چطور میشه درستش کرد خیلی بهش نیاز دارم تشکر

    • آواتار
      حسنا خاکزاد ۲۶ اردیبهشت ۱۴۰۱ / ۱۱:۴۲ ب٫ظ

      درود
      نمیدونم چکار کردید
      دقت کنید به فرمول هایی که داخل NAME مینویسید
      یکبار با دقت عینا انحام بدید متوجه مشکل میشید

  • Fada ۲ بهمن ۱۴۰۰ / ۲:۴۸ ب٫ظ

    سلام, عکسی را در سلول A1 درج کرده‌ام و می‌خواهم وقتی که عدد ۱ را در سلول B1 مینویسم، عکس ظاهر باشد و وقتی که عدد ۲ را می‌نویسم، مخفی شود، آیا امکان دارد؟ ممنون از پاسخ جناب‌عالی

    • سامان چراغی ۲ بهمن ۱۴۰۰ / ۸:۴۱ ب٫ظ

      سلام
      برای انجام این کار از VBA میتونید استفاده کنید.
      کافیه یک روال بنویسید که حاوی شرط برای بررسی محتوای سلول B1 باشه و نهایتا با دستور زیر میتونید کامنت رو نمایش بدید یا مخفی کنید:
      Range(“A1”).Comment.Visible = True/False

    • آواتار
      حسنا خاکزاد ۲ بهمن ۱۴۰۰ / ۱۰:۳۸ ب٫ظ

      درود بر شما
      محتوای خالی رو وقتی وصل کنید به camera خلی نشون میده دیگه
      دقیقا مراحل مقاله رو طی کنید
      درست میشه. if رو مطابق با نیازتون تغییر بدید

  • jafar ۱۳ دی ۱۳۹۹ / ۱۰:۳۹ ب٫ظ

    سلام، فایلی دارم که در اون یک ستون به عنوان عکس هست اما این عکس حاوی تگ img در html است می خوم اون عکس رو در اکسل نشون بدم، امکانش هست؟

    • سامان چراغی ۱۷ دی ۱۳۹۹ / ۱۰:۲۹ ق٫ظ

      سلام، باید با استفاده از VBA آدرس فایل استخراج کنید و به کنترل Image روی Image ActiveX Control اختصاص بدید.

      • jafar ۱۸ دی ۱۳۹۹ / ۲:۰۷ ب٫ظ

        متاسفانه 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

  • milad ۷ اسفند ۱۳۹۸ / ۳:۳۳ ب٫ظ

    سلام
    ممنون از مطالب مفیدتون
    من هنگامی که روی پرچم نامش flag میزارم این خطا رو میدهReference is not valid
    اگه میشه من راهنمایی کنید

    • آواتار
      حسنا خاکزاد ۸ اسفند ۱۳۹۸ / ۱۲:۰۵ ب٫ظ

      درود
      خود عکس رو نباید نامگذاری کنید
      سلول ها نامگذار یمیشن و نام وصل میشه به عکس
      مقاله رو دوباره مطالعه کنید

  • Moahsen ۶ بهمن ۱۳۹۸ / ۹:۱۱ ق٫ظ

    درود بسیار بر شما
    مطالبتون بسیار عالی و مفید هستند

    به کمک‌تون نیاز دارم
    لطفا بزرگواری کرده و بفرمایید،
    چطور میتونم در یک شیت به‌وسیله یک دکمه، یک شکل (Shape) را در سلول انتخاب شده جای‌گزاری کنم؟
    توضیح اینکه:
    یک فرم درست کرده‌ام (چیزی شبیه فاکتور خرید) که داری چند ردیف هست، که بنا به نیاز ، یک یا چند تای آن توسط کاربر پر خواهد شد
    و در انتها نیاز هست که پایین ردیفهای پر شده خطی ایجاد شود (‌مثل بستن زیر فاکتور)
    که این خط به طور خودکار و با فشار یک دکمه (Button) جای‌گزاری گردد.

    قبلا از محبت شما سپاسگزاری میکنم

    • آواتار
      حسنا خاکزاد ۶ بهمن ۱۳۹۸ / ۱۲:۰۳ ب٫ظ

      درود بر شما
      اگر حتما میخواید دکمه باشه، باید کد نویسی کنید
      اما اینکار و میتونید با فمرول نویسی در conditional formatting انجام بدید با این شرط که هر موقع سلول بالایی پر بود و پایینی خالی، خط رو رسم کنه

      • Moahsen ۶ بهمن ۱۳۹۸ / ۵:۲۰ ب٫ظ

        درود گرامی
        بله لازم هست به شکل دکمه باشد
        سپاسگزار خواهم بود در مورد کد، راهنمایی بفرمائید.

        • آواتار
          حسنا خاکزاد ۷ بهمن ۱۳۹۸ / ۱۰:۱۴ ق٫ظ

          ماکرو ضبط کنید و کد رو ویرایش کنید
          این مقاله مربوط به ضبط ماکرو
          https://excelpedia.net/excel-macro/

          • Moahsen ۷ بهمن ۱۳۹۸ / ۱۰:۵۴ ق٫ظ

            درود بسیار گرامی

            از راهنمایی تون سپاس فراوان دارم
            پاینده باشید و سربلند
            با آرزوی پیشرفت و موفقیت شما

  • موسوی ۲۶ آذر ۱۳۹۸ / ۲:۱۹ ب٫ظ

    سلام
    امکان فراخوانی تصویر از یک فولدر هم هست؟

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

توسط
تومان