Застосування поділу у Microsoft Excel. Розділити текст по стовпцях в Excel Як відокремити дані в осередку excel

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

Як роз'єднати осередки в Excel

Мінімальною одиницею електронних таблицьє осередок. Здійснити розбиття клітин можливо, якщо користувач зробив злиття цих клітин. Досвідчений користувач знає, як розділити комірку в Екселі при складанні заголовка об'єкта зі складною структурою.

Існує 3 варіанти для розбиття осередків у табличному процесорі. Важливо пам'ятати, що розділити можна лише ті осередки, які пройшли операцію злиття.


Злиття осередків

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

Для злиття осередків користувач:

  • відкриває книгу із робочим листом;
  • виділяє сусідні об'єкти;
  • переходить на вкладку "Головна";
  • в інструментарії програми знаходить "Вирівнювання";
  • при натисканні на кнопку вибирає "Об'єднати і помістити в центрі".

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

Кнопка "Об'єднання" неактивна, якщо об'єкти знаходяться на стадії редагування.


Розбиття об'єднаних осередків

Якщо структура об'єкта табличного процесора має на увазі розбиття осередку всередині таблиці потрібно знати, як роз'єднати осередки в Excel. Для цього користувач працює виключно з кнопкою "Об'єднати та помістити в центрі":

  • виділяє 2 сусідні колонки;
  • вибирає "Об'єднати рядками" в меню кнопки;
  • виділяє комірку для розбиття;
  • активує "Скасувати об'єднання".

Примітка: Ексель у такому варіанті розглядає результат як єдине ціле.


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

Потрібно знати, як розбити комірки в Excel, як провести процес розбиття по діагоналі за допомогою форматування. Користувач:

  1. Викликає меню "Формат осередків" (одним із способів): за допомогою правої кнопки миші; знаходить кнопку "Кордон" і клацає на покажчик, вибираючи "Інші межі"; клацає на стрілку в області "Шрифт", переходить на вкладку "Кордон"; користується "гарячим" поєднанням CTRL+1.
  2. Коли з'явиться діалогове вікно, на вкладці "Кордон" клацає на діагональні лінії біля блоку "Напис".
  3. Підтверджує дію через натискання "ОК".

Примітка: при такому способі розбиття осередку табличний процесорсприймає об'єкт як одну клітину.


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

Подібний варіант підходить, якщо секція велика або створена за допомогою об'єднання кількох одиниць. Для здійснення способу користувач:

  1. Переходить до вкладки "Вставка".
  2. В області "Ілюстрації" натискає кнопку "Фігури".
  3. Вибирає інструмент "Лінія".
  4. Проводить лінію за наміченим зразком.

Знаючи, як у Ексель розділити комірку на дві частини, користувач побудує таблицю за наміченим шаблоном.


Як розділити слова по осередках

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

  1. Виділяє секцію з більшим обсягом інформації.
  2. Відкриває діалогове вікно з перенесення рядка одним із способів: знаходить на панелі інструментів вкладки "Головна" у блоці "Вирівнювання" стрілку в нижньому правому кутку і натискає на неї; натискає CTRL+1 та переходить на вкладку "Вирівнювання"; знаходить на панелі інструментів вкладки "Головна" у блоці "Вирівнювання" кнопку "Перенесення тексту"; натискає на стрілку біля кнопки "Вирівнювання";
  3. У блоці "Відображення" ставить галочку "Переносити за словами".
  4. Підтверджує дію за допомогою "ОК".

Інша ситуація наздоганяє обивателя, коли не знає, як в Екселі розділити слова в осередку стовпцями. Якщо в клітці вказано кілька параметрів, то привести текст до читального вигляду в таблиці можна за допомогою:

  • виділення діапазону осередків, які потрібно поділити;
  • Примітка: розбиття секції призводить до стирання інформації в сусідніх клітинах, тому для проведення операції необхідно очистити простір;
  • переходу на вкладку "Дані" у блок "Робота з даними";
  • натискання на кнопку "Текст по стовпцям";
  • при появі діалогового вікна "Майстер розподілу тексту по стовпцях" ідентифікації та проставлення перемикача;
  • у блоці "Зразок аналізу даних" перегляду передбачуваного результату;
  • перевизначення формату для нових колонок у вікні "Формат даних стовпця".

Користувачам корисно знати, як розділити слова в Екселі по осередках. Якщо діапазон розбиття тексту по секціях не буде порожнім, інформація перезаписується. Щоб зробити вільний простір, користувач вставляє нові стовпці (рядки) або вирізає та вставляє інформацію на іншому аркуші.


При злитті та розбиття секцій користувач повинен пам'ятати, що застосувати сортування або циклічність неможливо. Якщо в клітину не будуть вносити інформацію, а її поділ візуальний, то проводити операції з об'єднання та поділу небажано.

У цій статті Ви знайдете кілька способів, як розбити комірки або цілі стовпці в Excel 2010 і 2013. Наведені приклади та скріншоти ілюструють роботу з інструментами «Текст по стовпцям» та «Миттєве заповнення», крім цього Ви побачите добірку формул для розділення імен, текст та числових значень. Цей урок допоможе Вам обрати найкращий методрозбиття даних у Excel.

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

Розбиваємо осередки в Excel за допомогою інструмента «Текст по стовпцям»

Інструмент « Текст по стовпцях» дійсно дуже зручний, коли потрібно розділити дані з одного стовпця за декількома в Excel 2013, 2010, 2007 або 2003.

"Текст по стовпцях" дозволяє розбивати значення осередків, відокремлені роздільниками, або виділяти дані фіксованої ширини (коли всі значення містять певну кількість символів). Давайте розглянемо ці варіанти докладніше:

Розбиваємо текстові дані з роздільниками по стовпцях в Excel

Припустимо, є список учасників, запрошених на конференцію чи якийсь інший захід. На малюнку нижче видно, що у стовпці Participant(Учасник) перераховано імена учасників, державу та очікувану дату прибуття:

Необхідно розбити цей текст на окремі стовпці, щоб таблиця мала такі дані (зліва направо): First Name(Ім'я), Last Name(Прізвище), Country(Країна), Arrival Date(очікувана дата прибуття) та Status(Статус).


Примітка:Якщо у Вас немає стовпців, які прямують безпосередньо за тим, що Ви хочете розбити, то необхідність у цьому кроці відпадає і його можна пропустити. Головне не пропустіть, що порожніх стовпців має бути не менше, ніж кількість стовпців, на яку ви хочете поділити дані.

Порада:Якщо Ви не хочете імпортувати якийсь стовпець (стовпці), який показано в області Data preview(Зразок аналізу даних), то виділіть його і виберіть варіант Do not import column(Пропустити стовпець) у розділі Column data format(Формат даних стовпця).


Розбиваємо текст фіксованої ширини кількома стовпцями

Якщо дані складаються з текстових чи числових значень із фіксованою кількістю символів, Ви можете розбити їх на кілька стовпців у такий спосіб.

Наприклад, є список товарів з ID та найменуванням, причому ідентифікатор товару – це 9 символів, які стоять перед найменуванням цього товару:

Ось що Вам потрібно зробити, щоб розбити такий стовпець на два:


Розбиваємо об'єднані осередки в Excel

Якщо Ви об'єднали кілька осередків на аркуші Excel і тепер хочете знову розбити їх на окремі стовпці, відкрийте вкладку Home(Головна) та у групі команд Alignment(Вирівнювання) натисніть маленьку чорну стрілку поруч із кнопкою Merge & Center(Об'єднати та помістити в центрі). Далі зі списку виберіть Unmerge Cells(Скасувати об'єднання осередків).

Таким чином, об'єднання осередків буде скасовано, але задоволення від результату буде зіпсовано тим, що всі дані залишаться в лівому стовпці. Думаю, Ви здогадалися, що потрібно знову використати функцію Text to Columns(Текст на стовпчиках), щоб розбити дані з одного стовпця на два або більше стовпців.

Розділяємо дані на кілька стовпців у Excel 2013 за допомогою миттєвого заповнення

Якщо Ви вже оновилися до Excel 2013, то можете скористатися перевагами нового інструменту. Миттєве заповнення» та змусити Excel автоматично заповнювати (у нашому випадку – розбивати) дані при виявленні певної закономірності.

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

Таким чином, за допомогою цього інструменту Ви можете взяти якусь частину даних, що знаходяться в одному або кількох стовпцях, та ввести їх у новий стовпець. Думаю, Ви краще зрозумієте про що я говорю з наступного прикладу.

Насамперед, переконайтеся, що інструмент «Миттєве заповнення» увімкнено. Ви знайдете цей параметр на вкладці File(Файл) > Options(Параметри) > Advanced(Додатково) > Automatically Flash Fill(Автоматично виконувати миттєве наповнення).

Тепер давайте подивимося, як можна автоматично розбити дані по осередках. Отже, Ви включили інструмент «Миттєве заповнення» і починаєте вводити з клавіатури дані, які потрібно помістити в окремі осередки. У міру введення Excel буде намагатися розпізнати шаблон у значеннях, що вводяться, і як тільки він його розпізнає, дані автоматично будуть вставлені в інші осередки. Щоб зрозуміти, як це працює, подивіться на малюнок нижче:

Як бачите, я ввів лише пару імен у стовпець B, і «Миттєве заповнення» автоматично заповнило решту осередків іменами зі стовпця A. Якщо ви задоволені результатом, просто натисніть Enterі весь стовпець буде заповнений іменами. Дуже розумний інструмент, чи не так?

Якщо миттєве заповнення увімкнено, але не пропонує жодних варіантів, які відповідають певному шаблону, Ви можете запустити цей інструмент вручну на вкладці Data(Дані) > Flash Fill(Миттєве заповнення) або натиснувши клавіші Ctrl+E.

Як в Excel розбивати комірки за допомогою формул

Існують формули, які можуть бути дуже корисними, коли виникає необхідність розбити осередки або стовпці з даними в Excel. Насправді наступних шести функцій буде достатньо в більшості випадків – LEFT(ЛЕВСІМВ), MID(ПСТР), RIGHT(ПРАВИМІВ), FIND(ЗНАЙТИ), SEARCH(ПОШУК) та LEN(ДЛСТР). Далі в цьому розділі я коротко поясню призначення кожної з цих функцій і наведу приклади, які Ви зможете використати у своїх книгах Excel.

Приклад 1

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

Ви легко зможете розбити такі імена на два стовпці за допомогою таких формул:

  • Вилучаємо ім'я (стовпець First name):

    LEFT(A2,SEARCH(" ",A2,1)-1)
    =ЛЕВСИМВ(A2;ПОШУК(" ";A2;1)-1)

  • Вилучаємо прізвище (стовпець Last name):

    RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))
    =ПРАВСИМВ(A2;ДЛСТР(A2)-ПОШУК(" ";A2;1))

Для тих, кому цікаво, що означають ці формули, я спробую докладніше пояснити.

SEARCH(ПОШУК) або FIND(ЗНАЙТИ) – це абсолютно ідентичні функції, які виконують пошук позиції певного текстового рядка в заданому осередку. Синтаксис формули:

SEARCH(find_text,within_text,)
=ПОШУК(шуканий_текст;текст_для_пошуку;[поч_позиція])

Як аргументи Ви повинні вказати: що потрібно знайти, де потрібно шукати, а також позицію символу, з якого потрібно розпочати пошук. У нашому прикладі SEARCH(" ",A2,1)або ПОШУК(” “;A2;1)говорить про те, що ми хочемо знайти символ пробілу в осередку A2і почнемо пошук із першого символу.

Примітка:Якщо пошук починається з першого символу, Ви можете взагалі пропустити аргумент start_num(нач_позиція) у формулі та спростити її до такого виду:

LEFT(A2,SEARCH(" ",A2)-1)

LEFT(ЛЕВСІМВ) та RIGHT(ПРАВСИМВ) – повертає ліву чи праву частину тексту із заданого осередку відповідно. Синтаксис формули:

LEFT(text,)
= ЛЕВСІМВ (текст; [кількість_знаків])

Як аргументи вказуємо: який текст взяти та скільки символів витягти. У наступному прикладі формула витягуватиме ліву частину тексту з комірки A2до позиції першого знайденого пропуску.

LEFT(A2,SEARCH(" ",A2)-1)
=ЛЕВСІМВ(A2;ПОШУК(" ";A2)-1)

LEN(ДЛСТР) – вважає довжину рядка, тобто кількість символів у заданому осередку. Синтаксис формули:

LEN(text)
= ДЛСТР (текст)

Наступна формула вважає кількість символів у комірці A2:

LEN(A2)
=ДЛСТР(A2)

Якщо імена у Вашій таблиці містять по батькові або суфікси, то знадобляться трохи складніші формули з використанням функції MID(ПСТР).

Приклад 2

Ось такі формули потрібно використовувати, коли імена, які потрібно розбити, містять по-батькові або лише один ініціал по-батькові посередині.

  • Вилучаємо ім'я:

    LEFT(A2,FIND(" ",A2,1)-1)

  • Отримуємо по батькові:


  • Вилучаємо прізвище:


Функція MID(ПСТР) – витягує частину текстового рядка (тобто задану кількість символів). Синтаксис:

MID(text,start_num,num_chars)
= ПСТР (текст; початкова_позиція; кількість_знаків)

Як аргументи функції вказуємо: який текст взяти, позицію символу, з якого потрібно почати, і скільки символів витягти.

Приклад 3

Ви можете використовувати аналогічні формули, щоб розбити імена з суфіксами наприкінці:

  • Вилучаємо ім'я:

    LEFT(A2,FIND(" ",A2,1)-1)
    =ЛЕВСИМВ(A2;ЗНАЙТИ(" ";A2;1)-1)

  • Вилучаємо прізвище:

    MID(A2,FIND(" ",A2,1)+1,FIND(" ",A2,FIND(" ",A2,1)+1)-(FIND(" ",A2,1)+1))
    =ПСТР(A2;ЗНАЙТИ(" ";A2;1)+1;ЗНАЙТИ(" ";A2;ЗНАЙТИ(" ";A2;1)+1)-(ЗНАЙТИ(" ";A2;1)+1) )

  • Вилучаємо суфікс:

    RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(" ",A2,1)+1))
    =ПРАВСИМВ(A2;ДЛСТР(A2)-ЗНАЙТИ(" ";A2;ЗНАЙТИ(" ";A2;1)+1))

Приклад 4

А ось формули, що дозволяють розбити імена з прізвищем, яке стоїть попереду і відокремлене від імені коми, і по батькові, що знаходиться в кінці:

  • Вилучаємо ім'я:

    MID(A2,SEARCH(" ",A2,1)+1,FIND(" ",A2,FIND(" ",A2,1)+1)-(FIND(" ",A2,1)+1))
    =ПСТР(A2;ПОШУК(" ";A2;1)+1;ЗНАЙТИ(" ";A2;ЗНАЙТИ(" ";A2;1)+1)-(ЗНАЙТИ(" ";A2;1)+1) )

  • Отримуємо по батькові:

    RIGHT(A2,LEN(A2)- FIND(" ",A2,FIND(" ",A2,1)+1))
    =ПРАВСИМВ(A2;ДЛСТР(A2)-ЗНАЙТИ(" ";A2;ЗНАЙТИ(" ";A2;1)+1))

  • Вилучаємо прізвище:

    LEFT(A2,FIND(" ",A2,1)-2)
    =ЛЕВСИМВ(A2;ЗНАЙТИ(" ";A2;1)-2)

Приклад 5

Як Ви розумієте, ці формули працюють не тільки для розподілу імен в Excel. Ви можете використовувати їх для розбиття будь-яких даних з одного стовпця на кілька. Наприклад, такі формули можна використовувати, щоб розбити текстові дані, розділені комами:

  • Вилучаємо назву штату:

    LEFT(A2,SEARCH(",",A2)-1)
    =ЛЕВСИМВ(A2;ПОШУК(",";A2)-1)

  • Вилучаємо абревіатуру штату:

    MID(A2,SEARCH(",",A2)+2,SEARCH(",",A2,SEARCH(",",A2)+2)-SEARCH(",",A2)-2)
    =ПСТР(A2;ПОШУК(",";A2)+2;ПОШУК(",";A2;ПОШУК(",";A2)+2)-ПОШУК(",";A2)-2)

  • Вилучаємо столицю штату:

    RIGHT(A2,LEN(A2)-(SEARCH(",",A2,SEARCH(",",A2)+1)+1))
    =ПРАВСИМВ(A2;ДЛСТР(A2)-(ПОШУК(",";A2;ПОШУК(",";A2)+1)+1))

А ось приклад реальних даних з Excel 2010. Дані з першого стовпця розбиті на три окремі стовпці:

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

У Microsoft Excelрозподіл можна зробити як з допомогою формул, і використовуючи функції. Ділимим і дільником при цьому виступають числа та адреси осередків.

Спосіб 1: розподіл числа на число

Аркуш Ексель можна використовувати як своєрідний калькулятор, просто поділяючи одне число на інше. Знаком поділу виступає сліш (зворотна риса) - «/» .


Після цього Ексель розрахує формулу і у вказану комірку виведе результат обчислень.

Якщо обчислення провадиться з декількома знаками, то черговість їх виконання провадиться програмою відповідно до законів математики. Тобто, перш за все, виконується розподіл та множення, а вже потім – додавання та віднімання.

Як відомо, розподіл на 0 є некоректною дією. Тому за такої спроби здійснити подібний розрахунок в Екселі в осередку з'явиться результат «#СПРАВ/0!».

Спосіб 2: розподіл вмісту осередків

Також у Excel можна ділити дані, що у осередках.


Можна також комбінувати, як дільник або дільник використовуючи одночасно і адреси осередків і статичні числа.

Спосіб 3: розподіл стовпця на стовпець

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


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

Спосіб 4: розподіл стовпця на константу

Бувають випадки, коли потрібно розділити стовпець на те саме постійне число – константу, і вивести суму поділу в окрему колонку.


Як бачимо, цього разу поділ теж виконаний коректно. І тут при копіюванні даних маркером заповнення посилання знову залишалися відносними. Адреса ділимого для кожного рядка автоматично змінювалася. І це дільник є у разі постійним числом, отже, властивість відносності нею не поширюється. Таким чином, ми розділили вміст осередків стовпця на константу.

Спосіб 5: розподіл стовпця на комірку

Але що робити, якщо потрібно розділити стовпець на вміст одного осередку. Адже за принципом відносності посилань координати дільника і дільника зміщуватимуться. Нам же потрібно зробити адресу осередку з дільником фіксованою.


Після цього результат по всьому стовпцю готовий. Як бачимо, у цьому випадку відбувся розподіл колонки на комірку з фіксованою адресою.

Спосіб 6: функція ПРИВАТНЕ

Поділ в Екселі можна також виконати за допомогою спеціальної функції, яка називається ПРИВАТНЕ. Особливість цієї функції у тому, що вона ділить, але остаточно. Тобто, при використанні цього способу поділу результатом завжди буде ціле число. При цьому округлення проводиться не за загальноприйнятими математичними правилами до найближчого цілого, а до меншого за модулем. Тобто число 5,8 функція округлить не до 6, а до 5.

Подивимося застосування цієї функції з прикладу.


Після цих дій функція ПРИВАТНЕздійснює обробку даних і видає відповідь у комірку, яка була вказана в першому кроці даного способу поділу.

Цю функцію можна також ввести вручну без використання Майстра. Її синтаксис виглядає так:

ПРИВАТНЕ(числитель; знаменник)

Як бачимо, основним способом поділу у програмі Microsoft Officeє використання формул. Символом поділу в них є сліш – «/» . У той же час, для певних цілей можна використовувати в процесі поділу функцію ПРИВАТНЕ. Але, треба врахувати, що з розрахунку у такий спосіб різниця виходить без залишку, цілим числом. При цьому округлення проводиться не за загальноприйнятими нормами, а до меншого за модулем цілого числа.

Аркуш в Microsoft Excel - сам по собі велика таблиця, що складається з осередків, яким присвоєно певну адресу (цифра, що відповідає рядку, і буква, що відповідає стовпцю). За цією адресою можна звертатися до даних, тобто використовувати їх у формулах. Відповідно, поділити комірку безпосередньо, як скажемо у Ворді, не вийде. Оскільки адреса А5 не стане А/1-5 та А/2-5. То що можна зробити?

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

А ось як вчинити, якщо таблиця готова, розкажу в даній статті. Розберемося, як розділити комірку в Екселі на дві і більше по горизонталі та вертикалі, а також по діагоналі.

Наприклад, у нас буде така таблиця. У ній потрібно після назви вказати ще й виробника.

Оскільки нові стовпці додаються ліворуч від поточного виділення, вибираю будь-яке значення у стовпці С та на вкладці «Головна» вставляю стовпці.

Отже, після назви з'явився ще один вертикальний стовпець, який назву Виробник.

Оскільки фрукти можуть бути привезені з різних країн, потрібно навпроти кожного у доданому стовпці з одного осередку зробити кілька.

Почнемо з яблука. Виділяємо блок відразу під цією назвою, клацаємо правою кнопкою миші та вибираємо в контекстному меню «Вставити» .

Позначаємо маркером у віконці «рядок» і натискаємо «ОК» .

У потрібному місці додасться додатковий рядок. У такий же спосіб додаються ще рядки, якщо Виробників більше.

Тепер об'єднаємо осередки у тих стовпцях, де дані не змінюються. У прикладі це Номер, Назва та Кількість. Спочатку потрібно їх виділити, потім клацнути по кнопці «Об'єднати» і вибрати «Комірки».

Для різних способів об'єднання осередків в Ексель можна прочитати у відповідній статті.

Заповнюємо даними поле Виробник та Ціна.

Як бачите, додавши рядки і об'єднавши певні осередки, ми досягли того, що в потрібному стовпці вдалося розділити їх по горизонталі на кілька рядків. Таким чином, у Яблука кілька різних виробників і різна ціна.

З Грушею можна зробити так само. Додаємо рядки, об'єднуємо там, де Номер і Назва, і лише дві строчки в стовпці Кількість (оскільки значення буде однакове). Потім вписуємо дані і отримуємо ніби поділений осередок у полі Виробник і Ціна.

Зверніть увагу, що в об'єднаних блоках потрібно вказувати у формулі адресу верхнього лівого. Наприклад, із D2:D3 це буде D2, а з В4:В6 – В4.

Тепер давайте розглянемо, як розбити одну комірку на 2 або більше по вертикалі. Це необхідно, якщо хочете додати до назви стовпця кілька підназв, і потім заповнювати їх даними.

Наприклад поділимо стовпець Кількість. З другого рядка виділяємо будь-яку не ділену комірку, це може бути С2 або Е2. Викликаємо контекстне меню та вставляємо додатковий рядок.

За допомогою відповідної кнопки об'єднуємо D1 та E1, помістивши значення в центрі.

Ось таким хитрим способом ми розбили осередок у стовпці під словом Кількість і дали їм назви Завезено та Продано. Там, де Продано, у мене залишилися раніше об'єднані осередки зі значенням, якщо воно не підходить, потрібно його просто скасувати, вибравши відповідний пункт.

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

Щоб розбити по діагоналі, виділяємо комірку, викликаємо контекстне меню кліком правою кнопкоюі тиснемо «Формат».

Вибраний блок буде поділено.

Можна зробити по-іншому. Угорі відкрийте «Вставка» , натисніть «Ілюстрації» – «Фігури» та виберіть пряму лінію.

У потрібній комірці намалюйте її від одного кута до іншого по діагоналі. Якщо, як і в прикладі, вона не того кольору, тоді на вкладці «Засоби малювання»виберіть потрібний або із готових стилів, або натиснувши на «Контур фігури» .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Як розділити ім'я та прізвище у 2 стовпці

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

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

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

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

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

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

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

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

Інші формули

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

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

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

Підсумок

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

Поділитися з друзями:
Подібні публікації