Эксель разбивает текст по столбцам как отменить

Как в Excel разделить текст из одной ячейки в несколько

В руководстве объясняется, как разделить ячейки в Excel с помощью формул и стандартных инструментов. Вы узнаете, как разделить текст запятой, пробелом или любым другим разделителем, а также как разбить строки на текст и числа.

Разделение текста из одной ячейки на несколько — это задача, с которой время от времени сталкиваются все пользователи Excel. В одной из наших предыдущих статей мы обсуждали, как разделить ячейки в Excel с помощью функции «Текст по столбцам» и «Мгновенное заполнение». Сегодня мы подробно рассмотрим, как можно разделить текст по ячейкам с помощью формул.

Чтобы разбить текст в Excel, вы обычно используете функции ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT) или ПСТР (MID) в сочетании с НАЙТИ (FIND) или ПОИСК (SEARCH). На первый взгляд, некоторые рассмотренные ниже приёмы могут показаться сложными. Но на самом деле логика довольно проста, и следующие примеры помогут вам разобраться.

Для преобразования текста в ячейках в Excel ключевым моментом является определение положения разделителя в нем. Что может быть таким разделителем? Это запятая, точка с запятой, наклонная черта, двоеточие, тире, восклицательный знак и т.п. И, как мы далее увидим, даже целое слово.

В зависимости от вашей задачи эту проблему можно решить с помощью функции ПОИСК (без учета регистра букв) или НАЙТИ (с учетом регистра).

Как только вы определите позицию разделителя, используйте функцию ЛЕВСИМВ, ПРАВСИМВ и ПСТР, чтобы извлечь соответствующую часть содержимого.

Для лучшего понимания пошагово рассмотрим несколько примеров.

Делим текст вида ФИО по столбцам.

Далее в качестве разделителя выбираем пробел.

Обращаем внимание на то, как разделены наши данные в окне образца.

В следующем окне определяем формат данных. По умолчанию там будет «Общий». Он нас вполне устраивает, поэтому оставляем как есть. Выбираем левую верхнюю ячейку диапазона, в который будет помещен наш разделенный текст. Если нужно оставить в неприкосновенности исходные данные, лучше выбрать B1, к примеру.

В итоге имеем следующую картину:

=ЛЕВСИМВ(A2; ПОИСК(» «;A2;1)-1)

В качестве разделителя мы используем пробел. Функция ПОИСК указывает нам, в какой позиции находится первый пробел. А затем именно это количество букв (за минусом 1, чтобы не извлекать сам пробел) мы «отрезаем» слева от нашего ФИО при помощи ЛЕВСИМВ.

Далее будет чуть сложнее.

Нужно извлечь второе слово, то есть имя. Чтобы вырезать кусочек из середины, используем функцию ПСТР.

Как вы, наверное, знаете, функция Excel ПСТР имеет следующий синтаксис:

ПСТР (текст; начальная_позиция; количество_знаков)

Текст извлекается из ячейки A2, а два других аргумента вычисляются с использованием 4 различных функций ПОИСК:

• Начальная позиция — это позиция первого пробела плюс 1:

• Количество знаков для извлечения: разница между положением 2- го и 1- го пробелов, минус 1:

ПОИСК(» «;A2;ПОИСК(» «;A2)+1) — ПОИСК(» «;A2) – 1

В итоге имя у нас теперь находится в C.

Осталось отчество. Для него используем выражение:

В этой формуле функция ДЛСТР (LEN) возвращает общую длину строки, из которой вы вычитаете позицию 2- го пробела. Получаем количество символов после 2- го пробела, и функция ПРАВСИМВ их и извлекает.

Вот результат нашей работы по разделению фамилии, имени и отчества из одной по отдельным ячейкам.

Распределение текста с разделителями на 3 столбца.

Предположим, у вас есть список одежды вида Наименование-Цвет-Размер, и вы хотите разделить его на 3 отдельных части. Здесь разделитель слов – дефис. С ним и будем работать.

1. Чтобы извлечь Наименование товара (все символы до 1-го дефиса), вставьте следующее выражение в B2, а затем скопируйте его вниз по столбцу:

Здесь функция мы сначала определяем позицию первого дефиса («-«) в строке, а ЛЕВСИМВ извлекает все нужные символы начиная с этой позиции. Вы вычитаете 1 из позиции дефиса, потому что вы не хотите извлекать сам дефис.

1. Чтобы извлечь цвет (это все буквы между 1-м и 2-м дефисами), запишите в C2, а затем скопируйте ниже:

Логику работы ПСТР мы рассмотрели чуть выше.

1. Чтобы извлечь размер (все символы после 3-го дефиса), введите следующее выражение в D2:

Аналогичным образом вы можете в Excel разделить содержимое ячейки в разные ячейки любым другим разделителем. Все, что вам нужно сделать, это заменить «-» на требуемый символ, например пробел (« »), косую черту («/»), двоеточие («:»), точку с запятой («;») и т. д.

Примечание. В приведенных выше формулах +1 и -1 соответствуют количеству знаков в разделителе. В нашем примере это дефис (то есть, 1 знак). Если ваш разделитель состоит из двух знаков, например, запятой и пробела, тогда укажите только запятую («,») в ваших выражениях и используйте +2 и -2 вместо +1 и -1.

Как разбить текст по переносам строки.

Чтобы разделить слова в ячейке по переносам строки, используйте подходы, аналогичные тем, которые были продемонстрированы в предыдущем примере. Единственное отличие состоит в том, что вам понадобится функция СИМВОЛ (CHAR) для передачи символа разрыва строки, поскольку вы не можете ввести его непосредственно в формулу с клавиатуры.

Предположим, ячейки, которые вы хотите разделить, выглядят примерно так:

Напомню, что перенести таким вот образом текст внутри ячейки можно при помощи комбинации клавиш ALT + ENTER.

Возьмите инструкции из предыдущего примера и замените дефис («-») на СИМВОЛ(10), где 10 — это код ASCII для перевода строки.

Чтобы извлечь наименование товара:

=ПСТР(A2; ПОИСК(СИМВОЛ(10);A2) + 1; ПОИСК(СИМВОЛ(10);A2; ПОИСК(СИМВОЛ(10);A2)+1) — ПОИСК(СИМВОЛ(10);A2) — 1)

=ПРАВСИМВ(A2;ДЛСТР(A2) — ПОИСК(СИМВОЛ(10); A2; ПОИСК(СИМВОЛ(10); A2) + 1))

Результат вы видите на скриншоте выше.

Таким же образом можно работать и с любым другим символом-разделителем. Достаточно знать его код.

Как распределить текст с разделителями на множество столбцов.

Изучив представленные выше примеры, у многих из вас, думаю, возник вопрос: «А что, если у меня не 3 слова, а больше? Если нужно разбить текст в ячейке на 5 столбцов?»

Если действовать методами, описанными выше, то формулы будут просто мега-сложными. Вероятность ошибки при их использовании очень велика. Поэтому мы применим другой метод.

Имеем список наименований одежды с различными признаками, перечисленными через дефис. Как видите, таких признаков у нас может быть от 2 до 6. Делим текст в наших ячейках на 6 столбцов так, чтобы лишние столбцы в отдельных строках просто остались пустыми.

Для первого слова (наименования одежды) используем:

Как видите, это ничем не отличается от того, что мы рассматривали ранее. Ищем позицию первого дефиса и отделяем нужное количество символов.

Для второго столбца и далее понадобится более сложное выражение:

Замысел здесь состоит в том, что при помощи функции ПОДСТАВИТЬ мы удаляем из исходного содержимого наименование, которое уже ранее извлекли (то есть, «Юбка»). Вместо него подставляем пустое значение «» и в результате имеем «Синий-M-39-42-50». В нём мы снова ищем позицию первого дефиса, как это делали ранее. И при помощи ЛЕВСИМВ вновь выделяем первое слово (то есть, «Синий»).

А далее можно просто «протянуть» формулу из C2 по строке, то есть скопировать ее в остальные ячейки. В результате в D2 получим

=ЕСЛИОШИБКА(ЛЕВСИМВ(ПОДСТАВИТЬ($A2&»-«; ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:C2)&»-«;»»;1); ПОИСК(«-«;ПОДСТАВИТЬ($A2&»-«;ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:C2)&»-«;»»;1);1)-1);»»)

Обратите внимание, жирным шрифтом выделены произошедшие при копировании изменения. То есть, теперь из исходного текста мы удаляем все, что было уже ранее найдено и извлечено – содержимое B2 и C2. И вновь в получившейся фразе берём первое слово — до дефиса.

Если же брать больше нечего, то функция ЕСЛИОШИБКА обработает это событие и вставит в виде результата пустое значение «».

Скопируйте формулы по строкам и столбцам, на сколько это необходимо. Результат вы видите на скриншоте.

Таким способом можно разделить текст в ячейке на сколько угодно столбцов. Главное, чтобы использовались одинаковые разделители.

Как разделить ячейку вида ‘текст + число’.

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

Предположим, у вас есть столбец смешанного содержания, где число всегда следует за текстом. Естественно, такая конструкция рассматривается Excel как символьная. Вы хотите поделить их так, чтобы текст и числа отображались в отдельных ячейках.
Результат может быть достигнут двумя разными способами.

Метод 1. Подсчитайте цифры и извлеките это количество символов

Самый простой способ разбить выражение, в котором число идет после текста:

Чтобы извлечь числа, вы ищите в строке все возможные числа от 0 до 9, получаете общее их количество и отсекаете такое же количество символов от конца строки.

Если мы работаем с ячейкой ​​A2:

Чтобы извлечь буквы, вы вычисляете, сколько их у нас имеется. Для этого вычитаем количество извлеченных цифр (C2) из ​​общей длины исходной ячейки A2. После этого при помощи ЛЕВСИМВ отрезаем это количество символов от начала ячейки.

здесь A2 – исходная ячейка, а C2 — извлеченное число, как показано на скриншоте:

Метод 2: узнать позицию 1- й цифры в строке

Альтернативное решение — использовать эту формулу массива для определения позиции первой цифры:

Как видите, мы последовательно ищем каждое число из массива <0,1,2,3,4,5,6,7,8,9>. Чтобы избежать появления ошибки если цифра не найдена, мы после содержимого ячейки A2 добавляем эти 10 цифр. Excel последовательно перебирает все символы в поисках этих десяти цифр. В итоге получаем опять же массив из 10 цифр — номеров позиций, в которых они нашлись. И из них функция МИН выбирает наименьшее число. Это и будет та позиция, с которой начинается группа чисел, которую нужно отделить от основного содержимого.

Также обратите внимание, что это формула массива и ввод её нужно заканчивать не как обычно, а комбинацией клавиш CTRL + SHIFT + ENTER .

Как только позиция первой цифры найдена, вы можете разделить буквы и числа, используя очень простые формулы ЛЕВСИМВ и ПРАВСИМВ.

Чтобы получить текст:

Чтобы получить числа:

Где A2 — исходная строка, а B2 — позиция первого числа.

Чтобы избавиться от вспомогательного столбца, в котором мы вычисляли позицию первой цифры, вы можете встроить МИН в функции ЛЕВСИМВ и ПРАВСИМВ:

Для вытаскивания текста:

Для чисел:

Этого же результата можно достичь и чуть иначе.

Сначала мы извлекаем из ячейки числа при помощи вот такого выражения:

То есть, сравниваем длину нашего текста без чисел с его исходной длиной, и получаем количество цифр, которое нужно взять справа. К примеру, если текст без цифр стал короче на 2 символа, значит справа надо «отрезать» 2 символа, которые и будут нашим искомым числом.

А затем уже берём оставшееся:

Как видите, результат тот же. Можете воспользоваться любым способом.

Как разделить ячейку вида ‘число + текст’.

Если вы разделяете ячейки, в которых буквы стоят после цифр, вы можете отделять числа по следующей формуле:

Она аналогична рассмотренной в предыдущем примере, за исключением того, что вы используете функцию ЛЕВСИМВ вместо ПРАВСИМВ, чтобы получить число теперь уже из левой части выражения.

Теперь, когда у вас есть числа, отделите буквы, вычитая количество цифр из общей длины исходной строки:

Где A2 — исходная строка, а B2 — искомое число, как показано на снимке экрана ниже:

Как разбить текст по ячейкам по маске (шаблону).

Эта опция очень удобна, когда вам нужно разбить список схожих строк на некоторые элементы или подстроки. Сложность состоит в том, что исходный текст должен быть разделен не при каждом появлении определенного разделителя (например, пробела), а только при некоторых определенных вхождениях. Следующий пример упрощает понимание.

Предположим, у вас есть список строк, извлеченных из некоторого файла журнала:

Вы хотите, чтобы дата и время, если таковые имеются, код ошибки и поясняющие сведения были размещены в 3 отдельных столбцах. Вы не можете использовать пробел в качестве разделителя, потому что между датой и временем также есть пробелы. Также есть пробелы в тексте пояснения, который также должен весь находиться слитно в одном столбце.

Решением является разбиение строки по следующей маске: * ERROR: * Exception: *

Здесь звездочка (*) представляет любое количество символов.

Двоеточия (:) включены в разделители, потому что мы не хотим, чтобы они появлялись в результирующих ячейках.

То есть в данном случае в качестве разделителя по столбцам выступают не отдельные символы, а целые слова.

Итак, в начале ищем позицию первого разделителя.

Затем аналогичным образом находим позицию, в которой начинается второй разделитель:

Итак, для ячейки A2 шаблон выглядит следующим образом:

С 1 по 20 символ – дата и время. С 21 по 26 символ – разделитель “ERROR:”. Далее – код ошибки. С 31 по 40 символ – второй разделитель “Exception:”. Затем следует описание ошибки.

Таким образом, в первый столбец мы поместим первые 20 знаков:

Обратите внимание, что мы взяли на 1 позицию меньше, чем начало первого разделителя. Кроме того, чтобы сразу конвертировать всё это в дату, ставим перед формулой два знака минус. Это автоматически преобразует цифры в число, а дата как раз и хранится в виде числа. Остается только установить нужный формат даты и времени стандартными средствами Excel.

Далее нужно получить код:

Думаю, вы понимаете, что 6 – это количество знаков в нашем слове-разделителе «ERROR:».

Ну и, наконец, выделяем из этой фразы пояснение:

Аналогично добавляем 10 к найденной позиции второго разделителя «Exception:», чтобы выйти на координаты первого символа сразу после разделителя. Ведь функция говорит нам только то, где разделитель начинается, а не заканчивается.

Таким образом, ячейку мы распределили по 3 столбцам, исключив при этом слова-разделители.

Если выяснение загадочных поворотов формул Excel — не ваше любимое занятие, вам может понравиться визуальный метод разделения ячеек в Excel, который демонстрируется в следующей части этого руководства.

Как разделить ячейки в Excel с помощью функции разделения текста Split Text.

Альтернативный способ разбить столбец в Excel — использовать функцию разделения текста, включенную в надстройку Ultimate Suite for Excel. Она предоставляет следующие возможности:

• Разделить ячейку по символу-разделителю.
• Разделить ячейку по нескольким разделителям.
• Разделить ячейку по маске (шаблону).

Чтобы было понятнее, давайте более подробно рассмотрим каждый вариант по очереди.

Разделить ячейку по символу-разделителю.

Выбирайте этот вариант, если хотите разделить содержимое ячейки при каждом появлении определённого символа .

Для этого примера возьмем строки шаблона Товар-Цвет-Размер , который мы использовали в первой части этого руководства. Как вы помните, мы разделили их на 3 разных столбца, используя 3 разные формулы . А вот как добиться того же результата за 2 быстрых шага:

1. Предполагая, что у вас установлен Ultimate Suite , выберите ячейки, которые нужно разделить, и щелкните значок «Разделить текст (SplitText)» на вкладке «AblebitsData».

1. Панель Разделить текст откроется в правой части окна Excel, и вы выполните следующие действия:
   • Разверните группу «Разбить по символам (SplitbyCharacters)» и выберите один из предопределенных разделителей или введите любой другой символ в поле «Пользовательский (Custom)» .
   • Выберите, как именно разбивать ячейки: по столбцам или строкам.
   • Нажмите кнопку «Разделить (Split)» .

Примечание. Если в ячейке может быть несколько последовательных разделителей (например, более одного символа пробела подряд), установите флажок « Считать последовательные разделители одним».

Готово! Задача, которая требовала 3 формул и 5 различных функций, теперь занимает всего пару секунд и одно нажатие кнопки.

Разделить ячейку по нескольким разделителям.

Этот параметр позволяет разделять текстовые ячейки, используя любую комбинацию символов в качестве разделителя. Технически вы разделяете строку на части, используя одну или несколько разных подстрок в качестве границ.

Например, чтобы разделить предложение на части, используя запятые и союзы, активируйте инструмент «Разбить по строкам (Split by Strings)» и введите разделители, по одному в каждой строке:

В данном случае в качестве разделителей мы используем запятую и союз “или”.

В результате исходная фраза разделяется при появлении любого разделителя:

Примечание. Союзы «или», а также «и» часто могут быть частью слова в вашей исследуемой фразе, так что не забудьте ввести пробел до и после них, чтобы предотвратить разрывы слов на части.

А вот еще один пример. Предположим, вы импортировали столбец дат из внешнего источника, и выглядит он следующим образом:

Этот формат не является обычным для Excel, и поэтому ни одна из функций даты не распознает здесь какие-либо элементы даты или времени. Чтобы разделить день, месяц, год, часы и минуты на отдельные ячейки, введите следующие символы в поле Spilt by strings:

• Точка (.) Для разделения дня, месяца и года
• Двоеточие (:) для разделения часов и минут
• Пробел для разграничения даты и времени

Нажмите кнопку Split, и вы сразу получите результат:

Разделить ячейки по маске (шаблону).

Эта опция очень удобна, когда вам нужно разбить список однородных строк на некоторые элементы или подстроки.

Сложность заключается в том, что исходный текст не может быть разделен при каждом появлении заданного разделителя, а только при некоторых определенных вхождениях. Следующий пример упростит понимание.

Предположим, у вас есть список строк, извлеченных из некоторого файла журнала. Чуть выше в этой статье мы разбивали этот текст по ячейкам при помощи формул. А сейчас используем специальный инструмент. И вы сами решите, какой из способов удобнее и проще.

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

Решением является разбиение строки по следующей маске:

Где звездочка (*) представляет любое количество символов.

Двоеточия (:) включены в разделители, потому что мы не хотим, чтобы они появлялись в результирующих ячейках.

А теперь нажмите кнопку «Разбить по маске (Split by Mask)» на панели «Split Text» , введите маску в соответствующее поле и нажмите «Split».
Результат будет примерно таким:

Примечание. При разделении строки по маске учитывается регистр. Поэтому не забудьте ввести символы в шаблоне точно так, как они отображаются в исходных данных.

Большое преимущество этого метода — гибкость. Например, если все исходные строки имеют значения даты и времени, и вы хотите, чтобы они отображались в разных столбцах, используйте эту маску:

Проще говоря, маска указывает надстройке разделить исходные строки на 4 части:

• Все символы перед 1-м пробелом в строке (дата)
• Символы между 1-м пробелом и словом ERROR: (время)
• Текст между ERROR: и Exception: (код ошибки)
• Все, что идет после Exception: (текст описания)

Думаю, вы согласитесь, что использование надстройки Split Text гораздо быстрее и проще, нежели использование формул.

Надеюсь, вам понравился этот быстрый и простой способ разделения строк в Excel. Если вам интересно попробовать, ознакомительная версия доступна для загрузки здесь.

Вот как вы можете разделить текст по ячейкам таблицы Excel, используя различные комбинации функций, а также специальные инструменты. Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Поиск ВПР нескольких значений по нескольким условиям — В статье показаны способы поиска (ВПР) нескольких значений в Excel на основе одного или нескольких условий и возврата нескольких результатов в столбце, строке или в отдельной ячейке. При использовании Microsoft…
Формат времени в Excel — Вы узнаете об особенностях формата времени Excel, как записать его в часах, минутах или секундах, как перевести в число или текст, а также о том, как добавить время с помощью…
Как сделать диаграмму Ганта — Думаю, каждый пользователь Excel знает, что такое диаграмма и как ее создать. Однако один вид графиков остается достаточно сложным для многих — это диаграмма Ганта. В этом кратком руководстве я постараюсь показать…
Как сделать автозаполнение в Excel — В этой статье рассматривается функция автозаполнения Excel. Вы узнаете, как заполнять ряды чисел, дат и других данных, создавать и использовать настраиваемые списки в Excel. Эта статья также позволяет вам убедиться, что вы…
Функция ИНДЕКС в Excel — 6 примеров использования — В этом руководстве вы найдете ряд примеров формул, демонстрирующих наиболее эффективное использование ИНДЕКС в Excel. Из всех функций Excel, возможности которых часто недооцениваются и используются недостаточно, ИНДЕКС определенно занимает место…
Проверка данных в Excel: как сделать, использовать и убрать — Мы рассмотрим, как выполнять проверку данных в Excel: создавать правила проверки для чисел, дат или текстовых значений, создавать списки проверки данных, копировать проверку данных в другие ячейки, находить недопустимые записи,…
Быстрое удаление пустых столбцов в Excel — В этом руководстве вы узнаете, как можно легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых…
Как полностью или частично зафиксировать ячейку в формуле — При написании формулы Excel знак $ в ссылке на ячейку сбивает с толку многих пользователей. Но объяснение очень простое: это всего лишь способ ее зафиксировать. Знак доллара в данном случае служит только…
Чем отличается абсолютная, относительная и смешанная адресация — Важность ссылки на ячейки Excel трудно переоценить. Ссылка включает в себя адрес, из которого вы хотите получить информацию. При этом используются два основных вида адресации – абсолютная и относительная. Они…
Относительные и абсолютные ссылки – как создать и изменить — В руководстве объясняется, что такое адрес ячейки, как правильно записывать абсолютные и относительные ссылки в Excel, как ссылаться на ячейку на другом листе и многое другое. Ссылка на ячейки Excel,…

Как отменить разделение столбцов в Excel

Мы часто копируем тексты из текста в Excel и разбиваем тексты на разные столбцы или строки по некоторым правилам с помощью функции «Текст в столбцы». И в следующий раз, когда вы скопируете серию текстов, Excel будет ссылаться на то же правило, чтобы разделить скопированные данные. Если мы хотим остановить это, вы можете следовать статье ниже, и мы покажем вам способ предотвращения разделения текста на столбцы.

Не разбивать текст на столбцы

Подготовьте два списка имен в тексте, разделитель — запятая между именами:

Шаг 1. Скопируйте первую строку имен в Excel.

Шаг 2. Разделите имена на пять разных столбцов с помощью функции «Текст в столбцы»:

Шаг 3. Теперь скопируем вторую строку списка имен в текст.

Очевидно, что имена автоматически разделяются запятой. Вы также увидите значок параметров вставки.

Шаг 4. Чтобы остановить автоматическое разделение, щелкните значок «Параметры вставки», затем щелкните стрелку. Параметр по умолчанию — «Соответствие форматированию места назначения», поэтому вторая строка имен автоматически разбивается на столбцы, как и первая строка.

Шаг 5. Нажмите «Использовать мастер импорта тестов…». Мастер импорта текста автоматически загружается для пользователя. Просто используйте вариант по умолчанию: С разделителями и нажмите «Далее».

Несмотря на то, что параметр «Разделенные», похоже, используется для разделения данных запятыми или другими разделителями, он также может предотвратить разделение данных, сняв отметку с некоторых параметров, см. шаги ниже.

Шаг 6. В следующем окне снимите все флажки. В предварительном просмотре выбранных данных вы можете видеть, что все имена перечислены в одном столбце. Нажмите «Далее».

Шаг 7. Выберите формат данных столбца и нажмите «Готово».

Шаг 8. Теперь вы обнаружите, что имена перечислены в A2 без разделения.

Если вы часто пользуетесь Microsoft Excel, возможно, вы сталкивались с ситуациями, когда вам нужно было добавить или подсчитать ячейки с учетом регистра. Если вы выполняли эту задачу вручную, я хотел бы отметить .

Среднее значение – это полезная функция Excel, позволяющая быстро вычислить среднее значение последних N значений в столбце. Тем не менее, вам может потребоваться время от времени вставлять новые цифры под вашей исходной информацией, и .

В этом уроке показано, как использовать функцию ЕСЛИ в Excel для распределения баллов на основе количества прошедшего времени. Если вы намерены следовать этому руководству, вы можете сделать это, загрузив .

Предположим, что у вас есть список дат с разными форматами дат, как показано на прилагаемом рисунке. В этом случае функция сортировки Excel не сможет их правильно отсортировать. Однако вы можете преобразовать все различные форматы даты в определенный файл .

В статье показано, как проверить, является ли диапазон ячеек пустым или пустым, с помощью ISBLANK и других функций, чтобы распознавать пустые ячейки в Excel и предпринимать соответствующие действия в зависимости от их состояния. Есть несколько обстоятельств, при которых .

В Excel вы можете вычислить площадь параллелограмма, используя обычную формулу и оператор умножения. Чтобы получить площадь круга, используйте функцию PI и оператор экспоненты (). В Excel стандартный набор.

В этой публикации показано, как определить будущую стоимость инвестиций с помощью формулы годового графика начисления сложных процентов в Google Таблицах, а также приведены примеры того, как это сделать с использованием годовых, ежемесячных или ежедневных процентных ставок. Кроме того, вы будете .

В этой публикации показано, как определить будущую стоимость инвестиций с помощью формулы годового графика сложных процентов в Excel, а также приведены примеры того, как это сделать с использованием годовых, ежемесячных или ежедневных процентных ставок. Кроме того, вы получите .

Финансовый выбор играет решающую роль в разработке и реализации корпоративных стратегий и планов. В повседневной жизни мы также сталкиваемся с множеством финансовых решений. Например, предположим, что вы подаете заявку на получение кредита на покупку нового автомобиля. .

Распространенной задачей в Excel является разделение содержимого одной ячейки на несколько ячеек. Это часто необходимо, когда данные экспортируются из какого-либо другого программного обеспечения в Excel и их необходимо отсортировать, прежде чем их можно будет использовать. Это можно сделать с помощью функции «Текст в столбцы» либо с разделителем (например, пробелом, запятой, табуляцией и т. д.), либо с помощью ширины каждой части текста.

Текст в столбцы

Использование разделителя

Возможно, наиболее частым требованием в Excel является разделение полных имен на имена и фамилии. Допустим, у вас есть список имен, изображенный ниже, в столбце B, и вы хотите разделить его на имя (столбец C) и фамилию (столбец D). Очевидным разделителем здесь является пробел между именем и фамилией.

1. Выберите диапазон ячеек с данными, которые вы хотите разделить. В данном случае это B2:B8.

2. На ленте выберите Данные > Текст в столбцы.

3.Появится мастер преобразования текста в столбцы. На первом шаге установите для типа файла значение С разделителями (выбрано по умолчанию) и нажмите кнопку Далее.

4. На втором шаге выберите разделитель, в данном случае это один пробел. При выборе разделителя в области предварительного просмотра данных появляется линия, показывающая, как будут разделены данные.

Вы также можете выбрать табуляцию, точку с запятой, запятую или любой другой символ, который вы определили в качестве разделителя.

5. На последнем шаге определите формат данных столбца. По умолчанию новые столбцы имеют общий формат, но вы можете установить для них текст, дату или любой другой формат, который вам нужен (дополнительно).

Выберите столбцы (удерживайте клавишу CTRL и в окне предварительного просмотра данных щелкните заголовки столбцов, формат которых вы хотите установить). В разделе Формат данных столбца выберите Текст. Вам также необходимо определить пункт назначения, в котором будут заполняться столбцы. В данном случае назначением является ячейка C2.

В результате столбцы C и D заполнены раздельными именами и фамилиями.

Фиксированная ширина

Еще один вариант разделения текста на столбцы — использование фиксированной ширины. При использовании фиксированной ширины функция «Текст в столбцы» разбивает текст на определенное количество символов, а не на разделитель. Хорошим примером этого метода является разделение почтового индекса и названия города, поскольку почтовые индексы всегда состоят из пяти цифр. Допустим, у вас есть почтовый индекс и название города в столбце B, и вы хотите разделить их на столбцы C и D.

1. Чтобы разделить данные, сначала выберите диапазон ячеек с данными, которые вы хотите разделить. В данном случае это B2:B9.

2. На ленте выберите Данные > Текст в столбцы.

3. Снова появится Мастер преобразования текста в столбцы. На первом шаге установите тип файла Фиксированная ширина и нажмите Далее.

4. На втором этапе установите линию разрыва в данных предварительного просмотра, щелкнув между почтовым индексом и названием города.
Вы можете дважды щелкнуть линию разрыва, чтобы удалить ее, или выбрать и перетащить, чтобы переместить ее в другое место.

5. На последнем шаге определите формат данных столбца. По умолчанию новые столбцы имеют общий формат. (Вы можете изменить это, как описано в предыдущем разделе.) Вам также необходимо определить назначение, в котором будут заполняться столбцы. В данном случае назначением является ячейка C2.

Конечным результатом являются почтовые индексы и названия городов, разделенные на столбцы C и D.

Текст в столбцы в Google Таблицах

Разбиение текста на столбцы в Google Таблицах работает немного по-другому. Основное отличие состоит в том, что вы не можете установить ячейку назначения, поэтому разделенные данные перезапишут исходные данные. Также Google Sheets автоматически определяет разделитель, но вы также можете установить его по своему усмотрению. Шаги для разделения текстовых ячеек в Google Таблицах следующие.

1. Выберите диапазон ячеек, которые вы хотите разделить (B2:B9).

2. Выберите Данные > Разделить текст на столбцы.

В результате вы получите данные, разбитые на два столбца, начиная с ячейки B2. (Исходный столбец перезаписывается.)

Обратите внимание: если щелкнуть раскрывающееся меню «Разделитель» в правом нижнем углу, можно выбрать разделитель (запятая, точка с запятой, точка, пробел или другой). Как вы можете видеть на картинке выше, Google Sheets автоматически распознает, что разделителем столбцов является пробел, и разделяет текст на основе этого.

При работе с данными и электронными таблицами большое значение имеют удобочитаемость и структура.

Это упрощает просмотр данных и работу с ними. Один из лучших способов сделать ваши данные более удобочитаемыми – разбить их на части, чтобы облегчить доступ к нужной информации.

При вводе данных с нуля можно гарантировать, что мы структурируем данные, чтобы они были более удобочитаемыми. Однако иногда вам нужно работать с данными, созданными кем-то другим.

Если объем данных очень велик, обычно довольно сложно структурировать читабельность данных.

Например, у вас могут быть данные со списком имен, и вы можете расположить имена в алфавитном порядке фамилий.

В других случаях у вас может быть список адресов, но вы хотите правильно упорядочить эти данные, чтобы четко видеть, сколько людей проживает, скажем, в Нью-Йорке.

Лучший способ решить две вышеупомянутые проблемы – разбить один столбец на несколько столбцов.

В новых версиях Excel есть специальная функция, позволяющая делать это с помощью меню Данные.

Давайте посмотрим, как этого можно добиться в обоих приведенных выше случаях.

Как разделить один столбец на несколько столбцов

Допустим, у вас есть список имен, который вы хотите разделить на столбцы Имя и Фамилия.

1. Выберите столбец, который вы хотите разделить
2. На ленте данных выберите «Текст в столбцы» (в группе Инструменты данных). Откроется мастер преобразования текста в столбцы.
3. Здесь вы увидите параметр, позволяющий задать способ разделения данных в выбранных ячейках. Убедитесь, что этот параметр выбран. Если вы не знакомы с термином ‘разделитель’, это символ, указывающий, как данные в ячейках отделены друг от друга, например, имя и фамилия в каждой ячейке. разделены пробелом. Это означает, что разделителем здесь является символ пробела.
4. Нажмите «Далее».
5. Вот настройки, которые необходимо выполнить на втором этапе мастера Tetx to Columns:
   • По умолчанию разделитель Tab установлен. Но мы не хотим этим пользоваться. Мы хотим использовать разделитель Пробел. Поэтому снимите флажок с разделителя Tab и установите флажок Пробел (1).
   • Есть также флажок, который позволяет вам указать, хотите ли вы рассматривать последовательные разделители как один (2). Это означает, что если у вас по ошибке есть два пробела между именами, вы хотите рассматривать их как один пробел?
   • Вы можете увидеть, как ваши данные будут выглядеть после разделения, в области предварительного просмотра данных (3) в нижней части диалогового окна. Обратите внимание, что при выборе пробела мы получаем именно тот результат, который нам нужен.
   • Наконец нажмите «Далее» (4)
6. Теперь вы увидите параметр, в котором можно указать формат данных в столбцах. По умолчанию выбран параметр Общие, который гарантирует, что столбцы имеют тот же формат, что и исходные ячейки. Оставьте его с выбранным параметром Общие и нажмите «Готово».

Теперь у нас есть два столбца данных: имя в столбце A и фамилия в столбце B.

Важно отметить, что при разделении содержимого ячейки Excel не вставляет новые ячейки для хранения содержимого.

Поэтому новые ячейки перезапишут содержимое следующей ячейки справа. Поэтому вы должны убедиться, что вы оставили пустое место справа перед разделением.

У вас также есть возможность выбрать место назначения разделенных данных.

Вы можете указать это на шаге 7, введя место, где вы хотите, чтобы разделенные ячейки отображались в поле ввода назначения. Здесь также можно выбрать ячейку назначения.

Само собой разумеется, что количество столбцов, на которые будут разбиты ваши данные, зависит от выбранных вами разделителей.

Это означает, что если у вас есть запятая в качестве разделителя и в некоторых ячейках есть три слова, разделенные запятыми, ваши данные будут разделены на три столбца.

Как разделить несколько строк в ячейке на несколько ячеек

Теперь давайте обсудим, как действовать в случаях, когда у вас есть много информации, представленной в отдельных строках ячейки.

Возьмите, например, лист ниже.

Здесь вы можете увидеть полный адрес, указанный в каждой ячейке. Каждая часть адреса находится на отдельной строке ячейки. Разделение этого столбца на четыре разных столбца, которые могут отображать полное имя человека, улицу, город и страну, значительно упростит выявление закономерностей в данных.

К сожалению, разделить ячейки несколькими строками не так просто, как метод, описанный выше. Но это тоже не слишком сложно. Вот как вы можете решить эту проблему.

1. Выберите столбец, который вы хотите разделить
2. На ленте данных выберите «Текст в столбцы» (в группе «Инструменты данных»). Откроется мастер преобразования текста в столбцы.
3. Здесь вы увидите параметр, позволяющий задать способ разделения данных в выбранных ячейках. Убедитесь, что этот параметр выбран.
4. Нажмите «Далее».
5. По умолчанию разделитель табуляции отмечен флажком. Снимите все отмеченные разделители и выберите вариант «Другое». В маленьком поле рядом с этой опцией вам нужно указать символ-разделитель, который вы хотите использовать. Если вы хотите указать символ разрыва строки. Нажмите Ctrl + J на ​​клавиатуре. Это покажет крошечную мигающую точку внутри коробки. Это означает, что был вставлен разделитель разрыва строки.

Вы можете увидеть, как ваши данные будут выглядеть после разделения, в области предварительного просмотра данных в нижней части диалогового окна.

Обратите внимание, что мы получаем именно тот результат, который нам нужен. У нас есть все имена в первом столбце, вторые строки (названия улиц) во втором столбце, названия городов в третьем столбце и названия стран в четвертом столбце.

1. Нажмите «Далее».
2. Теперь вы увидите параметр, в котором можно указать формат данных в столбцах. По умолчанию выбран параметр «Общие», который гарантирует, что столбцы имеют тот же формат, что и исходные ячейки.
3. Здесь мы также хотим, чтобы все столбцы отображались, начиная со столбца B, чтобы новые ячейки не перезаписывали существующий столбец. Рядом с местом назначения мы видим написанную ячейку «$ A $ 2». Мы можем изменить это, выбрав нужную ячейку назначения «$B$2» и нажав «Готово».
4. Вы можете получить диалоговое окно с вопросом, хотите ли вы заменить данные, которые уже присутствуют в целевых ячейках. Нажмите «ОК».

После этого вы найдете столбцы с B по E, каждый из которых содержит отдельный элемент адреса, который присутствовал в столбце A.

Как разделить объединенную ячейку

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

1. Нажмите объединенную ячейку. Если вы хотите разъединить несколько ячеек, выберите их все.
2. В разделе инструментов Выравнивание вкладки Главная вы увидите раскрывающийся список рядом с параметром Объединить и центрировать. Нажмите на стрелку раскрывающегося списка и выберите «Разъединить ячейки«.
3. Это приведет к разделению объединенной ячейки на исходное количество ячеек.

Обратите внимание, что в Excel нет возможности разбить необъединенную ячейку на ячейки меньшего размера (как это возможно в MS Word).

Мы обсудили, как разделить ячейки в Excel на отдельные ячейки, используя разные типы разделителей. Мы также кратко рассмотрели, как разделить ячейки, которые ранее были объединены.

Вышеупомянутые шаги были указаны при условии, что вы используете версии Excel с 2013 по 2019. Однако Microsoft продолжает обновлять свои меню, вкладки и параметры в каждой новой версии, которую она выпускает.

Поэтому мы не можем точно сказать, будут ли упомянутые нами методы работать в более поздних версиях Excel.

Бывает сложно работать с большим количеством текста в одном столбце.

Особенно, если вы хотите использовать формулы для работы с определенными частями этого текста.

И хотя есть несколько сложных формул, которые помогут вам разделить текст на новые столбцы, это может занять много времени и работать не очень хорошо.

Вот тут на помощь приходит текст в столбцы!

Это отличный инструмент Excel для разделения текста на более удобные части.

И это намного проще, чем пытаться работать с формулами, чтобы сделать то же самое!

Каспер Лангманн, соучредитель Spreadsheeto

*Это руководство предназначено для Excel 2019/Microsoft 365 (для Windows). Есть другая версия? Нет проблем, вы можете выполнить те же действия.

Получите БЕСПЛАТНЫЙ файл с упражнениями

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

Если вы хотите своими глазами увидеть, как работает Text to Columns, загрузите его и следуйте инструкциям!

Загрузите БЕСПЛАТНЫЙ файл упражнения

Разделение текста фиксированной ширины

Текст в столбцы можно использовать в двух разных режимах: с фиксированной шириной и с разделителями. Хотя разделители могут быть более полезными, фиксированная ширина немного проще. Итак, мы начнем здесь.

Сначала поговорим о разнице.

Текст фиксированной ширины и текст с разделителями

Когда вы разбиваете текст на столбцы, Excel должен знать, где делать разбиение.

При разделении по фиксированной ширине Excel разделяет текст по определенному количеству символов. Поэтому он может разделить текст на 5, 15 и 27 символы.

Разделение с разделителями, с другой стороны, происходит, когда Excel видит определенные символы. Он может разделить текст по каждой найденной запятой, табуляции или пробелу.

На первом листе примера книги вы увидите список годов выпуска и моделей автомобилей. Поскольку каждый год в списке состоит из четырех цифр, мы можем использовать разбиение по фиксированной ширине.

Сначала выберите данные, которые нужно разделить (в данном случае это ячейки A2:A51). Затем перейдите на вкладку «Данные» и нажмите «Текст в столбцы»:

В мастере преобразования текста в столбцы установите переключатель рядом с параметром Фиксированная ширина:

Нажмите «Далее», и вы увидите предварительное изображение того, где Excel разделит ваш текст.

Excel сделает предположение, где вы можете разделить данные. В этом случае он угадывает правильно!

Каспер Лангманн, соучредитель Spreadsheeto

Если Excel ошибается, вы можете внести изменения.

Нажмите на предварительный просмотр, чтобы добавить строку (Excel разделит текст на каждую строку).

Дважды щелкните строку, чтобы удалить ее.

Нажмите и перетащите линию, чтобы переместить ее.

Нажмите «Далее». Теперь вы можете выбрать, как импортировать каждый новый столбец:

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

В большинстве случаев можно оставить значение «Общие». Вот что мы здесь сделаем.

Каспер Лангманн, соучредитель Spreadsheeto

Вам также необходимо указать место назначения для ваших новых данных.

Имейте в виду, что если вы укажете в качестве места назначения столбец, из которого поступают ваши данные (как это делается по умолчанию), исходные данные будут перезаписаны.

Мы изменим пункт назначения на B2, чтобы сохранить исходные данные:

Наконец, нажмите «Готово», чтобы разделить текст на столбцы!

Excel разделил текст именно там, где мы сказали, и создал два новых столбца, с которыми намного проще работать.

Обратите внимание, что Excel также удалил начальный пробел из ячеек во втором столбце. Очень удобно!

Каспер Лангманн, соучредитель Spreadsheeto

Разделение текста с разделителями

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

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

Несмотря на это, разбиение с разделителями часто намного быстрее, чем фиксированное.

Каспер Лангманн, соучредитель Spreadsheeto

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

Давайте разделим его на три столбца: один для имени, один для номера телефона и один для города.

Для начала откройте мастер «Текст в столбцы», щелкнув «Текст в столбцы» на вкладке «Данные». Однако на этот раз выберите «С разделителями»:

После нажатия кнопки «Далее» вам нужно будет выбрать разделитель. Поскольку между полями в наших данных нет определенных символов, мы выберем Пробел и отменим выбор всего остального:

Как вы можете видеть в предварительном просмотре, Excel разбивает текст по каждому пробелу, то есть разбивает имена и номера телефонов на несколько ячеек.

Мы займемся этим позже.

Нажмите «Далее», чтобы выбрать формат столбцов.

На этот раз мы изменим все на текст, чтобы Excel не возился с форматированием телефонных номеров. Удерживая нажатой клавишу Shift, выберите все столбцы и нажмите кнопку-переключатель «Текст».

Мы установим место назначения B2, чтобы сохранить наши исходные данные. Затем нажмите Готово, чтобы получить новые столбцы:

Excel выдает нам множество предупреждений о числах, хранящихся в виде текста, но не беспокойтесь об этом. С телефонными номерами это то, что нам нужно.

Каспер Лангманн, соучредитель Spreadsheeto

Теперь нужно просто очистить данные!

Для этого используйте функции TEXTJOIN или CONCATENATE, чтобы снова объединить имена в столбец Full Name. Сделайте то же самое с телефонными номерами.

Обратите внимание, что некоторые города содержат несколько слов, что приводит к странным разделителям и разделениям. Вероятно, вам придется исправить это вручную.

Как отменить вставку текста по столбцам в excel

Разделение текста на столбцы с помощью мастера распределения текста по столбцам

В этом курсе:

• Вставка и удаление строк и столбцов
  Статья
• Выделение содержимого ячеек в Excel
  Статья
• Закрепление областей окна для блокировки строк и столбцов
  Статья
• Скрытие и отображение строк и столбцов
  Статья
• Фильтр уникальных значений или удаление повторяющихся значений
  Статья
• Разделение текста на столбцы с помощью мастера распределения текста по столбцам
  Статья
• Создание списка последовательных дат
  Статья

Разделение текста на столбцы с помощью мастера распределения текста по столбцам

С помощью мастера распределения текста по столбцам текст, содержащийся в одной ячейке, можно разделить на несколько.

Проверьте, как это работает!

Выделите ячейку или столбец с текстом, который вы хотите разделить.

На вкладке Данные нажмите кнопку Текст по столбцам.

В мастере распределения текста по столбцам установите переключатель с разделителями и нажмите кнопку Далее.

Выберите разделители для своих данных. Например, запятую и пробел. Данные можно предварительно просмотреть в окне Образец разбора данных.

Нажмите кнопку Далее.

В поле Поместить в выберите место на листе, где должны отображаться разделенные данные.

Как отменить текст по столбцам в excel

В Excel есть автоматический инструмент, предназначенный для разделения текста по столбцам. Он не работает в автоматическом режиме, поэтому все действия придется выполнять вручную, предварительно выбирая диапазон обрабатываемых данных. Однако настройка является максимально простой и быстрой в реализации.

1. С зажатой левой кнопкой мыши выделите все ячейки, текст которых хотите разделить на столбцы.
2. После этого перейдите на вкладку «Данные» и нажмите кнопку «Текст по столбцам».
3. Появится окно «Мастера разделения текста по столбцам», в котором нужно выбрать формат данных «с разделителями». Разделителем чаще всего выступает пробел, но если это другой знак препинания, понадобится указать его в следующем шаге.

Из этой инструкции можно сделать вывод, что использование такого инструмента оптимально в тех ситуациях, когда разделение необходимо выполнить всего один раз, обозначив для каждого слова новый столбец. Однако если в таблицу постоянно вносятся новые данные, все время разделять их таким образом будет не совсем удобно, поэтому в таких случаях предлагаем ознакомиться со следующим способом.

Способ 2: Создание формулы разделения текста

В Excel можно самостоятельно создать относительно сложную формулу, которая позволит рассчитать позиции слов в ячейке, найти пробелы и разделить каждое на отдельные столбцы. В качестве примера мы возьмем ячейку, состоящую из трех слов, разделенных пробелами. Для каждого из них понадобится своя формула, поэтому разделим способ на три этапа.

Шаг 1: Разделение первого слова

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

Для удобства создадим три новые столбца с подписями, куда будем добавлять разделенный текст. Вы можете сделать так же или пропустить этот момент.

Полностью созданная формула имеет вид =ЛЕВСИМВ(A1;ПОИСК(» «;A1)-1) , вы же можете создать ее по приведенной выше инструкции или вставить эту, если условия и разделитель подходят. Не забывайте заменить обрабатываемую ячейку.

Шаг 2: Разделение второго слова

Самое трудное — разделить второе слово, которым в нашем случае является имя. Связано это с тем, что оно с двух сторон окружено пробелами, поэтому придется учитывать их оба, создавая массивную формулу для правильного расчета позиции.

В этом случае основной формулой станет =ПСТР( — запишите ее в таком виде, а затем переходите к окну настройки аргументов.

Формула получилась большая, и не все пользователи понимают, как именно она работает. Дело в том, что для поиска строки пришлось использовать сразу несколько функций, определяющих начальные и конечные позиции пробелов, а затем от них отнимался один символ, чтобы в результате эти самые пробелы не отображались. В итоге формула такая: =ПСТР(A1;ПОИСК(» «;A1)+1;ПОИСК(» «;A1;ПОИСК(» «;A1)+1)-ПОИСК(» «;A1)-1) . Используйте ее в качестве примера, заменяя номер ячейки с текстом.

Шаг 3: Разделение третьего слова

Последний шаг нашей инструкции подразумевает разделение третьего слова, что выглядит примерно так же, как это происходило с первым, но общая формула немного меняется.

В пустой ячейке для расположения будущего текста напишите =ПРАВСИМВ( и перейдите к аргументам этой функции.

7 примеров использования функции «Текст по столбцам» в Excel

Эта функция крайне удивительна и может послужить вам в разных ситуациях.

Самое простое её использование — разделить текст по нескольким столбикам, но её также можно использовать и для других целей.

Сегодня, я продемонстрирую несколько вариантов использования функции «Текст по столбцам».

Как использовать функцию «Текст по столбцам»

Все очень просто — щелкните «Данные» -> «Текст по столбцам»

Далее откроется окно функции. Само выполнение состоит из 3 этапов. На каждом определяются некоторые параметры, которые индивидуальны в каждом случае, не переживайте, мы их рассмотрим.

Итак, давайте я покажу вам варианты использования.

Деление строки

Допустим, у нас есть такая табличка:

И, например, нам нужно разделить имя + фамилию и поместить их в разные столбцы.

Как это сделать?

• Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

• На первом этапе выбираем «с разделителями», так как мы будем делить строку по пробелу;

• На втором этапе, собственно, указываем что пробел в нашем случае будет разделителем строки;

• И на третьем этапе указываем куда поместить данные;

• Подтверждаем.

• Таким образом, в нашем конкретном случае пробел являлся разделителем. Если же у вас будет строка не просто имя пробел фамилия, а будут, например, инициалы, нужно будет менять схему деления;

• Также не забывайте о том, что результат выполнения функции будет статическим, т.е. если в изначальных ячейках меняются значения — вам нужно будет заново использовать функцию.

Делим электронную почту на имя пользователя и домен

Итак, в этом случае, мы будем использовать другой разделитель.

Допустим, у нас есть следующая табличка:

Я просто придумал эти почты исходя из фильмов по супергероям ��

Разделителем, как вы могли догадаться, в этом случае будет знак «@».

Давайте разделим строки.

Как это сделать?

• Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

• На первом этапе выбираем «с разделителями», так как мы будем делить строку по знаку «@»;

• На втором этапе, в опции «другой» укажите наш разделитель;

• И укажите куда поместить данные после обработки;

• Подтвердите.

Получаем главный адрес сайта из ссылки

Бывает такое, что из множества ссылок вам нужно «вытащить» только главные адреса сайтов.

Например, в случае https://yandex.ru/primer1 главный адрес будет yandex.ru.

Итак, допустим у нас есть следующая табличка:

Давайте разделим строки и получим главные адреса сайтов!

Как это сделать?

• Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

• На первом этапе выбираем «с разделителями»;

• На втором этапе выберите «другой» и укажите «/»;

• Укажите куда нужно поместить данные;

• Подтвердите.

Далее можно очистить таблицу от ненужных столбцов и дубликатов.

Важная информация: в случае, если ваши ссылки не имеют https:// в начале — все будет еще проще, вы получите главный адрес сайта в первом столбике.

Приведение дат к общему формату

Часто бывает так, что при импорте дат из разных источников они имеют разный формат.

Но что делать если этих данных очень много, а необходимо сделать так, чтобы все даты были в одном, «общем», формате?

Допустим, у нас есть такая табличка:

Давайте приведем все даты к одному формату!

Как это сделать?

• Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

• На первом этапе выбираем «с разделителями»;

• На втором этапе уберите все галочки;

• На третьем этапе выберите опцию «дата — ДМГ» и укажите куда поместить обработанные данные;

• Подтвердите.

Готово! Таким образом даты были отформатированы.

Смена формата с текстового на числовой

Бывают случаи, когда вы получаете от кого-то табличку, но не можете быстро произвести расчеты, так как некоторые числа в ней имеют текстовый формат.

Такое может быть по нескольким причинам:

• Перед числом есть знак “ ‘ “;

• Эти числа выведны благодаря некоторым функциям, которые присваивают им текстовый формат.

Допустим, у нас есть такая табличка:

Давайте переведем их в формат «Числовой».

Как это сделать?

• Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

• На первом этапе выбираем «с разделителями»;

• На втором этапе уберите все галочки;

• нужная опция «общий»;

• Подтвердите.

Итак, теперь все числа автоматически отформатируются как «общий» формат и их можно использовать при вычислениях.

Убираем несколько первых символов значения каждой ячейки

При работе в Excel вы можете столкнуться и с такой задачей.

Допустим, у нас есть такая табличка:

Давайте удалим первые пять букв с начала каждой строки.

Как это сделать?

• Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

• На первом этапе выбираем «фиксированной ширины»;

• На втором этапе — поставьте указатель на нужное количество символов (в нашем случае пять);

• Укажите куда нужно поместить данные;

• Подтвердите.

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

Преобразуем числа со знаком минус на конце в отрицательные числа

Такое встречается довольно редко, но все равно будет полезно знать, что делать в такой ситуации.

Допустим, у нас есть такая табличка:

Давайте обработаем эти числа.

Как это сделать?

• Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

• На первом этапе выбираем «с разделителями»;

• На втором этапе уберите все галочки;

• На третьем этапе щелкните на «Подробнее…»;

• В открывшемся окне поставьте галочку на опции указанной на картинке ниже;

• Укажите куда поместить обработанные данные;

• Подтвердите.

Готово, теперь числа будут отрицательными. Можете использовать их в формулах.

Как отменить текст по столбцам в excel

Как перестать разбивать текст на столбцы при вставке данных в Excel?

В Excel, если вы скопируете некоторые данные из другого формата файла и вставите их на рабочий лист, затем разделите данные с помощью функции «Текст в столбец», а затем в следующий раз, когда вы вставите данные аналогичного типа на рабочий лист, данные будут автоматически разделены на столбцы. Иногда вы хотите предотвратить разделение данных. Здесь я расскажу, как остановить автоматический вывод текста в столбец в Excel.

Запретить текст в столбец при вставке данных

Например, у вас есть данные в текстовом документе, как показано на скриншоте ниже:

Затем вы копируете его и вставляете на рабочий лист, а затем используете Текст в столбец функцию и разделите данные запятыми, см. снимок экрана:

Затем нажмите OK, данные разделены запятыми. И когда вы в следующий раз скопируете и вставите данные с запятыми в Excel, данные будут автоматически разделены запятыми.

Теперь я расскажу, как остановить этот автоматический текст в столбце.

1. Просто выберите пустую ячейку, затем введите в нее любые данные, затем выберите ее и щелкните Данные > Текст в столбцы. Смотрите скриншот:

2. в Текст в столбцы диалог, проверьте разграниченный и нажмите Далее перейти к следующему шагу; затем в диалоговом окне следующего шага оставьте все параметры снятыми в Разделители раздел. Смотрите скриншот:

3. Нажмите Завершить. Теперь, когда вы копируете данные из файла другого формата и вставляете данные на лист, текст не разделяется автоматически.

Горячие клавиши Excel. Выделение ⁠ ⁠

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях

Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.

Ввод данных в ячейку — клавиша Enter

Извлечение текста в Excel. Страшные, но иногда такие полезные текстовые функции⁠ ⁠

Друзья, всем привет. В прошлой статье я рассказывал про мгновенное заполнение в Excel. Вещь интересная, но, как некоторые пользователи отмечали в комментариях (и вполне обоснованно), мгновенное заполнение не всегда подходит для решения тех или иных задач. В первую очередь, это касается данных, которые в таблице часто меняются. Мгновенное заполнение не реагирует на изменение данных. В этом случае лучше использовать функции. Да, это дольше, да, это будет выглядеть страшно (далее поймёте, почему), но зато один раз сделал и потом наслаждаешься плодами своих трудов. В качестве примера я решил взять тестовое задание, которое давно проходил при приёме на работу в одну довольно известную компанию.

Важное уточнение! В Excel можно решать очень сложные задачи. Но должна быть хоть какая-то логика. Чем сложнее логика, тем сложнее будут сочетания функций, набор инструментов и т.д. Потратишь много времени, но решишь. А вот если логики нет, то не ждите чуда. Чудите сами, как говорится.

Что же, давайте приступим.

Есть столбец с текстом:

Нам необходимо в отдельные столбцы разнести код и наименование.

Первое, на что обращаем внимание, есть ли здесь закономерность? С кодом всё понятно. Нужно извлечь текст до первого пробела. Отлично. Находим порядковый номер первого пробела с помощью функции НАЙТИ (FIND):

Далее всё просто. С помощью функции ЛЕВСИМВ (LEFT) извлекаем всё, что до пробела, отнимая от порядкового номера пробела единицу:

Я показал пошаговое решение. Но это можно всё сделать и в одной формуле. Вместо D2 вставляем НАЙТИ (FIND):

Перейдём к более интересному — наименование. И снова начинаем с логики. Нужно извлечь текст между первым и последним пробелом. В Excel нет функции, которая по щучьему велению, по нашему хотению извлекает текст между первым и последним символом (например, пробелами). Придётся хитрить. Сначала пошагово, потом впихнём всё в одну ячейку (и оторвёмся по полной).

Надо подсчитать, а сколько в принципе у нас пробелов в тексте. Сделать это можно вот как:

ДЛСТР (LEN)(А2) (как вам название?) — подсчитывает общее количество символов в ячейке. ДЛСТР(ПОДСТАВИТЬ(A2;» «;»»)) — подсчитывает количество символов без пробелов. От них мы избавились благодаря функции ПОДСТАВИТЬ (SUBSTITUTE), которая заменила пробелы на пусто.

Теперь очень мудрёный шаг. Нам надо заменить последний пробел на какой-нибудь другой произвольный символ:

ПОДСТАВИТЬ (SUBSTITUTE)(A2;» «;»*»;E2) как раз это и делает. Даём ей исходный текст, что нужно найти, на что заменить, и какой символ по порядку (именно для этого нам нужно было узнать, сколько всего пробелов).

Зачем нам это? Немного терпения, друзья, сейчас всё будет.

Логика решения меняется. Теперь нам надо извлечь текст между первым пробелом и звёздочкой. А вот это реализовать уже можно. Находим порядковый номер звёздочки:

ПСТР (MID) — как много в этом слове. А функция, вообще-то, полезная. Указываем ей ячейку, порядковый номер, с которого хотим начать извлекать символы, и само количество символов.

А2 — понятно, ячейка с текстом.

D2+1 — начиная с какого символа будем извлекать (первый пробел + 1)

G2-D2-1 — количество извлекаемых символов. Порядковый номер звёздочки — порядковый номер первого пробела — 1 (чтобы исключить лишний символ в конце).

А теперь комбо! Всё в одной ячейке:

Говорил же, что будет страшно, а вы не верили.

Вот и решение задачи. Вообще, моё мнение, такие штуки в любом случае нужно сначала решать пошагово, а потом только впихивать всё в одну ячейку. Но тут уж каждый сам для себя решает.

В файле, который идёт к этой статье, есть ещё один готовый пример. Это тоже вполне себе реальная задача, которую слушатели предложили решить во время корпоративного обучения. Исходный текст нужно было переделать в то, что указано в «Должно быть»:

Здесь уже расписывать не буду, а то ещё одна «простыня» получится. Кому интересно, посмотрите. Там, правда, уже не так всё подробно по шагам расписано. Но я в вас верю!

Можно и ещё более сложные конструкции использовать. В комментариях к прошлой статье были варианты с обработкой пустых ячеек. Про всё не расскажешь. Если честно, я перед собой такую цель и не ставлю. Главное, что я хотел вам показать — это то, что прежде, чем кидаться искать функции, посмотрите, а есть ли вообще логика в том, что вы хотите? Если есть, тогда и решение найдётся.

Как всегда, огромное всем спасибо, кто потратил своё драгоценное время и внимание на чтение данного материала. Надеюсь, что-то вам обязательно пригодится. Пишите комментарии, делитесь своими мыслями, решениями и вопросами. Да пребудет с нами сила ИКСэль.

П.С. недавно проводил мастер-класс по разным штукам в Excel, про которые здесь почти не писал. Например, рассказал про то, что такое расширенный фильтр.

Мгновенное заполнение в Excel — магия в чистом виде⁠ ⁠

Друзья, всем привет. Сегодня хочу рассказать вам про мгновенное заполнение в Excel.

Ссылка на файл, чтобы можно было потренироваться — https://disk.yandex.ru/i/HyW0N215F6CuUg

Возможно, многие с ним знакомы заочно. Наверняка же замечали, что когда вручную заполняешь какие-то значения в ячейках, то с переходом к следующей ячейке при вводе символов Excel порой выдаёт вот такой список:

Так вот это и есть мгновенное заполнение во всей своей красе. Да, иногда это раздражает, потому что тебе это не нужно. Но в большинстве случае польза мгновенного заполнения огромна.

Извлечение данных

Предположим, у нас есть вот такой столбец с текстом:

Нам нужно извлечь отдельно номер договора и дату. Это можно сделать с помощью инструмента «Текст по столбцам». Правда, потом придётся от символа «№» ещё избавляться. А вот мгновенное заполнение справится с этим намного быстрее. Просто вводим справа от текста в первую ячейку номер договора (1), нажимаем Enter. Далее возможны два варианта.

Вариант 1. Вручную вводим в ячейку первую цифру второго договора (2). Excel предлагает свои варианты, жмём Enter — PROFIT!

Вариант 2. После того, как перешли ко второй ячейке, сразу нажимаем сочетание Ctrl + E (Е английская, конечно). Именно это сочетание отвечает за запуск мгновенного заполнения. Аналогично с датами. Вводим в ячейку С2 дату первого договора — Enter — Ctrl + E — наслаждаемся результатом.

ОЧЕНЬ ВАЖНАЯ ЧАСТЬ СТАТЬИ.

Так как же это работает? Всё довольно просто. В первой ячейке мы задаём образец, чего хотим получить, далее Excel распознаёт нашу логику и заполняет остальные ячейки по образу и подобию.

Ух ты! И так будет работать всегда?! Строго говоря — нет. Иногда, Excel не может с одной ячейки распознать логику. В этом случае нужно вручную заполнить не одну, а две, три, четыре (если случай совсем запущенный) ячейки. И только после этого нажимать Ctrl + E. Чем больше ячеек заполняешь, тем выше вероятность того, что твоя логика будет верно распознана могучим интеллектом Excel. Порой мгновенное заполнение не справляется с поставленной задачей:

Даты в первом столбце указаны в формате ГГГГ-ММ-ДД. При попытке привести их в формат ДД-ММ-ГГГГ получается вот такая «красота». Поэтому не поленитесь после того, как все ячейки будут заполнены, пробежаться по ним, а тот ли в них результат, который ты ожидал увидеть.

Образцы вводите в соседнем столбце от источника (можно справа или слева). Не «убегайте» далеко от данных, результат может быть непредсказуемым или вообще ничего не будет.

Ещё одно важное дополнение: мгновенное заполнение работает в версиях Excel 2013 и выше.

Теперь, когда с пояснениями закончено, давайте посмотрим, на что ещё способен этот удивительный инструмент.

Извлечение только чисел из столбца

Если нам из «красивого» столбца, в котором есть значения вроде «123руб», «55 рублей» и так далее, нужно извлечь только цифры, то вы уже знаете, что нам поможет:

В данном конкретном случае я прописал вручную две первых ячейки, иначе Excel не понимал, что нужны только числа.

Работа с текстом

В столбце указаны Имя и Фамилия. Нам нужно получить результат в виде «Имя Ф.» В первой ячейке вводим образец — Enter — Ctrl + E:

Кстати, если попробовать получить Фамилия И., то будьте внимательны. Если прописать два примера, потом начать вводить третий, то появляется довольно забавный список:

Но если не начинать вводить в третью ячейку текст, а сразу нажать на Ctrl + E, то всё будет нормально. Раз на раз не приходится. Временами мгновенное заполнение ведёт себя очень странно.

Извлечение части сплошного текста

Необходимо разбить слипшийся текст на части. Вводим в первых двух ячейках образец — Ctrl + E:

С номером поступаем аналогично.

Сбор текста

В отдельных столбцах есть различная информация, которую необходимо собрать в одно предложение. Обратите внимание, что порядок столбцов для мгновенного заполнения роли не играет. Прописываем предложение в первой ячейке — Enter — Ctrl + E:

На этом статью я хотел бы завершить. Уверен, я перечислил далеко не все чудесные возможности мгновенного заполнения. Буду вам благодарен, если в комментариях поделитесь своими способами применения этой чудесной штуки.

В качестве небольшой рекламы позвольте оставить здесь ссылку на мастер-класс, который я буду проводить 9 марта. Кто хочет узнать ещё несколько полезных приёмов при работе в Excel (там почти не будет того, о чём я писал здесь), а ещё хочет услышать чуть больше про то, где я работаю, записывайтесь — Полезные приемы при работе в Excel. Часть 2 (specialist.ru)

На этом всё. Как обычно, спасибо огромное всем, кто потратил своё драгоценное время и осилил данное полотно. Надеюсь, было полезно. Видео по данной статье обязательно появится на моём канале — (36) Андрей Митрохин — YouTube

Таблица подсчета розеток/выключателей/рамок. ⁠ ⁠

Когда я работал в магазине электротоваров регулярно приходилось считать ЭУИ и рамки к ним по зарисовкам заказчиков или их работников, тогда я это делал на бумаге и неплохо набил на этом руку. Но у некоторых продавцов консультантов это выходит не слишком быстро и качественно. Для автоматизации процесса я решил создать таблицу в google, а затем перенес ее в Excel (последний мне нравится больше). Таблицей я намерен поделиться ссылки будут ниже, а пока краткое описание:

Это страница «Сводка» первоначально ее надо заполнить под себя и сохранить как шаблон:
— Наименования всех типов ЭУИ какие у вас могут быть (если не достаточно того, что ввел я)
— Цвета механизмов (или например серия + цвет, как удобнее будет)
— Цвета рамок (аналогично механизмам)

Потом переходим на страницу «Ввод данных»
При добавлении новой строки указываете комнату
цвет механизма, цвет рамки и наполнение постов выбираете из выпадающего списка (подтянутся варианты со страницы «Сводка»), когда вы выбираете механизм для поста — ячейка окрашивается, считая количество постов в рамке

После заполнения страницы «Ввод данных», возвращаемся на «Сводка»
При выборе нужного цвета в крайней правой таблице («Текущий цвет») в списке ЭУИ и рамок останется только то количество, которое соответствует выбранным цветам.
Кроме того, общее количество механизмов и постов в рамках и количество установочных коробок

ЗЫ Отдельное спасибо @XaXa3Pa3a

Полезные трюки при работе в Excel⁠ ⁠

Всем привет. Это моя первая статья на Пикабу, поэтому позвольте сначала представиться. Я являюсь преподавателем Microsoft Excel. Теперь, когда с формальностями покончено, можно перейти к основному.

Сомнения перед написанием

Я довольно часто читаю разный тематический материал на Пикабу, и меня восхищают большинство авторов и статей. Статьи восхищают, в первую очередь, своей интересностью (есть такое слово вообще?) и полезностью. Именно поэтому у меня были большие сомнения, а стоит ли вообще лезть со своими очередными «простыми, но полезными штуками при работе в Excel». Да и кому вообще ты со своим Excel нужен?! Тем более, что беглый поиск по сайту не выдал ни одной подобной статьи. И та часть меня, которая отвечает за неуверенность, сразу подметила, что раз нет, значит, оно никому не нужно. А может, просто плохо искал. И да, я отдаю себе отчёт в том, что подобного материала довольно много на просторах интернета. И всё-таки, принцип «лучше сделать и жалеть, чем не сделать вовсе» возобладал.

Почему я посчитал, что это будет полезно

Занимаясь преподаванием этой замечательной программы (а я и правда считаю её чудесной и, можно сказать, влюблён в неё), я довольно часто подмечал, что именно мелочи оказывают самое большое впечатление на слушателей. Рассказываешь про сочетание функций ИНДЕКС(ПОИСКПОЗ), какое оно крутое, позволяет двумерный поиск по таблице осуществлять и много чего ещё делать, все сидят, понимающе кивают. Потом в процессе показываешь какую-нибудь мелочь, вроде той, что листы можно копировать, зажав Ctrl и мышкой перетащив лист чуть правее/левее, аудитория сразу оживает: «Ну всё, не зря время потратили». Именно про такие вот простые приёмы я и хотел бы вам рассказать (про первый так уже рассказал).

Небольшое пояснение

Путь до той или иной команды обычно описывается следующим образом: название вкладки — потом группа команд — сама команда:

Если у вас ноутбук, то функциональные клавиши могут работать только при одновременном нажатии на кнопку Fn+F1-12 (есть такие ноутбуки, в которых и этот способ не работает, тут надо уже по модели ноута смотреть).

Вообще, почти каждая функциональная клавиша отвечает за какое-то действие. Но я остановлюсь на одной, а именно — F4. И нет, речь пойдёт не про то, что этой кнопкой в Excel мы можем менять тип ссылки для ячейки.

F4 — повтор последнего выполненного пользователем действия (если нажимать её не тогда, когда курсор находится в строке формул)

Например, вам нужно для нескольких несмежных столбцов установить определённую ширину. Вместо того, чтобы каждый раз выбирать столбец, потом переходить на вкладку Главная — Ячейки — Формат — Ширина столбца. Можно один раз проделать эту операцию, потом просто выделить следующий столбец и нажать F4. И такой фокус можно проделывать со многими операциями, будь то закраска ячеек, строк, столбцов, части графика на диаграмме или банальная вставка столбцов (да, столбец можно вставлять сочетанием Ctrl + «+», но ведь это две кнопки, а F4 — одна).

Представления

Представления, с моей точки зрения, являются одним из самых недооценённых инструментов в Excel. Предположим, у вас есть таблица, в которой вы часто фильтруете несколько столбцов по разным критериям: отдел, пол и город.

И вот вы каждый раз раскрываете фильтр, устанавливаете нужные критерии, просматриваете данные, потом раскрываете фильтр, следующий критерий, потом фильтр. Думаю, суть вы уловили. «Но всё меняется, когда приходят они — представления!» © Установив нужные критерии, переходим на вкладку Вид — Режимы просмотра книги — нажимаем Представления:

Далее всё интуитивно (куда же без интуиции в этой прекрасной программе) понятно. Жмёшь «Добавить», обзываешь представление так, как тебе угодно — Ок. Здесь же, в окне добавления представления, мы можем узнать, а что, собственно, Excel сохраняет. А сохраняет он параметры печати, результаты фильтрации, скрытые строки и столбцы. Создав под каждый набор фильтров, строк и столбцов представление, потом лёгким и непринуждённым нажатием на эту команду ты будешь менять свою таблицу в мгновение ока. Это не совсем удобно? Что же, согласен. Давайте сделаем ещё удобнее и добавим представления на панель быстрого доступа. Для этого раскроем настройку панели быстрого доступа — Другие команды:

В открывшемся окне в поле «Выбрать команды из:» выбираем «Все команды». Потом находим «Представления» — Добавить:

Кстати, так можно добавить на панель быстрого абсолютно любую команду.

Теперь у нас появился выпадающий список со всеми нашими сохранёнными представлениями. Через это же окно можно и новые представления создавать. Просто пишешь в нём название, нажимаешь Enter — готово.

ПРЕДУПРЕЖДЕНИЕ!

Представления не работают в книгах, в которых есть «умные» таблицы (таблицы, которые мы создаём через вкладку Главная — Стили — Форматировать как таблицу).

После создания представления не нужно перемещать столбцы/менять их местами, иначе представление прекратит работать.

Два окна одной книги.

Прежде, чем кидать в меня различные предметы с криками «мало того, что про какой-то Excel пишет, так сейчас ещё будет рассказывать, как в двух окнах работать, смерд?!» позвольте пояснить. Речь пойдёт о том, как работать в двух окнах с ОДНОЙ книгой. Давайте смоделируем ситуацию. Есть у тебя два монитора (если ещё нет, обязательно заводи второй, пускай небольшой, но чтобы был), один файл Excel с несколькими листами внутри. Тебе нужно из одной таблицы перенести данные в другую (сравнить их, связать формулами и так далее). Что ты делаешь? Правильно, бесконечно долго и уныло переключаешься между листами. Второй монитор тем временем грустно за этим наблюдает. Но можно сделать этот процесс более удобным и быстрым. Прошу любить и жаловать, вкладка Вид — Окно — Новое окно:

Нажав на эту команду, мы получим ту же самую книгу, но в другом рабочем окне. Название файла будет немного изменено на «Мой файл:1» и «Мой файл:2». А дальше уже дело за тобой. Располагай окна так, как тебе удобно (на одном мониторе, на разных), копируй данные, создавай связи, формулы — в общем, работай. Но делать ты это уже будешь быстрее и удобнее. Все изменения, которые мы вносим в любое из клонированных окон, появляются сразу во всех связанных окнах. Главное, не забыть нажать «Сохранить» хоть в каком-нибудь окне.

Специальная вставка (пропускать пустые ячейки)

Вообще, про специальную вставку в Excel можно написать отдельную статью, наверное. Инструмент во многих случаях просто незаменимый. Но в рамках данной статьи я расскажу только про одну возможность. Представим, что есть две таблицы:

Нужно перенести данные из крайнего правого столбца второй таблицы (столбец Р) в крайний столбец первой таблицы (столбец F) таким образом, чтобы существующие номера остались. Обычным копированием-вставкой сделать это не получится, так как в столбце Р есть пустые ячейки, которые заменят собой существующие номера в столбце F. И тут на сцену выходит специальная вставка. Выделяем диапазон из столбца Р, копируем. Далее выбираем ячейку, начиная с которой нужно вставить данные (в нашем случае это F2), и либо щёлкаем правую кнопку мыши — в контекстном меню ищем «Специальная вставка», либо нажимаем сочетание клавиш Ctrl+Alt+V. Попадаем в такое окно:

Ставим галочку рядом с «пропускать пустые ячейки» — Ок. Профит!

Хочу отметить, что большинство приёмов, которые я здесь описал, не начнут прям с ходу экономить вам часы рабочего времени. Но если постепенно приучить себя их использовать, вспоминать о них, то скорость работы будет неуклонно возрастать. На этом, пожалуй, всё. Спасибо всем, кто уделил своё внимание и драгоценное время чтению поста. Надеюсь, что кому-то это было полезно. Вообще, если хотя бы одному человеку данный материал поможет в работе, я уже буду считать это успехом.

P.S. Если статья покажется интересной и полезной, то на примете есть ещё несколько приёмов, про которые могу рассказать.

Друзья, создал на Ютубе свой канал. Пока только видео с первой статьёй. В ближайшие дни опубликую вторую часть. Полезные трюки и приёмы при работе в Microsoft Excel — YouTube

Если б мишки были пчёлами… условия в Excel⁠ ⁠

Решил попробовать формат коротких постов по Excel

Дети, сегодня я расскажу вам о такой удобной функции в Excel как ЕСЛИ.

В общем виде выглядит так:

ЕСЛИ (условие; результат если условие верное; результат если условие не верное)

Я буду писать формулу, а на картинке будет результат этой формулы в 5 столбце

При множественных условиях иногда удобно применять функции И, и ИЛИ, которые позволяют соединять несколько условий

=ЕСЛИ(И(A2=»овощи»;D2>50);»дорогой овощ»;»либо не дорогой, либо не овощ»)

Не забываем, что можно комбинировать несколько ЕСЛИ Главное не запутаться в скобках и точках с запятыми.

Как видим результат получается довольно гибкий.
Также напомню, что есть операторы сравнения <,>,<=,>=,=,<>.

А на этом сегодня все, пойду дальше писать про макросы…

Ответ на пост «Помощь в формировании Таблицы на основе 2 таблиц»⁠ ⁠

Если по-простому, формулами, то так:

Таблицу 3 переделываем в другой вид:

Это будет состав детали, даты будем добавлять вправо.

(Таблица соответствия количества материала количеству детали)

Таблица 2 тогда будет выглядеть так:

(тупо суммируем кол-во материалов на дату)

1. Важно чтобы во всех 3-х таблицах даты были синхронизированы, чтобы можно было протягивать формулы не опасаясь что-то поломать.

2. Важно следить чтобы в составе (таблица 3) не было дубликатов пар — [деталь — материал] (желтые столбцы), иначе объем задваиваться будет.

Сумма итогов, чтобы её не переписывать каждый раз (в ячейке H2) — может выглядеть так:

Ответ на пост «Excel, делим покупки на несколько человек»⁠ ⁠

И так господа, перед Вами методичка по распиливанию затрат после попоек.

Разработана она мною, но на копирайт не претендую.

Ситуация проста: Пятница, Вы собрались пошуметь. Заказаны «стартовые» места, собрана компания друзей, сняты ограничения с карточек, побрились, подмылись, вызвали такси и поехали!

Сразу рекомендация: договоритесь как будете делить счёт. Вполне возможно, что в вашей компании тот, кому завтра платить за ипотеку. И он вообще в принципе не собирается много тратить.

Помните, между друзьями не должно быть обид или споров по денежным вопросам. Если у вас в компании есть друг, не желающий сопровождать общий счёт, объясните ему, что он сам платит за себя и главное платит СРАЗУ! Принесли счёт в ресторане, он сам запоминает и считает сумму напитого и наеденного и СРАЗУ кидает на стол деньги, а ещё фиксит это, например в общем чате. Кстати, общий чат — это тема. История с карточками — это хорошо, но налом платить в некоторых местах безопасней. Кидайте сразу в общий чат, кто, сколько и главное, за что заплатил. Поверьте, с утра вам будет не до воспоминаний, да и не всё вспомнить. Не забывайте о конспирации! Нельзя писать «3000 Диме на приват». Продумайте заранее все фразы и договоритесь об обозначениях.

Вы проснулись в субботу, в обед с привкусом тухлых кошачьих носков во рту, тремя размытыми печатями, двумя клубными браслетами и одной татуировкой с рожей чихуахуа на правом бедре. На карте минус месячный доход. Но вы всё предусмотрели, поэтому гоу в чат и историю операций с карты в приложении банка.

Помните! Очень большая вероятность, что вы вчера платили за всех и вам все должны (что не факт). В этом случае не один из ваших друзей чисто морально не хочет к похмелью ещё добавлять чувство долга! Поэтому от того, как быстро вернёте деньги зависит только от вас. Теперь вы коллектор и начинаете обзвон друзей.

1. Кидаем в чат позывной. Кто, за что, вчера платил.

2. Открываем Excell и создаём следующую таблицу:

И так: Перед вами таблица со следующими параметрами:

Столбец «Событие» — кратко фиксим события оплаты;

Столбцы «Участники» — все, кто присутствовал на пьянке;

Столбец «Сумма» — Общая сумма к разделу;

Столбец «N» — кол-во человек участвующих в разделе;

Столбец «Проверка» — собственно сумма всех сумм в столбцах B-E = 0.

Если пьянка длилась несколько дней (например Нашествие), то слева ещё вставляется таблица «Дата».

Допустим начали сначала Вася, Петя, Игорь. Зашли в Кафе, пожрали, да выпили на 9500 рубликов. Платил Петя.

Тогда для Пети пишется формула:

Для всех, кто учувствовал пишется формула:

В результате получаем:

Все кто с минусом — те в долгах;

Кто с плюсом -тот кредитор.

Формулы с $ помогают просто копипастить ячейки, и заполняя только столбцы Сумма и N, долбить долг дальше по событиям:

Как мы видим, в пабе к нам пришёл ещё Ваня.

Дальше из караоке идём в клуб. Ваня цепляет тёлку, Вася уезжает домой.

Для этого вводим ещё один столбец а с Васей прощаемся. В клубе мы оставляем 15 к.

Ваня с «Ваней — 2» уезжают, а Петя и Игорь идут:

После стрипухи все возвращаются в кафе на завтрак, при этом по дороге подбирая уже сбежавшего под утро Ваню. В кафе решают платить по честному! Поэтому каждый платит за то, сто съел (Платит Ваня):