→ Як осередок в екселя розбити на дві. Як розділити текст в осередку Excel? Приклади роботи з ПІБ в Excel

Як осередок в екселя розбити на дві. Як розділити текст в осередку Excel? Приклади роботи з ПІБ в Excel

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

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

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

Спосіб 1: через об'єднання осередків

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

  1. Чітко уявіть, як таблиця повинна буде виглядати в підсумку.
  2. Виділіть два осередки над тими, які передбачаються бути роздільними.
  3. На панелі інструментів натисніть на кнопку «Об'єднати і помістити в центр». Розташована вона в категорії «Вирівнювання».

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

  1. Виділіть діапазон комірок, які входять в вашу таблицю.
  2. На панелі інструментів натисніть на кнопку «Кордони».
  3. З меню, що випадає виберіть пункт «Все кордону».

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

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

Спосіб 2: поділ об'єднаних осередків

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

  1. Запустіть програму.
  2. Затиснувши ліву кнопку миші на найменуваннях стовпців (латинські літери, що знаходяться над першим рядком таблиці), виділіть два стовпці.
  3. Перебуваючи на головній вкладці програми, натисніть по стрілочки поряд з кнопкою «Вирівнювання».
  4. З з'явився меню, що випадає виберіть рядок «Об'єднати за рядками».

Тепер замість двох стовпців у вас один. Саме в ньому ви можете розділити осередок на дві. Щоб це виконати, вам необхідно:

  1. Клацніть по тій комірці, яку бажаєте розділити на дві. Важливо, щоб вона перебувала в стовпці, який вийшов після об'єднання.
  2. Перейдіть на вкладку «Головна».
  3. У групі інструментів «Вирівнювання» натисніть на кнопку «Об'єднати і помістити в центр».
  4. У випадаючому меню виберіть опцію «Скасувати об'єднання осередків».

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

Спосіб 3: поділ по діагоналі за допомогою форматування

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

  1. Відкрийте книгу і перейдіть на лист, в якому хочете поділити осередок.
  2. Виділіть її.
  3. Натисніть потім праву кнопку миші.
  4. У контекстному меню виберіть опцію «Формат ячеек».
  5. З'явиться вікно, в якому необхідно перейти на вкладку «Кордон».
  6. Тут розташовується інтерфейс осередки з текстом «Напис». У нижньому правому або лівому кутку знаходяться кнопки, після натискання яких осередок розбивається лінією по діагоналі справа наліво або зліва направо відповідно.
  7. Після цього ви можете також вибрати тип лінії і її колір.
  8. Задавши всі необхідні параметри, натисніть ОК.

Після цього осередок буде розбита на дві, і ви зможете ввести в неї свої значення.

Спосіб 4: поділ по діагоналі за допомогою вставки фігури

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

  1. Виділіть потрібну комірку.
  2. На вкладку «Вставка» натисніть на кнопку «Постаті».
  3. У списку оберіть з категорії «Лінії» однойменний об'єкт.
  4. Затиснувши ліву кнопку миші, проведіть лінію від одного кута до іншого.

Після цього осередок буде розбита на дві по діагоналі.

замість висновку

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

Користувачі, які звикли до роботи з формулами в табличному процесорі «Ексель» від «Майкрософт», часом намагаються знайти готову функцію для поділу одного числа на інше. Але такої функції немає, і використовувати знак двокрапки так само не вийде - він в «Ексель» призначений для позначення меж діапазонів. Формула розподіл в Excel - це просто знак зворотного слеша (/).

Як розділити одне число на інше

На робочому аркуші книги «Ексель» введіть в різні осередки будь-які два числа. Вони допоможуть розібратися з тим, як зробити формулу розподілу в Excel. Потім у вільній клітинці наберіть знак початку формули (так само \u003d) і вкажіть перший осередок з діленим. Далі наберіть знак ділення у формулі Excel - зворотний слеш - і вкажіть клітинку з подільником.

Зручно, що при спробі поділу на осередок, вміст якої є 0, «Ексель» виведе відповідну помилку: # СПРАВ / 0 !.

Важливо! Ексель дозволяє ділити між собою будь-які числа, в тому числі і дати. А ось при спробі виконати поділ над осередками, що містять текст, ви отримаєте помилку «# значить!», Про є невірне значення.

розподіл дат

Дуже цікаво ділити дати. Спробуйте розділити «01.01.2017» на «01.01.2016». Думаєте, у вас нічого не вийде? Це не так. Справа в тому, що «Ексель» автоматично перетворює все дати в число, відраховуючи кількість минулих днів з 01 січня 1900 року. Дата «01.01.1900» - це число 1, «02.01.1900» - 2. Ви легко можете це перевірити, перетворюючи формати осередків з зазначеними датами з формату «Дата» на формат «Числовий». Таким чином 1 січня 2017 року - це число 42736, саме стільки днів минуло з того ж числа і місяця 1900 року. Тому при розподілі дат одну на іншу ми можемо отримати цілком конкретний результат.

Логічно, що ви можете ділити не тільки дату на дату, але і будь-яке число на дату і навпаки. Оскільки формула «поділ» в Excel все одно представить вашу дату в число. А їм можна оперувати яким завгодно чином.

Таблиця ділення в «Ексель»

Побудуємо таблицю в «Ексель», в осередку А2: А6 помістимо числа, які будемо ділити (3, 7, 9, 12, 15), а в осередку В1: Е1 - числа, НА які будемо виконувати розподіл (1, 2, 3 , 4).

Щоб скласти таблицю розподілу, важливо згадати про в «Ексель». Формула розподіл в Excel при складанні такої таблиці припускає використання змішаних посилань. Так, щоб адреси осередків при копіюванні не зміщувалися, треба закріпити в подільному адресу номер стовпчика, а в дільнику - номер рядка. Таким чином, введіть у комірку В2 формулу «\u003d $ A2 / B $ 1» і простягніть її до кінця рядка? а потім і на всю таблицю. Таблиця заповниться даними. Встаньте тепер на будь-який елемент, припустимо, на D5. Формула її буде наступна: «\u003d $ A5 / D $ 1». Все зробили правильно.

Розподіл із залишком

При виконанні ділення в більшості випадків ми отримуємо дробове число. Для спрощення сприйняття таких чисел є в «Ексель» два важливих моменти.

Осередок, в яку записується результат, за замовчуванням має формат «Загальний». Якщо перейти на вкладку «Головна» в розділ «Число», то можна зі списку вибрати пункт зміни формату «Інші числові формати» і задати формат «Числовий». За даним форматом можна привласнити для осередку кількість відображуваних десяткових знаків. Так, при розподілі 7/3 ми зазвичай бачимо число 2,333333333, а за допомогою обмеження точності на три десяткових знака воно стане виглядати як 2,333. Що, погодьтеся, досить зручно.

У «Ексель» є дві відмінні функції для отримання приватного і залишку від ділення. Вони носять назву «Приватне» і «Залишок» і обидві приймають два обов'язкових аргументу - чисельник і знаменник. Розберемося з ними докладніше.

Введіть в «Ексель» два числа - 7 і 3 (в осередку D9 і D10 в нашому випадку), і правіше від них - формули:

ПРИВАТНЕ (D9; D10)

ОСТАТ (D9; D10)

Як бачимо, частка від ділення дорівнює двом, залишок - 1. В будь-яку вільну комірку введіть формулу:

E9 & "" & E10 & "/" & D10

Тут знак амперсанда допомагає приєднати до вмісту одного осередку вміст іншої у вигляді тексту.

Результат виконання даної формули буде наступний: 2 1/3 - дві цілих і одна третина.

Як бачимо, формула розподіл в Excel не являє собою нічого складного. Головне пам'ятати, що при обчисленні формул «Ексель» використовує стандартні правила математики.

Багато хто знає, що для того, щоб об'єднати текст в двох осередках досить скористатися функцією, проте, як бути, якщо необхідно не об'єднати, а навпаки розділити текст в комірці?

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

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

На Ваш огляд уявімо два способи поділу тексту. Один дуже швидкий - для тих кому треба просто розділити текст замінивши наявний, а другий з використанням формул.

Перший спосіб - супер швидкий

Насправді в MS Excel існує вбудована можливість швидкого поділу тексту в осередку, якщо там присутній або присутні роздільник / роздільники (наприклад, простий пробіл або кома). Причому таких роздільників може бути кілька, тобто текст буде розділяться якщо в рядку присутній або пробіл, або крапка з комою або кома і т.д.

Для цього необхідно виділити комірки з текстом, який необхідно розділити і скористатися командою «Текст за стовпцями».

В англомовній версії MS Excel дана команда звучить як «Text to Columns» вкладки «DATA».

Після нескладних підказок майстра (насправді, в нашому прикладі після вибору роздільник - пробілу можна сміливо тиснути «Готово»)

А ось і, власне, результат.

Другий спосіб - з використанням формул

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

Крім того, оскільки розділового знака в кінці рядка немає, то кількість символів в останньому слові (нашому випадку - батькові) обчислити не вдасться, але це не проблема, досить вказати свідомо більша кількість символів в якості аргументу «чісло_знаков» функції ПСТР, наприклад, 100 .

Розглянемо вищесказане на прикладі. Перш за все, для кращого розуміння, рознесемо формули і, таким чином, розділимо весь процес на два етапи.

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

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

Тут, в якості аргументів, використовуються проміжні значення, отримані за допомогою функції ПОШУК. Для останньої колонки кількість символів невідомо, тому було взято свідомо більша кількість символів (в нашому випадку 100).

Тепер спробуємо з'єднати проміжні розрахунки в одну формулу.

Якщо текст в осередку необхідно розділити лише на дві частини, то необхідно провести пошук лише одного пробілу (або іншого роздільник, який знаходиться між словами), а для поділу на 4 і більше частин формулу доведеться ускладнити пошуком 3го, 4го і т.д. роздільників.

Програма для редагування даних. Інформація у файлі представлена \u200b\u200bу вигляді таблиці, яка складається з рядків і стовпців. Найменша одиниця виміру в документі 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 відповідно. Для цього потрібно зробити так, щоб редактор сам знайшов прогалину між значеннями і розбив все автоматично. Використовуйте формулу «\u003d ЛЕВСИМВ (A2; ПОШУК (" "; A2; 1) -1)». Вона шукає прогалини в пошуках, після чого приймає їх за роздільник і відправляє, наприклад, прізвища в лівий з двох стовпців, а імена - в правий. Аналогічно з іншими значеннями, які розбиті пробілами. Зазначена формула не підходить для більш складних осередків, в тому числі імен з прізвищами та по батькові, суфіксами і іншими даними.

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

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

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

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

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

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

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

інші формули

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

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

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

підсумок

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

У цій статті Ви знайдете кілька способів, як розбити осередки або цілі стовпці в 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 (Файл)\u003e Options (Параметри)\u003e Advanced (Додатково)\u003e Automatically Flash Fill (Автоматично виконувати миттєве заповнення).

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

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

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

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

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

приклад 1

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

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

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

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

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

    RIGHT (A2, LEN (A2) -SEARCH ( "", A2,1))
    \u003d ПРАВСИМВ (A2; ДЛСТР (A2) -Пошук ( ""; A2; 1))

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

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

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

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

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

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

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

LEFT (text,)
\u003d ЛЕВСИМВ (текст; [колічество_знаков])

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

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

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

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

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

LEN (A2)
\u003d ДЛСТР (A2)

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

приклад 2

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

  • Витягуємо ім'я:

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

  • Витягуємо батькові:


  • Витягуємо прізвище:


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

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

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

приклад 3

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

  • Витягуємо ім'я:

    LEFT (A2, FIND ( "", A2,1) -1)
    \u003d ЛЕВСИМВ (A2; ШУКАТИ ( ""; A2; 1) -1)

  • Витягуємо прізвище:

    MID (A2, FIND ( "", A2,1) + 1, FIND ( "", A2, FIND ( "", A2,1) +1) - (FIND ( "", A2,1) +1))
    \u003d ПСТР (A2; ШУКАТИ ( ""; A2; 1) +1; ШУКАТИ ( ""; A2; ШУКАТИ ( ""; A2; 1) +1) - (ШУКАТИ ( ""; A2; 1) +1) )

  • Витягуємо суфікс:

    RIGHT (A2, LEN (A2) -FIND ( "", A2, FIND ( "", A2,1) +1))
    \u003d ПРАВСИМВ (A2; ДЛСТР (A2) -Знайти ( ""; A2; ШУКАТИ ( ""; A2; 1) +1))

приклад 4

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

  • Витягуємо ім'я:

    MID (A2, SEARCH ( "", A2,1) + 1, FIND ( "", A2, FIND ( "", A2,1) +1) - (FIND ( "", A2,1) +1))
    \u003d ПСТР (A2; ПОШУК ( ""; A2; 1) +1; ШУКАТИ ( ""; A2; ШУКАТИ ( ""; A2; 1) +1) - (ШУКАТИ ( ""; A2; 1) +1) )

  • Витягуємо батькові:

    RIGHT (A2, LEN (A2) - FIND ( "", A2, FIND ( "", A2,1) +1))
    \u003d ПРАВСИМВ (A2; ДЛСТР (A2) -Знайти ( ""; A2; ШУКАТИ ( ""; A2; 1) +1))

  • Витягуємо прізвище:

    LEFT (A2, FIND ( "", A2,1) -2)
    \u003d ЛЕВСИМВ (A2; ШУКАТИ ( ""; A2; 1) -2)

приклад 5

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

  • Витягуємо назва штату:

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

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

    MID (A2, SEARCH ( ",", A2) + 2, SEARCH ( ",", A2, SEARCH ( ",", A2) +2) -SEARCH ( ",", A2) -2)
    \u003d ПСТР (A2; ПОШУК ( ","; A2) +2; ПОШУК ( ","; A2; ПОШУК ( ","; A2) +2) -Пошук ( ","; A2) -2)

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

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

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

 

 
Це цікаво: