Як в Excel розділити осередок

Microsoft Office Excel - програма для редагування даних. Інформація у файлі представлена у вигляді таблиці, яка складається з рядків і стовпців. Найменша одиниця виміру в документі Excel - осередок. Ці елементи мають умовний номер, який визначається їх ставленням до колонку і рядку, наприклад, A1 або D13. Ви можете змінювати ширину і висоту осередків, надаючи їм бажаний вид, щоб форма, розмір і співвідношення сторін відповідали вимогам. Крім того, можна об'єднувати сусідні елементи з будь-якої з сторін або скасовувати поділ, щоб підлаштовувати структури таблиці. На жаль, так як осередок є мінімальною одиницею файлу в Excel, розділити її не можна.

Електронні таблиці Excel дуже популярні і часто експлуатуються для роботи з даними. Іноді у користувачів виникає необхідність розділити осередок на дві або декілька частин, але такої можливості в програмі немає. Проте є способи обійти це обмеження і надати таблиці бажаний вид.

Потреба розділити осередок може з'являтися в тому випадку, якщо в одному з стовпців має міститися два і більше елементів даних. Наприклад, два або кілька назв певного предмета, тоді як інші в стовпці "найменування" мають лише одне.Крім того, деяким користувачам потрібно розбити дані, які містяться в одній комірці, на кілька. Найпоширеніший приклад - поділ ПІБ людини окремо на прізвище, ім'я та по батькові, щоб кожне значення займало окрему клітку. В обох випадках ви можете виконати поділ за допомогою таких функцій, як об'єднання осередки, "Текст за стовпцями", "Миттєве заповнення" і безлічі формул, які настроюються індивідуально для кожного випадку.

зміст

1 Як розділити осередок в таблиці Excel за допомогою планування структури
2 Як розбити осередки, об'єднані при плануванні структури
3 Як візуально розбити осередок в таблиці Excel, як розділити елемент по діагоналі
4 Як розбити дані осередки по стовпчиках таблиці Excel за допомогою роздільника
5 Як розподілити дані осередки по стовпчиках таблиці Excel за допомогою миттєвого заповнення
6 Як розподілити дані осередки по стовпчиках таблиці Excel за допомогою формул
- 6.1 Як розділити ім'я і прізвище в 2 колонки
- 6.2 Як розділити ім'я, прізвище та по батькові в 3 стовпці
- 6.3 Як розподілити дані, якщо вони розділені коми
- 6.4 інші формули
7 підсумок

Як розділити осередок в таблиці Excel за допомогою планування структури

Excel має певну структуру таблиці - це потрібно для того, щоб було простіше орієнтуватися в даних, а також не було помилок у формулах і розрахунках, проблем з комбінацією і зчитуванням. Кожна осередок має свій індивідуальний номер, який обумовлений її положенням на осях цифр і латинських букв. Наприклад, буквено-числовий адресу першого елемента в таблиці - A1. Одному рядку відповідає одна клітинка таблиці, і навпаки. Це означає, що вона є мінімальним елементом і не може бути розділена на два або більше самостійних.

У деяких випадках користувачеві Excel потрібно зробити так, щоб в одному стовпці було два або більше значення для перетину з однією з рядків. Наприклад, в тому випадку, коли та чи інша річ має кілька назв або номерів, а дані інших вміщаються в одну клітинку. За аналогією з рядками, якщо один стовпець включає в себе первинні категорії (наприклад, "програмісти", "дизайнери" і так далі), а інший - вторинні ( "програмісти" - Іван, Петро). Незважаючи на те що в редакторі можна зробити це безпосередньо, обмеження можна обійти. Для цього потрібно:

Заздалегідь запланувати, яку максимальну кількість значень буде включати в себе рядок або стовпець;
На етапі підготовки листа Excel до роботи об'єднати ті осередки шпальти чи рядки, які будуть використовуватися як одинарні;
Таким чином, "розділені" осередки будуть являти собою самостійні елементи, а "цілі" - з'єднані, тобто результат вийде візуальним (але він все ще буде відповідати вимогам Excel).

Приклад: в стовпці A і рядках 1-5 у вас розташовано 5 прізвищ, а сусідній стовпець B містить посади цих людей в компанії. Якщо один або кілька людей займають 2 посади, впишіть другу в стовпець C, а для інших просто об'єднайте B1 і C1, B2 і C2 і так далі. Аналогічно в тих випадках, коли одному значенню в першому стовпці відповідає більш 2 в наступних. Кожна клітинка буде мати свою адресу в Excel і залишиться повністю функціонуючої.

Як розбити осередки, об'єднані при плануванні структури

Якщо після дій, подібних описаним в попередньому пункті, ви вирішили, що всій сторінці потрібно повернути попередній стан і розбити осередки:

Відкрийте потрібний лист, виділіть всі комірки (або певну частину) і перейдіть у вкладку "Головна" на верхній панелі Excel;
В області "Вирівнювання" натисніть на стрілку і відкрийте список, що випадає з функціями "Об'єднати і помістити в центрі", потім виберіть "Скасувати об'єднання комірок";
Елементи будуть розбиті на одинарні, але всі дані змістяться в лівий верхній - розподілити їх за стовпцями ви зможете за допомогою функції «Текст за стовпцями", яку ми розглянемо далі.

Як візуально розбити осередок в таблиці Excel, як розділити елемент по діагоналі

Якщо вам досить розбити осередок візуально, не змінюючи її властивостей і адреси в Excel, потрібно:

Встановити курсор на необхідний елемент або виділити кілька (або весь лист).
Відкрити вкладку "Головна", в області "Осередки" клікнути "Формат".
Відкриється меню, що випадає, де вам потрібно вибрати "Формат ячеек".
У новому вікні необхідно перейти у вкладку "Кордон" - тут ви зможете самостійно намалювати потрібні рамки осередку (вертикальні, горизонтальні і діагональні лінії, кілька варіантів ліній і безліч квітів).
Є й інший варіант - потрібно клікнути по виділених клітинок правою клавішею миші, щоб викликати контекстне меню, потім вибрати "Формат ячеек", перейти у вкладку "Кордон" і точно так же зробити лінії.
Одна або кілька вибраних осередків отримає розмітку, яку ви вказали.

Для того щоб зробити осередок, яка буде включати в себе назву рядків і стовпців одночасно, потрібно зробити:

У вікні "Формат ячеек", у вкладці "Кордон" намалювати будь-яку діагональну лінію, яка йде з лівого верхнього в правий нижній кут.
Форматувати.
Ввести текст у "верхню частину" осередки (вона розділена тільки візуально), яка буде відповідати рядку, наприклад, "назва").
Вирівняти його по лівому або правому краю, розташувати точніше за допомогою пробілів.
Під час редагування елемента натиснути Alt + Enter, щоб перейти на новий рядок, потім ввести текст для стовпця, наприклад, "кількість";
Якщо текст розташований або виглядає не так, як потрібно, необхідно поміняти його місце розташування за допомогою пробілу або змінити розмір і співвідношення сторін осередків.

Як розбити дані осередки по стовпчиках таблиці Excel за допомогою роздільника

Якщо у вас є осередки, які заповнені деякими даними, і їх потрібно розподілити по стовпцях за категоріями, використовуйте функцію розділення. Вона відмінно підійде в тому випадку, коли в елементах вказана інформація, наприклад, про запрошених людей - ім'я, прізвище, номер квитка, їх місто або країна, дата прибуття. Якщо все це було перенесено з текстового документа, воно не матиме форматування.Для того щоб було зручніше працювати в Excel, дані потрібно розбити по відповідним стовпцях - "ім'я", "прізвище" і так далі.

Робиться це так:

Створіть нові порожні стовпці, якщо праворуч від того, який містить інформацію, їх недостатньо (їх повинно бути не менше, ніж кількість категорій даних), інакше відомості будуть записані на інші, вже заповнені. Поставте курсор миші після шуканого стовпчика на рядку з латинськими буквами і натисніть по рамці таблиці правою клавішею миші, в контекстному меню виберіть "Вставити". Якщо потрібно додати кілька порожніх стовпців, перед цим виберіть аналогічну кількість праворуч від шуканого (натисніть на клітинку з латинською літерою і перетягніть виділення).
Виділіть той стовпець, який потрібно розбити. Відкрийте "Дані" - "Робота з даними" - "Текст за стовпцями".
У новому вікні (Майстер розподілу тексту по стовпцях) виберіть формат даних. Якщо в шуканому стовпці інформація різних категорій розділена пробілами або комами, виберіть "З роздільниками", якщо вона має фіксовану кількість даних - "Фіксованої ширини" (наприклад, цифровий ідентифікатор - розглянемо цей варіант пізніше), натисніть "Далі".
Потім вкажіть роздільники, які використовуються в масиві тексту в стовпці. Вкажіть їх в "Символом-роздільником є" (якщо їх декілька, перерахуйте все в поле "Інший"). Також вкажіть "Вважати послідовні роздільники одним", якщо в даних зустрічаються кілька типів поспіль (наприклад, два пробілу поспіль або точка, яка говорить про скорочення слова, а не про кінець пропозиції, і після неї йде кома).
Налаштуйте обмежувач рядків, якщо в тексті зустрічаються пропозиції, які виділені лапками, а в них зустрічаються роздільники з іншого пункту, але розбивати їх не можна. До таких відносяться пропозиції на кшталт "Росія, Москва" - адреса в такому випадку повинен залишатися цілісним. Якщо ви не встановите обмежувач, "Росія" і "Москва" виявляться в різних стовпчиках.
Виберіть формат даних. За замовчуванням він "Загальний". Якщо у вашій інформації є дати або суми грошових коштів, вкажіть відповідні для того стовпці, в яких вони будуть розміщені. Тут ви також можете вказати, куди будуть поміщені ті чи інші дані. Клацніть по іконці вибору діапазону праворуч від "Помістити в" і в якості першого стовпця вкажіть крайній лівий з вільних, які повинні бути заповнені.На жаль, дані не можна перемістити в іншу книгу Excel і навіть на інший аркуш, але ви можете розбити їх на поточному, після чого просто скопіювати в потрібне місце.
Натисніть "Готово" - всі налаштування будуть застосовані. Збережіть документ, щоб не загубити їх.

Як розподілити дані осередки по стовпчиках таблиці Excel за допомогою миттєвого заповнення

Починаючи з версії 2013 року, Microsoft Office Excel пропонує можливість утилізувати "Миттєве заповнення". За допомогою цієї функції ви можете змусити редактор автоматично розподілити дані в комірки стовпчика, як тільки він помітить закономірність введення.

Працює опція так: Excel починає аналізувати дані, які ви вписуєте в осередку робочого аркуша, і намагається з'ясувати, звідки вони беруться, чому відповідають і чи є в них закономірність. Так, якщо в стовпці A у вас вказані прізвища та імена людей, а в B ви вводите прізвища, за допомогою "Миттєвого заповнення" утиліта вважатиме цей принцип і запропонує автоматично розподілити всі значення в стовпець B.

За допомогою зазначеного інструменту вам досить ввести в новий стовпець лише частина даних - враховуючи, що функція працює в пасивному режимі, вона дуже зручна. Щоб її активувати і використовувати:

Переконайтеся, що у вас активовано "Миттєве заповнення" - воно знаходиться у вкладці "Файл" - пункт "Параметри" - "Додатково" - "Автоматично виконувати миттєве заповнення" (встановіть галочку, якщо її там немає).
Почніть вводити в один із стовпців дані з іншого, і редактор сам запропонує розподілити інформацію масово. Якщо вам підходить те, що пропонує система Excel, натисніть Enter.
Якщо функція активована, але не спрацьовує в рамках певного шаблону, запустіть інструмент вручну в "Дані" - "Миттєве заповнення" або натисніть Ctrl + "E".

Як розподілити дані осередки по стовпчиках таблиці Excel за допомогою формул

В Excel є формули, які дозволяють спростити розбиття даних і зробити це більш функціонально. Як правило, зазвичай досить команд "ЛЕВСИМВ", "ПСТР", "ПРАВСИМВ", "ШУКАТИ", "ПОШУК" І "ДЛСТР". Давайте розглянемо, коли вони потрібні і як їх використовувати.

Як розділити ім'я і прізвище в 2 колонки

Один з найбільш частих випадків - потреба розділити імена і прізвища з шпальти A в B і C відповідно. Для цього потрібно зробити так, щоб редактор сам знайшов прогалину між значеннями і розбив все автоматично. Використовуйте формулу "= ЛЕВСИМВ (A2; ПОШУК (» «; A2; 1) -1)".Вона шукає прогалини в пошуках, після чого приймає їх за роздільник і відправляє, наприклад, прізвища в лівий з двох стовпців, а імена - в правий. Аналогічно з іншими значеннями, які розбиті пробілами. Зазначена формула не підходить для більш складних осередків, в тому числі імен з прізвищами та по батькові, суфіксами і іншими даними.

Як розділити ім'я, прізвище та по батькові в 3 стовпці

Якщо вам потрібно розбити на стовпці повне ПІБ з трьох значень (при цьому будь-яка з них може бути тільки у вигляді літерного ініціал):

Використовуйте формулу "= ЛЕВСИМВ (A2; ШУКАТИ (» «; A2; 1) -1)", щоб відокремити ім'я;
Використовуйте "= ПСТР (A2; ШУКАТИ (» «; A2; 1) +1; ШУКАТИ (» «; A2; ШУКАТИ (» «; A2; 1) +1) - (ШУКАТИ (» «; A2; 1) + 1)) ", щоб знайти по батькові (в запису на кшталт" Іванов Іван Іванович ")
Використовуйте "= ПРАВСИМВ (A2; ДЛСТР (A2) -Знайти (» «; A2; ШУКАТИ (» «; A2; 1) +1))", щоб витягти прізвище.

Ці ж формули можна експлуатувати для записів на кшталт "Іванов Іван молодший" (на західний манер) або інших, що містять суфікс.

Як розподілити дані, якщо вони розділені коми

Якщо дані в осередках записані в вигляді "Black, Bob Mark" (повне ім'я з прізвищем спереду - в англійській мові в такому випадку потрібно постановка коми), розділити їх в звичні нам "Bob Mark White" можна так:

Використовуйте "= ПСТР (A2; ПОШУК (» «; A2; 1) +1; ШУКАТИ (» «; A2; ШУКАТИ (» «; A2; 1) +1) - (ШУКАТИ (» «; A2; 1) + 1)) ", щоб виділити ім'я;
Використовуйте "= ПРАВСИМВ (A2; ДЛСТР (A2) -Знайти (» «; A2; ШУКАТИ (» «; A2; 1) +1))", щоб витягти батькові;
Використовуйте "= ЛЕВСИМВ (A2; ШУКАТИ (» «; A2; 1) -2)", щоб витягти прізвище ".

інші формули

Excel дозволяє працювати не тільки з іменами і прізвищами людей, а й з іншими типами даних. Інший часто зустрічається приклад - адреси. Якщо в осередку записана інформація виду "Росія, Москва, вулиця Арбат", ви можете розподілити значення по іншим елементам, як роздільник вказавши кому, крапку або інший довільний символ. Щоб розбити таку адресу з 3 частин (країна, місто, вулиця):

Використовуйте "= ЛЕВСИМВ (A2; ПОШУК («, »; A2) -1)", щоб відокремити країну;
Використовуйте "= ПСТР (A2; ПОШУК («, »; A2) +2; ПОШУК («, »; A2; ПОШУК («, »; A2) +2) -Пошук («, »; A2) -2)" , щоб виділити місто;
Використовуйте "= ПРАВСИМВ (A2; ДЛСТР (A2) - (ПОШУК («, »; A2; ПОШУК («, »; A2) +1) +1))", щоб відокремити вулицю.

Таким чином, призначення зазначеної формули - розділяти дані в тому місці, де зустрічається заданий значок (в даному випадку - кома). Просто вкажіть його в лапки.

підсумок

Microsoft Office Excel пропонує широкі можливості для роботи як з сіткою таблиці, так і з її вмістом.Незважаючи на те що немає функції для дроблення осередки на кілька частин, ви можете добитися результату, плануючи структуру і групуючи елементи. Якщо форматування вас не влаштує, його можна скасувати на всьому аркуші. За допомогою кордонів можна розділити елемент по діагоналі, щоб домогтися розташування назви стовпців праворуч зверху, а рядків - зліва знизу. Якщо ви хочете розподілити масив інформації по осередках інших стовпців, використовуйте формули, функції "Миттєве заповнення" або "Текст за стовпцями".