مطالب برتر
حضرت علی (ع): «زَکوةُ العِلمِ نَشرُهُ؛ زکات علم نشر آن است».
وبلاگ "ترفندها" (سایت پی‌سی‌ترفند) برای عمل به این حدیث شریف و با هدف ارتقاء دانش و مهارت‌های مرتبط با رایانه در ۳۰ مهرماه ۱۳۹۱ ایجاد شده است. به امید آنکه مفید واقع شود.
🔵 کانال پی‌سی‌ترفند در تلگرام:
telegram.me/pctarfandir
🔵 ارتباط با ادمین:
telegram.me/pctarfand_admin
تبليغات
۱۵
بهمن ۹۳
نظرات
۲۲
ترکیب محتویات تعداد زیادی سلول در یک سلول در اکسل
مرادی
قبلا در این مطلب، دو روش برای ترکیب محتویات چند سلول در یک سلول در نرم‌افزار اکسل ارائه شد که یکی از این دو روش استفاده از تابع CONCATENATE بود. در واقع برای ترکیب چند سلول باید آدرس آن‌ها را به عنوان ورودی در این تابع قرار داد. مشکلی که وجود دارد این است که زمانیکه تعداد این ورودی‌ها زیاد باشد تایپ یا انتخاب سلولها بسیار زمان‌بر است. در ادامه مطلب با پی‌سی‌ترفند همراه باشید تا با نحوه حل این مشکل آشنا شوید.
برای حل این مشکل ۳ روش وجود دارد:
روش ۱) با استفاده از اکسل:
فرض کنید لیستی مشابه لیست زیر (A1 تا A11) دارید و قصد دارید متن تمام سلولها را با هم ترکیب کنید.
قبل از حل مشکل فوق ابتدا باید با تابع Transpose آشنا شویم.
تابع Transpose:
کلمه Transpose به معنای ترانهادن است و در اکسل با استفاده از تابع Transpose میتوان مکان ردیف‌ها و ستون‌ها را تغییر داد. همانطور که در تصویر زیر مشاهده می‌کنید جدول زیر دارای ۷ ردیف و ۵ ستون می‌باشد.
کلمه Transpose به معنای ترانهادن است و در اکسل با استفاده از تابع Transpose میتوان مکان ردیف‌ها و ستون‌ها را تغییر داد. همانطور که در تصویر زیر مشاهده می‌کنید جدول زیر دارای ۷ ردیف و ۵ ستون می‌باشد.
برای ترانهادن جدول فوق، یعنی تبدیل آن به ۷ ستون و ۵ ردیف، محدوده‌ای خالی که شامل ۷ ستون و ۵ ردیف باشد را انتخاب نمایید. سپس در نوار فرمول عبارت زیر را وارد نمایید.
=TRANSPOSE(A1:E7)
برای اینکه عمل ترانهادن برای همه ۳۵ سلول جدول فوق انجام شود باید از فرمول‌نویسی آرایه‌ای استفاده کرد که در اینجا آموزش داده شده است. بنابراین برای اینکه فرمول فوق بصورت آرایه‌ای عمل کند بعد از وارد کردن فرمول بالا، کلیدهای Ctrl+Shift+Enter را همزمان فشار دهید تا نتیجه زیر حاصل شود.
منبع: www.besttutorial.ir
پس از آشنایی با تابع Transpose، برای ترکیب محتویات تعداد زیادی سلول در یک سلول، مراحل زیر را طی کنید:
۱- در یک سلول خالی که می‌خواهید ترکیب سلولها در آن قرار گیرد فرمول زیر را تایپ کنید.
=CONCATENATE(TRANSPOSE(A1:A11))
البته اگر می‌خواهید که بین عبارات سلولها یک فاصله یا ویرگول باشد فرمول‌های زیر را تایپ کنید:
=CONCATENATE(TRANSPOSE(A1:A11&" "))
یا
=CONCATENATE(TRANSPOSE(A1:A11&&","))
۲- پس از تایپ فرمول، کلید اینتر را فشار ندهید. قسمت (" "&TRANSPOSE(A1:A11 را انتخاب کنید و کلید F9 را فشار دهید. اگر به هر دلیلی نمی‌توانید از F9 استفاده کنید کلیدهای Ctrl و = را همزمان فشار دهید.
نکته: برای نمایش مقدار واقعی یک فرمول، کافیست فرمول مورد نظر را انتخاب کرده و کلید F9 را فشار دهید. در صورتیکه کلید Esc را فشار دهید مجدداً فرمول مربوط به آن سلول نشان داده می‌شود.
۳- مشاهده می‌کنید که متن تمام سلولهای A1 تا A11 ظاهر شده است درحالیکه بین آن‌ها ویرگول و اطرافشان کروشه {} قرار دارد. کروشه‌های دو طرف را پاک کنید.
۴- حالا کلید اینتر را فشار دهید و نتیجه را ببینید. تمام.
نکته: اگر سلول‌هایی که قصد دارید با هم ترکیب کنید بصورت ردیفی بودند مثلا A1 تا K1 دیگر نیازی نیست از دستور TRANSPOSE استفاده کنید. فقط پس از فشردن F9 کروشه‌ها را پاک کنید.
روش ۲) با استفاده از Notepad و Word:
برای ترکیب سلولها در خارج از اکسل، باید از Word استفاده کرد. اما ابتدا باید کلمات را از جدول خارج کنیم. میتوان جدول را مستقیما در ورد از بین برد یا اینکه ابتدا در Notepad اینکار را انجام داد و سپس به ورد منتقل کرد. کلمات را اگر از لیست اشاره شده در روش ۱ را کپی کنید و سپس در Notepad پیست کنید جدول از بین می‌رود و تبدیل به متن می‌شود بصورت زیر:
سپس میتوان آن‌ها را به ورد انتقال داد تا سایر عملیات روی آن‌ها انجام شود. البته بدون نیاز به Notepad هم میتوان جدول را در ورد از بین برد. اگر بعد از کپی کلمات در اکسل، آن‌ها را بصورت معمولی (Ctrl+V) در ورد پیست کنید کلمات همراه با جدول در ورد پیست می‌شوند و نمی‌توان آن‌ها را با هم ترکیب کرد. برای از بین بردن جدول کافیست زمانیکه می‌خواهید لیست را در ورد پیست کنید در هنگام پیست گزینه Keep text only را انتخاب کنید. در اینصورت کلمات بصورت text و بدون جدول در ورد کپی می‌شوند.
در پایان کافیست خطوط را به هم بچسبانید تا کلمات کنار هم قرار بگیرند. برای این کار کافیست به این مطلب مراجعه کنید.
روش ۳) با استفاده از ++Notepad (اینجا و اینجا و اینجا)
برای مطلع شدن از جدیدترین مطالب سایت، کانال پی‌سی‌ترفند در تلگرام را با آیدی pctarfandir@ دنبال کنید:
|
سوالات خود در زمینه ورد و اکسل و ... را میتوانید از طریق ایمیل و تلگرام با ما مطرح کنید. آدرس ایمیل: tarfandha.blog@gmail.com آیدی تلگرام: pctarfand_admin@ |
نظرات (۲۲)
-
محمد
سلام
ممنون از آموزش خوبتون
من میخام تکست چند تا سلول رو به یه سلول انتقال بدم و بین شون هم ویرگول بزارم
از چه فرمولی باید استفاده کنم؟-
پاسخ:
-
-
رضا
سلام وقت بخیر
میخوام نام دانش اموزان در زیر عنوان کلاسها مخفی کنم مثلا کلاس ریاضی کلاس فارسی و همین طور کلاسهای بعد
حالا میخوام اگر دانش اموزی در دو تا یا بیشتر از دوتا کلاس را شرکت کرده وقتی کلاس فارسی و ریاضی در یک ستون قرار بگیرد (مثلا ستونی که ساعت 7صبح رو مشخص کرده)این دو تا عنوان مثلا قرمز بشه یا خطا بزنه که نشان بده در زیر مجموعه ها فردی هست هر دودرس رو داره و کلاسهاش تداخل داره برای این کار قابلیتی هست؟ تشکر اگر جواب بدین-
پاسخ:
سلامبرای تصور بهتر، در صورت امکان یک نمونه از فایلتان را برای من ارسال کنید.
-
-
محمد
چطور میشه بین این سه ستونی که می‌خواهیم یکی بشوند، به اکسل بگیم برای هر سلول یک اینتر فاصله بزنه؟ اینتر چی میشه؟-
پاسخ:
سلامکدام سه ستون؟
-
-
علی
سپاس فراوان -
غزل
سلام . من مىخواستم از اىن روش براى ترکىب چند حرف استفاده کنم که در نهاىت کلمه رو بسازم . نشد . حروف رو بهم وصل نکرد حتى کلمات رو هم جمله نساخت . در واقع حرف و کلمه رو درنهاىت داخل "" مىاورد . در صورتى که من از روشى استفاده کردم که فاصله نندازه چىکار کنم ؟-
پاسخ:
سلاماحتمالا روش های گفته شده را درست اجرا نکرده اید.
-
-
sanaz
سلام من شدیدا تو یه مسئله به مشگل برخوردم من تعدادی آیدی دارم که میخوام قبل از این آیدی ها @ بزارم مثلا این رو sanaz211 تبدیل کنم به این sanaz211@ مثل تگ کردن در اینستاگرام .تعداد آیدی ها خیلی بالاست و من از طریق فرمت سل اکسل نتونستم اینکار رو انجام بدم چون عمل نمیکنه میتونید راهی بهم بگید که بتونم قبل از همه این آیدی ها @ بزارم به صورت دستی و دونه دونه خیلی مشکله-
پاسخ:
سلامبا فرض اینکه آیدی ها در ستون A است در ستون B از فرمول زیر استفاده کنید.="@"&A1
موفق باشید.
-
-
محمد
واقعا فوق العاده بود......
ممنون...ممنون....ممنون....
خیلی عالی بود خیلی کارمو راه انداخت.......
فوقالعاده اید شما....... -
Mina
من دو تا ستون دارم که در ستون b مقادیر تکراری دارم
میخوام هرجا مقادیر تکراری در b دارم مقادیرشان در ستون a با علامت جمع کنار هم آورده بشه
نهایتا ستون b من دیگه تکراری نداره انگار هر جا که ستون b تکراری داشته مقادیر a با هم مرج بشن
امیدوارم متوجه منظورم شده باشید-
پاسخ:
-
-
علی
سلام . من دقیقا روش شما در اکسل رو انجام میدم ولی خطای the furmula you typed contains an error میده-
پاسخ:
سلاممتاسفانه دلیل این مشکل را نمیدانم.
-
-
سلام
من فرم کاری در اکسل دارم مبالغی هست حال می خواهم 5 درصد افزایش قیمت روی مبالغ اعمال نمایم و این درصد اتوماتیک اعمال گردد اینکار در ستون جدید کاریست آسان و لی من می خواهم قیمت های قبلی اصلاح شونند و ستون جدید نداشته باشم این چطور انجام می گردد بطور اتومات و بدون کپی و پیست کردن و حذف ستون جدید محاسبات و غیره که در اینترنت دیدم
ممنون
حسن-
پاسخ:
سلامبرای اینکار به کدنویسی ماکرو نیاز است.
-
-
احمد فیروززارع
اگر بخواهیم تمام حالات ممکن از ترکیب چند سلول با فرمت text را داشته باشیم باید از چه فرمولی استفاده کنیم؟
مثلا 6 سلول که هر کدام از یک حرف تشکیل شده، تمام کلماتی که از ترکیب آن حروف میتوان ساخت را به ما نمایش دهد؟-
پاسخ:
سلام
منظورتان کلمات معنا دار است؟
-
-
میلاد
با سلام
من چن تا شیت کنار هم دارم مثلا تو هر شیت یک سری نوع سنگ و مقدار متر مربع اون نوشته شده. همه اقلام توی شیت ها یکی نیست و متفاوته ولی تکراری هم دارم.
به طور مثال :
شیت 1 :
تراورتن عرض 40 : 250مترمربع
مرمریت خوی : 130مترمربع
شیت2:
گرانیت : 300 مترمربع
پله نهبندان : 175 مترمربع
تراورتن عرض 40 :500 مترمربع
شیت3:
فتیله : 56 مترمربع
درپوش : 400 مترمربع
مرمریت خوی : 90 مترمربع
حالا میخوام تو یه شیت دیگه مجموع اینها رو به تفکیک بهم بده-
پاسخ:
سلام
اگر همه اطلاعات را در یک شیت قرار دهید میتوانید از روش های گفته شده در این مطلب استفاده کنید.
موفق باشید.
-
-
حامد میرکو
سلام ، ببخشید من ی فایل نت پد دارم با کلی عدد جدا گانه ، میخواستم این اعداد رو تو اکسل وارد کنم ولی هر کدوم از این اعداد جداگانه داخل ی سلول قرلر بگیره، باید چه کنم؟-
پاسخ:
-
-
سینا
سلام ، وقتتون بخیر ، میخواستم سوال کنم که در ورژن 2016 این دستور چرا خطا میده ؟ و اینکه دستور textjoin رو چطوری میشه اجراییش کرد ( فقط در Office 365 قرار دادنش... برای 2016 چاره ای نداریم ؟ )
هدفم اینه که 700 تا سل متنی در یک ستون مشترک رو با یک علامت ( - ) در سلول شماره 701 در کنار هم داشته باشم-
پاسخ:
سلام
متاسفانه با آفیس 2016 کار نکرده ام.
-
-
مجید
آقا سلام دمت گرم -
سعید
آقا دمت گرم
مشکل حل شد -
زی
سلام من میخام همه سلولهای خالی رو صفر بزارم چیکار باید بکنم؟-
پاسخ:
-
-
محمد رنجبر
خیلی ممنون -
باران
خیلی ممنون عالی بود -
افشین
سلام
من دو شیت دارم که در هرکدام یک سری ستون مشترک مثلا نام و نام خانوادگی وجود دارد . چطوری میتونم ستونهای غیر مشترک را جلوی هر اسمی قرار بدهم؟
سپاس -
-
علی اصغر
ممنون از آموزشتون موفق باشید -
محمد
سلام / خیلی ممنون از اموزش خوبتون
آیا برعکس این مطلب هم میتونه اتفاق بیفته ؟
یعنی یه سلول حاوی متنی دو کلمه ای که کلماتش با "," از هم جدا شدن رو در دو سلول که هر سلول حاوی یکی از اون کلمات باشه تقسیم کرد؟-
پاسخ:
سلام
بله میتوانید با استفاده از ابزار Text to column آن‌ها را از هم جدا کنید. در این قسمت بجای تیک زدن گزینه Space از گزینه comma استفاده کنید.
موفق باشید.
-
