Я викладаю клас Power Power в Університеті Акрона. Хоча його рекламують як просунутий клас, завжди є деякі основні поняття, яких, здається, студенти не знають. Я вражений тим, як найпростіші техніки викликають найбільше хвилювання. Це одна з тих порад.
Сьогодні Саджад з Дубаю написав із запитанням. У нього є база даних із іменем у стовпці A та Прізвищем у стовпці B. Як можна об’єднати ім’я та прізвище в один стовпець?
Це одне з тих питань, якого ви ніколи не знайдете в довідці Excel, оскільки ніхто не думає шукати слово "конкатенація". Чорт візьми, я не думаю, що будь-яка нормальна людина коли-небудь вживає слово конкатенати. Якщо ви не знаєте, як шукати Concatenate, то ви ніколи не дізнаєтесь, що оператор конкатенації є амперсандом.
Почніть з основної формули =A2&B2
Це дасть вам результат, показаний в С2 нижче:
Це хороший початок. Однак нам справді слід об’єднати ім’я, пробіл та прізвище. Спробуйте цю формулу:
=A2&" "&B2
Тоді, запитання: ти хочеш кричати МІККІ МАНТІ, чи ти скоріше скажеш Міккі Мантл? Якщо ви хочете змінити назву на відповідний регістр, використовуйте =PROPER()функцію.
=PROPER(A2&" "&B2)
Далі потрібно скопіювати формулу до всіх комірок стовпця. Метод ярлика для цього полягає у подвійному клацанні ручки заповнення, поки вибрано комірку С2. Ручка заповнення - це темніша квадратна крапка в правому нижньому куті вказівника комірки. Точка виглядає так:
Коли вказівник миші знаходиться поруч із крапкою, вказівник миші змінюється на такий хрест.
Двічі клацніть, і формула буде скопійована до всіх комірок у діапазоні.
Примітка: Excel використовує стовпець ліворуч, коли з’ясовує, скільки копіювати комірки після подвійного клацання. Якщо у вас випадково була порожня комірка в B8, цей фокус зупинився б на рядку 7. Залиште Шер, щоб викликати проблему.
У цьому випадку вам може знадобитися взяти маркер заповнення та перетягнути вниз до всіх рядків, щоб скопіювати формулу.
Примітка 2: Належна функція є чудовою, але вона не правильно пише великі прізвища, такі як Маккартні (див. Клітинку C7). Вам доведеться вручну пройти і прописати великі літери C після Mc. Це також мало б проблему з VanHalen. Це біль? Так - але легше виправити кілька клітинок, ніж переписати все належним чином.
Перетворення формул у значення
Тепер, коли у стовпці C у вас є прізвище Ім'я, у вас може виникнути спокуса видалити стовпці A і B. Ви поки що цього не можете зробити. Якщо ви видалите стовпці A і B, усі формули в стовпці C зміняться на #REF! помилка. Ця помилка говорить: "Гей - ти сказав мені, що значення в цьому стовпці має бути від A2 і B2, але ти видалив ці клітинки, тому я не знаю, що тут помістити!".
Рішення полягає у зміні формул на значення перед видаленням стовпців A і B. Виконайте такі дії:
- Виділіть діапазон комірок у стовпці C
- Скопіюйте ці клітинки в буфер обміну, використовуючи ваш улюблений метод (4 способи на вибір: Ctrl + C, або Редагувати - Копіювати з меню, або значок буфера обміну на панелі інструментів, або клацніть правою кнопкою миші та виберіть копію).
- Не знімаючи виділення з комірок, у меню виберіть «Редагувати»> «Спеціальна вставка». У діалоговому вікні «Спеціальна вставка» виберіть «Значення», а потім «ОК». На цьому етапі поточне значення кожної комірки в діапазоні буде вставлено назад у комірку. Замість того, щоб мати формулу, тепер у вас буде статичне значення. Безпечно видаляти стовпці A і B.
Приєднання комірки, що містить текст, до комірки, що містить номер
Загалом, це спрацює досить добре. На зображенні нижче я використав формулу для побудови фрази, що містить ім’я у стовпці A із віком у стовпці C.
Фокус полягає в тому, що число відображається в одному форматі, а ви хочете, щоб воно використовувалося в іншому форматі. Класичним прикладом цього є дати. Дата 11 грудня 1943 року фактично зберігається як кількість днів з 1 січня 1900 року. Якщо я спробую приєднати текст до стовпця А з датою до стовпця В, я отримаю безглуздий вигляд.
Рішення полягає у використанні =TEXT()функції. Текстова функція вимагає двох аргументів. Перший аргумент - це комірка, що містить число. Другий аргумент - це власний формат числа, який вказує, як відображатиметься номер. Наступна формула дасть приємно відформатований результат.
У цій підказці викладено багато цікавих прийомів.
- Формула для об’єднання 2 стовпців тексту з використанням амперсанда як оператора конкатенації
- Як приєднати клітинку до текстового значення
- Як використовувати функцію PROPER для зміни імен на належні регістри
- Чому ви отримуєте #REF! помилка
- Як використовувати Вставити спеціальні значення для перетворення формул у значення.
- Приєднання комірки, що містить текст, до комірки, що містить номер
- Використання функції TEXT для управління відображенням дати у формулі.
Ця концепція передрукована з Партизанського аналізу даних за допомогою Microsoft Excel.
