Разное

Как расширить строку в экселе: Как расширить ячейку в таблице Excel

Содержание

Как сделать автоподбор высоты строки в Excel

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

Автоподбор высоты строк

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

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

Метод 1. Автоподбор высоты через формат ячеек

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

Итак, давайте приступим:

  1. Щелкаем правой кнопкой мыши по ячейке/выделенному диапазону (в нашем случае выделяем всю таблицу) и в контекстном меню щелкаем по строке “Формат ячеек…”.
  2. Откроется окно форматирования, в котором переключаемся во вкладку “Выравнивание”. В параметрах “Отображение” ставим галочку напротив опции “переносить текст”, после чего щелкаем OK.
  3. Благодаря нашим действиям, содержимое выделенных ячеек полностью отобразилось в них с учетом переноса, а высота строк изменилась ровно на столько, сколько необходимо для того, чтобы полностью отобразить все данные.

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

Метод 2. Настройка высоты через координатную панель

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

Метод 3. Автоподбор высоты для нескольких строк

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

  1. На координатной панели вместо одной строки одновременно выделяем диапазон строк, высоту которых хотим настроить. Как это сделать, читайте в нашей статье “Как выделить ячейки в Эксель“.
  2. Теперь аналогично методу, описанному выше, двойным щелчком левой кнопки мыши автоматически задаем высоту строк. Наводить указатель мыши можно на нижнюю границу любой строки выделенного диапазона.
  3. Все готово. Мы задали оптимальную высоту для всех строк, которые выбрали, выполняя первый шаг.

Метод 4. Использование инструментов на ленте

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

  1. Отмечаем область ячеек, высоту которых нужно автоматически задать.
  2. Переключаемся во вкладку “Главная” и нажимаем кнопку “Формат”, которая расположена в разделе “Ячейки”. Откроется перечень команд, среди которых нам нужна строка “Автоподбор высоты строки”, следовательно, щелкаем по данному пункту.
  3. Это все, что нужно было сделать для того, чтобы автоматически задать высоту строк для выделенного фрагмента данных.

Метод 5. Настройка высоты для объединенных ячеек

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

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

  1. Сначала нужно выделить ячейки, которые мы якобы хотим объединить.
  2. Затем щелчком правой кнопки мыши по выделенному диапазону вызываем контекстное меню, в котором выбираем строку “Формат ячеек…”.
  3. Переключаемся во вкладку “Выравнивание”, кликаем по текущему варианту выравнивания по горизонтали, выбираем “по центру выделения” и жмем кнопку OK.
  4. В результате текст из первой ячейки расположился по центру выделенных элементов, при этом, объединение ячеек не было выполнено.
  5. Теперь осталось только применить функцию автоподбора высоты строк любым из способов, описанных выше.

Заключение

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

Автоподбор высоты строки в Excel: подробная инструкция

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

Применение атоподбора

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

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

Происходит активация окна форматирования. Переходим во вкладку «Выравнивание». В блоке настроек «Отображение» устанавливаем флажок напротив параметра «Переносить по словам».

Чтобы сохранить и применить изменения конфигурации настроек, жмем на кнопку «OK», которая размещена внизу этого окна.

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

Способ 1: панель координат

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

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

Способ 2: включение автоподбора для нескольких строк

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

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

    Если диапазон сильно большой, то кликаем левой кнопкой мыши по первому сектору, потом зажимаем кнопку

    Shift на клавиатуре и выполняем щелчок по последнему сектору панели координат требуемой области. При этом все её строки будут выделены.

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

Урок: Как выделить ячейки в Excel

Способ 3: кнопка на ленте инструментов

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

  1. Выделяем диапазон на листе, к которому нужно применить автоподбор. Находясь во вкладке
    «Главная»
    , производим щелчок по кнопке «Формат». Данный инструмент размещен в блоке настроек «Ячейки». В появившемся списке в группе «Размер ячейки» выбираем пункт «Автоподбор высоты строки».
  2. После этого строки выделенного диапазона увеличат свою высоту настолько, насколько нужно для того, чтобы их ячейки показали все свое содержимое.

Способ 4: подбор высоты для объединенных ячеек

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

  1. Выделяем те ячейки, которые нужно объединить. Кликаем по выделению правой кнопкой мыши. В меню переходим по пункту «Формат ячеек…».
  2. В открывшемся окне форматирования переходим во вкладку «Выравнивание». В блоке настроек «Выравнивание» в поле параметра «По горизонтали» выбираем значение «По центру выделения». После того, как конфигурирование произведено, жмем на кнопку «OK».
  3. После этих действий данные расположились по всей зоне выделения, хотя по факту продолжают храниться в крайней левой ячейке, так как объединения элементов, по сути, не произошло. Поэтому, если, например, нужно будет удалить текст, то сделать это можно будет только в крайней левой ячейке. Далее опять выделяем весь диапазон листа, на котором размещен текст. Любым из трех предыдущих способов, которые были описаны выше, включаем автоподбор высоты.
  4. Как видим, после этих действий автоподбор высоты строки был произведен при сохраняющейся иллюзии объединения элементов.

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

Мы рады, что смогли помочь Вам в решении проблемы.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
ДА НЕТ

Как сделать ячейку шире в excel?

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

Процедура расширения

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

Способ 1: простое перетаскивание границ

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

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

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

Способ 2: расширение нескольких столбцов и строк

Существует также вариант расширить несколько столбцов или строк одновременно.

  1. Выделяем одновременно несколько секторов на горизонтальной и вертикальной шкале координат.
  2. Устанавливаем курсор на правую границу самой правой из ячеек (для горизонтальной шкалы) или на нижнюю границу самой нижней ячейки (для вертикальной шкалы). Зажимаем левую кнопку мыши и тянем появившуюся стрелочку соответственно вправо или вниз.
  3. Таким образом расширяется не только крайний диапазон, но и ячейки всей выделенной области.

Способ 3: ручной ввод размера через контекстное меню

Также можно произвести ручной ввод размера ячеек, измеряемый в числовых величинах. По умолчанию высота имеет размер 12,75 единиц, а ширина – 8,43 единицы. Увеличить высоту можно максимум до 409 пунктов, а ширину до 255.

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

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

  1. Выделяем сектор или диапазон вертикальной шкалы координат. Кликаем по этому участку правой кнопкой мыши. В контекстном меню выбираем пункт «Высота строки…».
  2. Открывается окошко, в котором нужно вбить нужную высоту ячеек выбранного диапазона в единицах. Делаем это и жмем на кнопку «OK».

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

Способ 4: ввод размера ячеек через кнопку на ленте

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

  1. Выделяем на листе ячейки, размер которых нужно установить.
  2. Переходим во вкладку «Главная», если находимся в другой. Кликаем по кнопке «Формат», которая располагается на ленте в группе инструментов «Ячейки». Открывается список действий. Поочередно выбираем в нем пункты «Высота строки…» и «Ширина столбца…». После нажатия на каждый из этих пунктов будут открываться небольшие окошки, о которых шёл рассказ при описании предыдущего способа. В них нужно будет ввести желаемую ширину и высоту выделенного диапазона ячеек. Для того, чтобы ячейки увеличились, новая величина этих параметров должна быть больше, чем установленная ранее.

Способ 5: увеличение размера всех ячеек листа или книги

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

  1. Для того, чтобы совершить данную операцию, следует, прежде всего, выделить нужные элементы. Для того, чтобы выделить все элементы листа, можно просто нажать сочетание клавиш на клавиатуре Ctrl+A. Существует и второй вариант выделения. Он предполагает нажатие на кнопку в виде прямоугольника, которая расположена между вертикальной и горизонтальной шкалой координат Excel.
  2. После того, как выделили любым из этих способов лист, жмем на уже знакомую нам кнопку «Формат» на ленте и производим дальнейшие действия точно так же, как описано в предыдущем способе с переходом по пунктам «Ширина столбца…» и «Высота строки…».

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

  1. Кликаем правой кнопкой мыши по ярлыку любого из листов, который расположен внизу окна сразу над шкалой состояния. В появившемся меню выбираем пункт «Выделить все листы».
  2. После того, как листы выделены, производим действия на ленте с использованием кнопки «Формат», которые были описаны в четвертом способе.

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

Способ 6: автоподбор ширины

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

  1. Выделяем диапазон, к которому желаем применить свойства автоподбора ширины. Кликаем по выделению правой кнопкой мыши. Открывается контекстное меню. Выбираем в нем пункт «Формат ячеек…».
  2. Открывается окно форматирования. Переходим во вкладку «Выравнивание». В блоке настроек «Отображение» устанавливаем галочку около параметра «Автоподбор ширины». Жмем на кнопку «OK» в нижней части окна.

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

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

Помогла ли вам эта статья?

Да Нет

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

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

Работа со строками, столбцами и ячейками

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

Чтобы изменить ширину столбца:
  1. Установите курсор на линию столбца в заголовке столбца так, чтобы курсор из белого креста стал двойной стрелкой.
  2. Нажмите левую кнопку мыши и тяните столбец вправо, чтобы увеличить его ширину, и влево, чтобы ее уменьшить.
  3. Отпустите кнопку мыши. Ширина столбца на листе будет изменена.

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

Чтобы установить точное значение ширины столбца:
  1. Выделите столбцы, которые нужно изменить.
  2. Кликните по команде Формат на вкладке Главная. Появится выпадающее меню.
  3. Выберите Ширина столбца.
  4. Откроется диалоговое окно ширина столбца. Введите нужную ширину.
  5. Нажмите OK. Ширина каждого выделенного столбца на листе изменится.

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

Чтобы изменить высоту строки:
  1. Установите курсор на линию строки так, чтобы курсор из белого креста стал двойной стрелкой.
  2. Нажмите левую кнопку мыши и тяните строку вниз, чтобы увеличить ее высоту, и вверх, чтобы ее уменьшить.
  3. Отпустите кнопку мыши. Высота строки на листе будет изменена.
Чтобы установить точное значение высоты строки:
  1. Выделите строки, которые нужно изменить.
  2. Кликните по команде Формат на вкладке Главная. Появится выпадающее меню.
  3. Выберите Высота строки.
  4. Откроется диалоговое окно Высота строки. Введите нужную высоту строки.
  5. Нажмите OK. Высота каждой выделенной строки на листе изменится.

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

Чтобы вставить строки:
  1. Выделите строку, над которой вы хотите вставить новую.
  2. Кликните по команде Вставить на вкладке Главная.
  3. На листе появится новая строка.

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

Чтобы вставить столбцы:
  1. Выделите столбец справа от которого вы хотите вставить новый. Например, чтобы вставить столбец между столбцами A и B, выделите столбец B.
  2. Кликните по команде Вставить на вкладке Главная.
  3. Новый столбец появится на листе.

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

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

Чтобы удалить строки:
  1. Выделите удаляемые строки.
  2. Кликните по команде Удалить на вкладке Главная.
  3. Строки будут удалены с листа.
Чтобы удалить столбцы:
  1. Выделите удаляемые столбцы.
  2. Кликните по команде Удалить на вкладке Главная.
  3. Столбцы будут удалены с листа.

Перенос текста и объединение ячеек

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

Чтобы установить Перенос текста:
  1. Выделите нужные ячейки.
  2. Выберите команду Перенос текста на вкладке Главная.
  3. Текст в выбранных ячейках будет отображаться в несколько строк.

Если вы передумали переносить текст, просто еще раз нажмите на кнопку команды Перенос текста.

Чтобы объединить ячейки с помощью команды «Объединить и поместить в центре»:
  1. Выделите объединяемые ячейки.
  2. Выберите команду «Объединить и поместить в центре» на вкладке Главная.
  3. Выделенные ячейки будут объединены, а текст помещен в центре.

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

Чтобы получить доступ к параметрам объединения:

Кликните по стрелке выпадающего меню у команды «Объединить и поместить в центре» на вкладке Главная. Появится выпадающее меню.

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

Практикуйте!

  1. Откройте существующую Excel 2010 книгу.
  2. Измените размер столбца.
  3. Измените размер строки в 46 пикселей.
  4. Вставьте колонку между колонками А и В.
  5. Вставка строку между строк 3 и 4.
  6. Удалите столбец или строку.
  7. Попробуйте объединить несколько ячеек.

Форматирование и редактирование ячеек в Excel – удобный инструмент для наглядного представления информации. Такие возможности программы для работы бесценны.

Значимость оптимальной демонстрации данных объяснять никому не нужно. Давайте посмотрим, что можно сделать с ячейками в Microsoft Excel. Из данного урока вы узнаете о новых возможностях заполнения и форматирования данных в рабочих листах.

Как объединить ячейки без потери данных Excel?

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

Порядок объединения ячеек в Excel:

  1. Возьмем небольшую табличку, где несколько строк и столбцов.
  2. Для объединения ячеек используется инструмент «Выравнивание» на главной странице программы.
  3. Выделяем ячейки, которые нужно объединить. Нажимаем «Объединить и поместить в центре».
  4. При объединении сохраняются только те данные, которые содержатся в верхней левой ячейке. Если нужно сохранить все данные, то переносим их туда, нам не нужно:
  5. Точно таким же образом можно объединить несколько вертикальных ячеек (столбец данных).
  6. Можно объединить сразу группу смежных ячеек по горизонтали и по вертикали.
  7. Если нужно объединить только строки в выделенном диапазоне, нажимаем на запись «Объединить по строкам».

В результате получится:

Если хоть одна ячейка в выбранном диапазоне еще редактируется, кнопка для объединения может быть недоступна. Необходимо заверить редактирование и нажать «Ввод» для выхода из режима.

Как разбить ячейку в Excel на две?

Разбить на две ячейки можно только объединенную ячейку. А самостоятельную, которая не была объединена – нельзя. НО как получить такую таблицу:

Давайте посмотрим на нее внимательнее, на листе Excel.

Черта разделяет не одну ячейку, а показывает границы двух ячеек. Ячейки выше «разделенной» и ниже объединены по строкам. Первый столбец, третий и четвертый в этой таблице состоят из одного столбца. Второй столбец – из двух.

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

Как разделить ячейку в Excel по диагонали?

Для решения данной задачи следует выполнить следующий порядок действий:

  1. Щелкаем правой кнопкой по ячейке и выбираем инструмент «Формат» (или комбинация горячих клавиш CTRL+1).
  2. На закладке «Граница» выбираем диагональ. Ее направление, тип линии, толщину, цвет.
  3. Жмем ОК.

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

На вкладке «Иллюстрации» выбираем «Фигуры». Раздел «Линии».

Проводим диагональ в нужном направлении.

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

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

  1. Выделить нужный диапазон, вмещающий определенное количество ячеек. Щелкаем правой кнопкой мыши по любой латинской букве вверху столбцов.
  2. Открываем меню «Ширина столбца».
  3. Вводим тот показатель ширины, который нам нужен. Жмем ОК.

Можно изменить ширину ячеек во всем листе. Для этого нужно выделить весь лист. Нажмем левой кнопкой мыши на пересечение названий строк и столбцов (или комбинация горячих клавиш CTRL+A).

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

Как разбить ячейку на строки?

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

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

Выделяем ячейку. На вкладке «Выравнивание» нажимаем кнопку «Перенос текста».

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

Пробуйте, экспериментируйте. Устанавливайте наиболее удобные для своих читателей форматы.

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

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

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

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

С изменением высоты все аналогично: выделили блок и перемещайте границу, которая находится под номером выбранного ряда (в примере, линия под цифрой 8).

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

Появится вот такое окошечко. В нем нужно вписать значение. Обратите внимание, оно указывается в пунктах. Как и пиксели – это единица измерения, с которой можно работать в Эксель. Нажмите «ОК».

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

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

Дальше указываем число в пунктах.

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

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

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

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

Сделать в Эксель в одном столбце ячейки разные по ширине, так, как и в строке разные по высоте, не получится. Для этого можно воспользоваться объединением ячеек в Excel. Подробнее об этом прочтите в статье.

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

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

Подогнать размер блока по тексту можно и по-другому. Для этого выделите нужные и в выпавшем списке Формат выберите автоподбор ширины и высоты.

В итоге, все слова в ячейках будет видно.

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

В следующем окне слева перейдите на «Дополнительно», прокрутите окно в низ и в поле «Единицы на линейке» выберите подходящую величину. Жмите «ОК».

Возвращаемся к листу Эксель, вверху открываем «Вид» и кликаем по кнопочке «Разметка страницы». После этого она будет выглядеть, как показано на скриншоте.

Перемещайте границы между названиями столбцов (А, В, С…), чтобы подобрать размер – он будет отображаться во всплывающем окошке. Для изменения строк все так же: перемещайте границы между их номерами (1, 2, 3, 4, 5…).

Данный способ не совсем точный. Например, у меня так и не получилось выбрать ширину ровно 3 см. Поэтому вот небольшая шпаргалка:
1 пункт – 1.33 пиксель – 0.35 мм
1 пиксель – 0.75 пунктов – 0.24мм
1 мм – 2.85 пунктов – 3.8 пикселя
Что касается сантиметров:
0,98 см – 37 пикселя
1,01 см – 38 пикселя
0,50 см – 19 пикселей

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

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

Поделитесь статьёй с друзьями:

17:04      

Людмила

Просмотров:   3778

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

Как быстро уменьшить и задать одинаковый размер ячеек в Excel

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

Пришлось просто выделить все, что находилось на странице бланка и вставить в другой файл excel. Все ничего, но все данные бланка вытянулись на исходный размер ячеек. Изначально размер ячеек равен — 8,43.

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

Способ 1.

  • Выделите необходимое количество ячеек.
  • Щелкните правой кнопкой мыши на любой ячейке с латинской буквой в самом верху колонки таблицы
  • Выберите в выпадающем меню пункт Ширина столбца…
  • В открывшемся окошке Ширина столбца установите свою цифру, и нажмите кнопку ОК.

Теперь размер ячеек в Excel будет тот, который указали вы.

Способ 2.

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

  • Щелкните левой кнопкой мыши по квадратику вверху слева на пересечении строк и колонок
  • У вас должен выделиться весь лист документа
  • Теперь подведите курсор мыши на пересечение любого столбца (там, где написаны латинские буквы). Когда курсор примет вид крестика, нажмите левую кнопку мыши, и не отпуская ее, уменьшите размер колонки до необходимого вам размера, и отпустите кнопку мыши.

Таким образом, у вас изменился размер ячеек во всем листе документа Excel.

Видео ролик Как быстро изменить размер ячеек в Excel:

С уважением, Людмила 

Понравилась статья — нажмите на кнопки:

Как расширить строки в таблице excel

Изменение ширины столбцов и высоты строк

​Смотрите также​​, чтобы восстановить удаленные​Строки таблицы выше​ или добавьте новые​ Excel. Если у​ кнопкой мыши по​ листа. Для этого​Находясь во вкладке​«Автоподбор высоты строки»​ величину в числовом​ отпускаем мышку. Как​ мыши и тянем​Ячейки​Значение ширины столбца по​ содержимым, дважды щелкните​ по умолчанию для​Размер ячейки​Примечание:​ данные. Вы также​или​ строки или столбцы​ вас есть классическое​

​ выделенному объекту. Появляется​ просто набираем на​«Главная»​или​ выражении.​ можно увидеть, изменилась​ курсор вверх (если​нажмите кнопку​ умолчанию указывает приблизительное​ нижнюю границу заголовка​ всех новых книг​выберите пункт​Мы стараемся как​

​ можете выделение повторяющихся​

​Строки таблицы ниже​

​ между существующими.​

​ приложение Excel, нажмите​

​ контекстное меню. В​

​ клавиатуре сочетание клавиш​

​, кликаем на ленте​

​«Автоподбор ширины столбца»​

​Для этого следует выделить​

​ величина не только​

​ следует сузить) или​

​Формат​

​ количество символов стандартного​​ строки.​

  • ​ и листов, можно​Ширина столбца​ можно оперативнее обеспечивать​​ значений, прежде чем​​.​​Ввод данных​​ кнопку​​ нем переходим по​​Ctrl+A​ по иконке​.​ столбец или строку,​ строки или столбца,​ вниз (если требуется​.​ шрифта, которое умещается​​К началу страницы​​ создать шаблон книги​​.​​ вас актуальными справочными​​ удалять их с​​В приведенном ниже примере​​   ​​Открыть в Excel​ пункту​.​​«Формат»​​После того, как был​​ в которой находится​​ с границами которых​ расширить).​В разделе​ в ячейке. Можно​

  • ​Если вы предпочитаете работать​ или листа шаблон​Введите значение в поле​ материалами на вашем​ помощью условного форматирования.​ новая строка будет​Чтобы добавить строку внизу​и используйте параметр​«Специальная вставка…»​После того, как вся​, которая размещена в​

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

  2. ​ с ширины столбцов​​ и затем основаны​​Ширина столб

Как расширить строку в таблице excel

Изменение ширины столбцов и высоты строк

​Смотрите также​​ добавлением строк на​Как видим, ячейки очищены,​ курсор на выбранную​ выше и ниже​ их можно увидеть​ новый столбец, введите​ Устанавливаем величину масштабирования​ значение в процентах,​ увеличить. Выделение производим​ строчки табличного диапазона.​ любой из выбранных​В разделе​ кнопкой​ строк на листе,​ОК​.​Примечание:​ листе. Для подтверждения​ и готовы к​ ячейку и, не​ конкретного диапазона. Затем,​

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

​ этого правила пронумеруем​

​ заполнению данными.​

​ отпуская левой клавиши,​

​ после клика правой​

​Решить поставленную задачу можно​

​ справа от таблицы​

​«OK»​

​ масштаб табличного диапазона​

​ как и при​

​ вариант расширения строк.​

​Задать вопрос на форуме​

​выберите пункт​

​и выберите команду​​Выделить все​

  • ​Советы:​Размер ячейки​ можно оперативнее обеспечивать​​ строки за пределами​​Нужно учесть, что данный​​ перетащить выделенную область​​ кнопкой мыши, появится​​ двумя способами:​​ и нажмите клавишу​внизу окна​ и листа в​ предыдущем варианте действий.​Выделяем на вертикальной панели​ сообщества, посвященного Excel​Высота строки​Специальная вставка​​, а затем перетащите​​ ​​выберите пункт​​ вас актуальными справочными​​ таблицы и добавим​​ способ подходит только​​ на новое место.​​ контекстное меню, в​С помощью контекстного меню.​ ВВОД.​​«Параметры страницы»​​ целом. Естественно, чтобы​​ Затем щелкаем по​​ координат сектора той​У вас есть предложения​.​.​

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

​ произвести увеличение вы​ выделению правой кнопкой​ строки или группы​
Установка заданной ширины столбца
  1. ​ по улучшению следующей​Введите значение в поле​В разделе​

  2. ​ любой строки.​​ по умолчанию для​​.​​ языке. Эта страница​​Чтобы в таблицу автоматически​​ если в таблице​​ можно в «Параметрах»​

Как в Экселе вставить строку или столбец

На рабочем листе Эксель располагается 1 048 576 строк и 16 384 столбца. Заполнить такое количество ячеек очень тяжело, да и не нужно. Часто рабочие листы содержат огромное количество информации. Чтобы правильно организовать такие данные, важно уметь правильно обращаться со строками и столбцами – добавлять, удалять, скрывать, изменять размеры. Конечно, вы можете настроить внешний вид рабочего листа, но это не всегда полезно. Этот пост обязателен к прочтению, если вы хотите выполнять действительно серьёзные задачи, ведь управление строками и столбцами вы будете применять каждый день.

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

Вставка новых строк и столбцов

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

Чтобы вставить строку на лист, воспользуйтесь одним из двух способов.

  1. Выделите любую ячейку в строке под местом вставки и выполните команду Главная – Ячейки – Вставить – Вставить строки на лист. Например, если нужно, вставить строку между 3 и 4 строками, установите курсор в любую клетку 4 строки
    Вставка строк на лист Excel
  2. Выделите всю строку, перед которой нужно сделать вставку (для этого кликните по номеру строки). Кликните правой кнопкой мыши в любом месте выделенной строки и в контекстном меню выберите Вставить
    Вставка строк через контекстное меню Эксель

Обе эти операции приведут к появлению новой пустой строки, при этом последняя строка листа будет удалена. Если последняя строка (№1 048 576) содержит данные – программа не сможет вставить новую строчку и выдаст сообщение об ошибке. Чтобы это исправить – удалите или перенесите информацию из последней строчки листа.

Ошибка при вставке новой строки в Excel

Вставка нового столбца в Эксель выполняется по тем же правилам:

  1. Установите курсор в любую клетку в столбце справа от места вставки и выполните: Главная – Ячейки – Вставить – Вставить столбцы на лист
  2. Выделите весь столбец, перед которым нужно вставить новый, кликните правой кнопкой мыши в любом месте выделения, в контекстном меню выберите команду Вставить.

При вставке нового столбца – последний (XFD) удаляется. Если в последнем столбце есть данные – вставка не будет выполнена, программа сообщит об ошибке.

Удаление строк и столбцов Excel

Я всегда советую удалять ненужную информацию. Если у вас на листе появились лишние строки и столбцы – вы можете их удалить. Могу предложить 2 равноценных способа:

При удалении, все последующие строки смещаются вверх, а столбцы – влево.

Как скрыть строку или столбец в Эксель

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

Мне известны 2 способа сокрытия:

  • Установите курсор в любую ячейку строки или столбца для скрытия. На ленте выберите:Главная – Ячейки – Формат – Скрыть или отобразить – Скрыть строки (или Ctrl+9), или Главная – Ячейки – Формат – Скрыть или отобразить – Скрыть столбцы (или Ctrl+0)
    Сокрытие ячеек
  • Выделите всю строку или столбец для сокрытия, кликните правой кнопкой мыши на выделенной области, в контекстном меню выберите Скрыть

Чтобы снова отобразить скрытые ячейки – нужно каким-либо способом их выделить. Например, если скрыта третья строка – выделите строки 2-4, тогда скрытые ячейки попадут в область выделения. Еще один вариант – перейти в скрытую ячейку. Для этого нажмите F5 (или Ctrl+G), откроется окно «Переход». В строке «Ссылка» введите координаты одной из скрытых ячеек (к примеру, A3) и нажмите Ок. Курсор будет переведен в скрытый столбец.

Когда выделена скрытая ячейка, выполните команду Главная – Ячейки – Формат – Скрыть или отобразить – Отобразить строки (или Отобразить столбцы, в зависимости от того, что скрыто).

Если выделена вся скрытая область – можно кликом правой кнопкой мыши вызвать контекстное меню и там выбрать Показать.

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

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

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

Изменение высоты строки

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

  • Перетащите нижнюю границу номера любой из выделенных строк
  • Воспользуйтесь командой Главная – Ячейки – Формат – Высота строки. В открывшемся диалоговом окне выберите нужную высоту (в пунктах)
  • Если нужно автоматически выбрать высоту строк в соответствии с содержимым ячеек, сделайте двойной клик по нижней границе любой из выделенных строк (на панели заголовков), или выполните Главная – Ячейки – Формат – Автоподбор высоты строки

Изменение ширины столбца

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

  • Перетащите правую границу имени любого из выделенных столбцов
  • Выполните команду Главная – Ячейки – Формат – Ширина столбца. В открывшемся окне укажите нужную ширину
  • Автоматически устанавливаем ширину столбцов двойным кликом по правой рамке имени столбца, либо командой Главная – Ячейки – Формат – Автоподбор ширины столбца.

Чтобы изменить ширину столбцов по умолчанию – воспользуйтесь командой Главная – Ячейки – Формат – Ширина по умолчанию. В диалоговом окне задайте нужное значение и нажмите Ок.

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

Готов ответить на ваши вопросы в комментариях, а в следующем посте мы научимся правильно выделять ячейки. До встречи!

Извлечь число из текстовой строки

В руководстве показано, как извлекать число из различных текстовых строк в Excel с помощью формул и инструмента «Извлечь».

Когда дело доходит до извлечения части текстовой строки заданной длины, Excel предоставляет три функции подстроки (Left, Right и Mid) для быстрого выполнения задачи. Когда дело доходит до извлечения числа из буквенно-цифровой строки, Microsoft Excel не предоставляет… ничего.

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

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

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

Формула 1. Получить число справа от строки

Чтобы извлечь число из строки «текстовое число», первое, что вам нужно знать, — это с чего начать извлечение. Итак, давайте определим положение первой цифры с помощью этой общей формулы:

МИН (ПОИСК ({0,1,2,3,4,5,6,7,8,9}, ячейка & «0123456789»))

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

.

= МИН (ПОИСК ({0,1,2,3,4,5,6,7,8,9}, A2 & "0123456789"))

Хотя формула содержит константу массива, это обычная формула, которая завершается обычным способом нажатием клавиши Enter.

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

= ВПРАВО (B2, ДЛИНА (A2) -B2 + 1)

Где A2 — исходная строка, а B2 — позиция первой цифры.

На следующем снимке экрана показаны результаты:

Чтобы удалить вспомогательный столбец, содержащий позицию первой цифры, вы можете встроить формулу MIN непосредственно в функцию RIGHT следующим образом:

= ВПРАВО (A2, LEN (A2) - MIN (ПОИСК ({0,1,2,3,4,5,6,7,8,9}, A2 & «0123456789»)) +1)

Чтобы формула возвращала число , а не числовую строку, вложите его в функцию VALUE:

= ЗНАЧЕНИЕ (ПРАВО (A2; LEN (A2) - MIN (ПОИСК ({0,1,2,3,4,5,6,7,8,9}, A2 & «0123456789»)) +1))

Как рассчитывается позиция 1-й цифры

Мы предоставляем константу массива {0,1,2,3,4,5,6,7,8,9} в аргументе find_text функции ПОИСК, чтобы формула выполняла поиск каждого элемента массива в исходная строка и возвращает их позиции.Поскольку константа массива содержит 10 цифр, результирующий массив также содержит 10 элементов.

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

Кроме того, мы используем специальную конструкцию (A2 & «0123456789») для объединения каждого возможного числа с исходной строкой. В ситуациях, когда определенное число в константе массива не найдено в исходной строке, эта конструкция действует как ЕСЛИОШИБКА, заставляя формулу возвращать «фальшивую» позицию, равную длине строки +1 или более символов.В результате, если исходная строка не содержит числа, как в строке 7 на скриншоте выше, формула ПРАВА возвращает пустую строку.

Чтобы упростить понимание, давайте посмотрим, как формула работает для определенной ячейки, скажем, A2. Эта ячейка содержит текстовую строку «ECDAA-05», для которой функция ПОИСК возвращает следующий массив {7,10,11,12,13,8,15,16,17,18}. Вот как:

  • 0 — это 1 st элемент константы массива и 7 -й символ в исходной строке, поэтому первый элемент результирующего массива равен «7».
  • 5 — это 6 -й элемент константы массива и 8 -й символ в исходной строке, поэтому шестой элемент результирующего массива равен «8».
  • В ячейке A2 не найден другой элемент константы массива, и поэтому остальные 8 элементов результирующего массива представляют позиции соответствующих цифр в объединенной строке (ECDAA-050123456789).

Поскольку 7 — наименьшее значение в результирующем массиве, функция MIN возвращает его, поэтому мы получаем позицию первой цифры (0) в исходной строке.

Формула 2. Вытяните число справа от строки

Другой способ извлечь число из конца строки — использовать эту общую формулу:

ПРАВА ( ячейка , СУММ (LEN ( ячейка )) — LEN (ПОДСТАВИТЬ ( ячейка , {«0», «1», «2», «3», «4», «5», » 6 «,» 7 «,» 8 «,» 9 «},» «))))

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

= ВПРАВО (A2, СУММ (LEN (A2) — LEN (ПОДСТАВИТЬ (A2, {«0», «1», «2», «3», «4», «5», «6», » 7 «,» 8 «,» 9 «},» «))))

Как работает эта формула

По сути, формула выполняет поиск всех чисел от 0 до 9 в исходной строке, подсчитывает найденные числа и возвращает такое количество символов из конца строки.

А вот подробная формула:

  • Сначала вы используете функции LEN и SUBSTITUTE, чтобы узнать, сколько раз данное число встречается в строке. Для этого вы заменяете число пустой строкой («»), а затем вычитаете длину строки без этого числа из общей длины исходной строки. Эта операция выполняется для каждого числа в константе массива.
  • Затем функция СУММ складывает все вхождения всех цифр в строке.
  • Наконец, счетчик цифр переходит в аргумент num_chars функции RIGHT, предписывающий ему вернуть такое количество символов из правой части строки.

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

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

ЛЕВО ( ячейка , СУММ (LEN ( ячейка ) -LEN (ПОДСТАВИТЬ ( ячейка , {«0», «1», «2», «3», «4», «5», » 6 «,» 7 «,» 8 «,» 9 «},» «))))

Используя исходную строку в A2, используйте следующую формулу для получения числа:

= ЛЕВЫЙ (A2, СУММ (LEN (A2) -LEN (ПОДСТАВИТЬ (A2, {«0», «1», «2», «3», «4», «5», «6», » 7 «,» 8 «,» 9 «},» «))))

Примечание. ROW (INDIRECT («1:» & LEN (A2))) / 10)

Где A2 — исходная текстовая строка.

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

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

= ЕСЛИ (СУММ (LEN (A2) -LEN (ПОДСТАВИТЬ (A2, {«0», «1», «2», «3», «4», «5», «6», «7» , "8", "9"}, "")))> 0, СУММПРОИЗВ (MID (0 & A2, LARGE (ИНДЕКС (ISNUMBER (- MID (A2, ROW (INDIRECT ("$ 1: $" & LEN (A2)) ), 1)) * ROW (INDIRECT ("$ 1: $" & LEN (A2))), 0), ROW (INDIRECT ("$ 1: $" & LEN (A2)))) + 1,1) * 10 ^ ROW (КОСВЕННО ("$ 1: $" & LEN (A2))) / 10), "")

Как показано на скриншоте ниже, улучшенная формула прекрасно работает (спасибо Алексею, нашему гуру Excel, за это улучшение):

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

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

С нашим Ultimate Suite, добавленным на вашу ленту Excel, вы можете быстро получить номер из любой буквенно-цифровой строки:

  1. Перейдите на вкладку Ablebits Data > группу Text и щелкните Extract :
  2. Выделите все ячейки с исходными строками.
  3. На панели инструмента «Извлечь» установите переключатель « Извлечь номера ».
  4. В зависимости от того, хотите ли вы, чтобы результаты были формулами или значениями, выберите Вставить как формулу или оставьте его невыделенным (по умолчанию).

    Я советую установить этот флажок, если вы хотите, чтобы извлеченные числа обновлялись автоматически, как только в исходные строки вносятся какие-либо изменения. Если вы хотите, чтобы результаты не зависели от исходных строк (например, если вы планируете удалить исходные данные позже), не устанавливайте это поле.

  5. Нажмите кнопку Вставить результаты . Готово!

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

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

Если установлен флажок Вставить как формулу , вы увидите формулу в строке формул.Интересно узнать, какой именно? Просто скачайте пробную версию Ultimate Suite и убедитесь сами 🙂

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

Получите промокод для Ultimate Suite — эксклюзивное предложение для читателей нашего блога!

Доступные загрузки

Excel Extract Number — образец книги (файл .xlsx)
Ultimate Suite — пробная версия (файл .zip)

Вас также может заинтересовать

Извлечение чисел из строки в Excel (с использованием формул или VBA)

Посмотреть видео — Как извлечь числа из текстовой строки в Excel (с помощью формулы и VBA)

В Excel нет встроенной функции для извлечь числа из строки в ячейке (или наоборот — удалить числовую часть и извлечь текстовую часть из буквенно-цифровой строки).

Однако это можно сделать с помощью коктейля из функций Excel или простого кода VBA.

Позвольте мне сначала показать вам, о чем я говорю.

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

Выбранный вами метод также будет зависеть от используемой версии Excel:

  • Для версий, предшествующих Excel 2016, вам необходимо использовать немного более длинные формулы.
  • Для Excel 2016 вы можете использовать недавно представленную функцию TEXTJOIN.
  • Метод VBA может использоваться во всех версиях Excel

Нажмите здесь, чтобы загрузить файл примера

Извлечь числа из строки в Excel (формула для Excel 2016)

Эта формула будет работать только в Excel 2016, поскольку в ней используется недавно представленная функция TEXTJOIN.

Кроме того, эта формула может извлекать числа в начале, конце или середине текстовой строки.

Обратите внимание, что формула TEXTJOIN, описанная в этом разделе, даст вам все числовые символы вместе. Например, если текст «Цена 10 билетов составляет 200 долларов США», в результате вы получите 10200.

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

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

 = TEXTJOIN ("", TRUE, IFERROR ((MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1) * 1), "")) 

Это массив формула, поэтому вам нужно использовать « Control + Shift + Enter » вместо Enter.

Если в текстовой строке нет чисел, эта формула вернет пустую строку (пустую строку).

Как работает эта формула?

Позвольте мне сломать эту формулу и попытаться объяснить, как она работает:

  • ROW (INDIRECT («1:» & LEN (A2))) — эта часть формулы дает серию чисел, начиная с единицы.Функция LEN в формуле возвращает общее количество символов в строке. В случае «Стоимость 100 долларов США» будет возвращено 19. Формулы, таким образом, станут СТРОКА (КОСВЕННАЯ («1:19»). Затем функция СТРОКА вернет серию чисел — {1; 2; 3 ; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}
  • (MID (A2, ROW (INDIRECT («1:» & LEN ( A2))), 1) * 1) — Эта часть формулы будет возвращать массив ошибок #VALUE! Или чисел на основе строки. Все текстовые символы в строке становятся ошибками #VALUE!, А все числовые значения остаются такими же. -является.Это происходит, когда мы умножили функцию MID на 1.
  • ЕСЛИОШИБКА ((MID (A2, ROW (INDIRECT («1:» & LEN (A2))), 1) * 1), »») — Когда функция ЕСЛИОШИБКА использованный, он удалит все #VALUE! ошибки и останутся только цифры. Вывод этой части будет выглядеть так — {«»; »»; «»; »»; »»; »;» »;»; »»; »»; »;» »;» »; ””; ””; ””; 1; 0; 0}
  • = TEXTJOIN («», ИСТИНА, ЕСЛИОШИБКА ((MID (A2, ROW (INDIRECT («1:» & LEN (A2))), 1) * 1), ””)) — функция TEXTJOIN теперь просто объединяет оставшиеся строковые символы (которые являются только числами) и игнорирует пустую строку.

Pro Совет: Если вы хотите проверить вывод части формулы, выберите ячейку, нажмите F2, чтобы перейти в режим редактирования, выберите часть формулы, для которой вы хотите получить результат, и нажмите F9 . Вы сразу увидите результат. А затем не забудьте нажать Control + Z или нажать клавишу Escape. НЕ нажимайте клавишу ввода.

Загрузите файл примера

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

 = TEXTJOIN ("", TRUE, IF (ISERROR (MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1) * 1), MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1), "")) 

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

Затем TEXTJOIN используется для объединения всех текстовых символов.

Внимание : Хотя эта формула отлично работает, она использует изменчивую функцию (функция ДВССЫЛКА).Это означает, что в случае, если вы используете это с огромным набором данных, может потребоваться некоторое время, чтобы получить результаты. Лучше всего создать резервную копию, прежде чем использовать эту формулу в Excel.

Извлечь числа из строки в Excel (для Excel 2013/2010/2007)

Если у вас есть Excel 2013. 2010. или 2007, вы не можете использовать формулу TEXTJOIN, поэтому вам придется использовать сложную формулу, чтобы получить это сделанный.

Предположим, у вас есть набор данных, показанный ниже, и вы хотите извлечь все числа в строке в каждой ячейке.ROW (INDIRECT («$ 1: $» & LEN (A2))) / 10), «»)

Если в текстовой строке нет числа, эта формула вернет пустую (пустую строку).

Хотя это формула массива, не нужно использовать , чтобы использовать «Control-Shift-Enter». Для этой формулы работает простой ввод.

Эта формула принадлежит замечательному форуму Mr. Excel.

Опять же, эта формула извлечет все числа в строке независимо от позиции. Например, если текст «Цена 10 билетов составляет 200 долларов США», в результате вы получите 10200.

Внимание : Хотя эта формула отлично работает, она использует изменчивую функцию (функция ДВССЫЛКА). Это означает, что в случае, если вы используете это с огромным набором данных, может потребоваться некоторое время, чтобы получить результаты. Лучше всего создать резервную копию, прежде чем использовать эту формулу в Excel.

Разделите текст и числа в Excel с помощью VBA

Если вам часто приходится разделять текст и числа (или извлекать числа из текста), вы также можете использовать метод VBA.

Все, что вам нужно сделать, это использовать простой код VBA для создания настраиваемой пользовательской функции (UDF) в Excel, а затем вместо использования длинных и сложных формул использовать эту формулу VBA.

Позвольте мне показать вам, как создать две формулы в VBA — одну для извлечения чисел и одну для извлечения текста из строки.

Извлечение чисел из строки в Excel (с использованием VBA)

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

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

 Функция GetNumeric (CellRef As String)
Dim StringLength As Integer
StringLength = Len (CellRef)
Для i = 1 до StringLength
Если IsNumeric (Mid (CellRef, i, 1)), то Result = Result & Mid (CellRef, i, 1)
Далее я
GetNumeric = Результат
Конечная функция 

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

Теперь вы можете использовать функцию GetText на рабочем листе.Поскольку мы проделали всю тяжелую работу в самом коде, все, что вам нужно сделать, это использовать формулу = GetNumeric (A2).

Это сразу даст вам только числовую часть строки.

Обратите внимание, что, поскольку теперь в книге есть код VBA, вам необходимо сохранить его с расширением .xls или .xlsm.

Загрузите файл примера

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

Извлечь текст из строки в Excel (с использованием VBA)

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

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

 Функция GetText (CellRef As String)
Dim StringLength As Integer
StringLength = Len (CellRef)
Для i = 1 до StringLength
Если Not (IsNumeric (Mid (CellRef, i, 1))), то Result = Result & Mid (CellRef, i, 1)
Далее я
GetText = Результат
Конечная функция 

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

Теперь вы сможете использовать функцию GetNumeric на рабочем листе.Поскольку мы проделали всю тяжелую работу в самом коде, все, что вам нужно сделать, это использовать формулу = GetText (A2).

Это сразу даст вам только числовую часть строки.

Обратите внимание, что, поскольку теперь в книге есть код VBA, вам необходимо сохранить его с расширением .xls или .xlsm.

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

Если вы используете Excel 2013 или предыдущие версии и не имеете

, вам также могут понравиться следующие руководства по Excel:

Как извлечь подстроку в Excel (с использованием текстовых формул)

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

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

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

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

ТЕКСТОВЫЕ ФУНКЦИИ Excel

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

  • ПРАВАЯ функция: извлекает указанное количество символов справа от текстовой строки.
  • LEFT функция: извлекает указанное количество символов слева от текстовой строки.
  • Функция MID: извлекает указанное количество символов из указанной начальной позиции в текстовой строке.
  • Функция НАЙТИ: Находит начальную позицию указанного текста в текстовой строке.
  • Функция LEN: возвращает количество символов в текстовой строке.

Извлечение подстроки в Excel с использованием функций

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

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

Пример 1. Извлечение имен пользователей из идентификаторов электронной почты

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

Вот формула для получения имени пользователя:

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

В приведенной выше формуле используется функция LEFT для извлечения имени пользователя путем определения положения знака @ в id.Это делается с помощью функции НАЙТИ, которая возвращает позицию символа @.

Например, в случае [email protected] FIND («@», A2) вернет 11, что является его позицией в текстовой строке.

Теперь мы используем функцию LEFT для извлечения 10 символов слева от строки (на один меньше значения, возвращаемого функцией LEFT).

Пример 2 — Извлечение имени домена из идентификаторов электронной почты

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

Вот формула, которая сделает это:

 = RIGHT (A2, LEN (A2) -FIND ("@", A2)) 

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

Давайте снова возьмем пример с [email protected] Функция НАЙТИ возвращает положение знака @, которое в данном случае равно 11. Теперь нам нужно извлечь все символы после @.Таким образом, мы определяем общую длину строки и вычитаем количество символов до символа @. Он дает нам количество символов, которые покрывают доменное имя справа.

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

Пример 3 — Извлечение доменного имени из идентификаторов электронной почты (без .com)

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

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

Вот формула, которая будет извлекать только имя домена:

 = MID (A2, FIND ("@", A2) + 1, FIND (".", A2) -FIND ("@", A2) - 1) 

Функция Excel MID извлекает указанное количество символов из указанной начальной позиции. В приведенном выше примере FIND («@», A2) +1 указывает начальную позицию (которая находится сразу после @), а FIND («.», A2) -FIND (« @ », A2) -1 определяет количество символов между« @ »и« .

Обновление: Один из читателей William19 упомянул, что приведенная выше формула не будет работать, если в идентификаторе электронной почты есть точка (.) (Например, [email protected]). Вот формула для таких случаев:

 = MID (A1, FIND ("@", A1) + 1, FIND (".", A1, FIND ("@", A1)) - FIND (" @ ", A1) -1) 

Использование текста в столбцы для извлечения подстроки в Excel

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

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

Вот как это сделать:

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

Если вы хотите еще больше разделить текст (например, разделить batman.com на batman и com), повторите с ним тот же процесс.

Использование НАЙТИ и ЗАМЕНИТЬ для извлечения текста из ячейки в Excel

НАЙТИ и ЗАМЕНИТЬ может быть мощным методом при работе с текстом в Excel. В приведенных ниже примерах вы узнаете, как использовать НАЙТИ и ЗАМЕНИТЬ с подстановочными знаками, чтобы делать удивительные вещи в Excel.

   См. Также:   Узнайте все о подстановочных знаках в Excel. 

Возьмем те же примеры идентификаторов электронной почты.

Пример 1. Извлечение имен пользователей из идентификаторов электронной почты

Ниже приведены шаги по извлечению имен пользователей из идентификаторов электронной почты с помощью функции «Найти и заменить»:

  • Скопируйте и вставьте исходные данные.Поскольку функция «Найти и заменить» работает и изменяет данные, к которым она применяется, лучше всего иметь резервную копию исходных данных.
  • Выберите данные и перейдите в раздел «Главная» -> «Редактирование» -> «Найти и выделить» -> «Заменить» (или используйте сочетание клавиш Ctrl + H).
  • В диалоговом окне «Найти и заменить» введите следующее:
    • Найдите что: @ *
    • Заменить на: (оставьте это поле пустым)
  • Нажмите «Заменить все».

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

Как это работает ?? — В приведенном выше примере мы использовали комбинацию @ и *. Звездочка (*) — это подстановочный знак, обозначающий любое количество символов. Следовательно, @ * будет означать текстовую строку, которая начинается с @ и может иметь любое количество символов после нее. Например, в [email protected] @ * будет @ batman.com. Когда мы заменяем @ * пробелом, удаляются все символы после @ (включая @).

Пример 2 — Извлечение имени домена из идентификаторов электронной почты

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

Вот шаги:

  • Выберите данные.
  • Перейдите на главную -> Редактирование -> Найти и выделить -> Заменить (или используйте сочетание клавиш Ctrl + H).
  • В диалоговом окне «Найти и заменить» введите следующее:
    • Найдите что: * @
    • Заменить на: (оставьте это поле пустым)
  • Нажмите «Заменить все».

Это мгновенно удалит весь текст перед @ в идентификаторах электронной почты. У вас будет результат, как показано ниже:

Вам также могут понравиться следующие руководства по Excel:

Использование Pandas в Excel | PyXLL

Pandas DataFrames и Series могут использоваться в качестве аргументов функций и типов возвращаемых значений для Excel функции рабочего листа с использованием декоратора xl_func .

При использовании в качестве аргумента диапазон, указанный в Excel, будет преобразован в Pandas DataFrame или Series как указывается сигнатурой функции.

При возврате DataFrame или Series диапазон данных будет возвращен в Excel. PyXLL может автоматически изменять размер диапазона формулы массива в соответствии с возвращаемыми данными, установив auto_resize = Истина в xl_func .

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

 из импорта pyxll xl_func
импортировать панд как pd
импортировать numpy как np

@xl_func ("строки int, столбцы int: dataframe ", auto_resize = True)
def random_dataframe (строки, столбцы):
    данные = np.random.rand (строки, столбцы)
    column_names = [chr (ord ('A') + x) для x в диапазоне (столбцы)]
    вернуть pd.DataFrame (данные, столбцы = имена_столбцов)
 

Функция также может принимать DataFrame или Series в качестве одного из аргументов. При передаче DataFrame или Series для функции вся область данных должна быть выбрана в Excel и использоваться в качестве аргумента функции.

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

 из импорта pyxll xl_func

@xl_func ("dataframe , str: float")
def sum_column (df, col_name):
    col = df [col_name]
    вернуть col.sum ()
 

См. Также Форматирование кадра данных Pandas.

Параметры типов панд

Для типов аргументов и возвращаемых данных dataframe серий и доступны следующие параметры:

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

    кадр данных

    index

    Количество столбцов для использования в качестве индекса DataFrame.Указание более одного приведет к DataFrame где индекс — это MultiIndex.

    столбец

    Количество строк, используемых в качестве столбцов DataFrame. Указание более одного приведет к DataFrame где столбцы — это MultiIndex. Если используется вместе с индексом , тогда любые заголовки столбцов в индексе столбцы будут использоваться для наименования индекса.

    dtype

    Тип данных для значений в кадре данных.Не может быть установлен с dtypes .

    dtypes

    Словарь имени столбца -> тип данных для значений в фрейме данных. Не может быть установлен с dtype .

    index_dtype

    Тип данных для значений в индексе фрейма данных.

  • dataframe , при использовании в качестве типа возврата

    кадр данных

    index

    Если True, включить индекс при возврате в Excel, если False — нет.Если Нет, включать только в том случае, если указатель назван.

    столбцы

    Если True, включить заголовки столбцов, если False — нет.

  • серия , при использовании в качестве типа аргумента

    серия

    index

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

    транспонировать

    Установите значение True, если серия расположена горизонтально, или False, если вертикально. По умолчанию ориентация определяется структурой данных.

    dtype

    Тип данных для значений в серии.

    index_dtype

    Тип данных для значений в индексе серии.

  • серия , при использовании в качестве типа возврата

    series

    index

    Если True, включить индекс при возврате в Excel, если False — нет.

    транспонировать

    Установите значение True, если серия должна быть расположена горизонтально, или False, если вертикально.

Передача как объекты Python вместо массивов Excel

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

Следующий код возвращает случайный DataFrame как объект Python, поэтому он будет отображаться в Excel как один ячейка с дескриптором этого объекта:

 из импорта pyxll xl_func
импортировать панд как pd
импортировать numpy как np

@xl_func ("целые строки, целые столбцы: объект")
def random_dataframe (строки, столбцы):
    data = np.random.rand (строки, столбцы)
    column_names = [chr (ord ('A') + x) для x в диапазоне (столбцы)]
    вернуть pd.DataFrame (данные, столбцы = имена_столбцов)
 

Результат такой функции может быть передан другой функции, которая ожидает DataFrame:

 @xl_func ("dataframe, int: dataframe ", auto_resize = True)
def dataframe_head (df, num_rows):
    вернуть df.head (num_rows)
 

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

Использование преобразователей типов Pandas вне UDF

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

В этих случаях можно использовать функцию get_type_converter . Например:

 из импорта pyxll get_type_converter

to_dataframe = get_type_converter ("var", "dataframe ")
df = to_dataframe (данные)
 

Или наоборот:

 to_array = get_type_converter ("фрейм данных", "переменная")
данные = to_array (df)
 

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

Как объединить текст в Excel с помощью функции TEXTJOIN

Объяснение синтаксиса TEXTJOIN:

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

Отдельные входные данные, которые мы вызываем аргументами или параметрами, обычно состоят из «ИСТИНА» или «ЛОЖЬ», строки текста, значения или ссылки на одну ячейку или диапазон ячеек.

Вот синтаксис функции TEXTJOIN:

‘TEXTJOIN (delimiter, ignore_empty, text1, [text2], …)’

Первым аргументом функции является обязательный ‘delimiter’ .Все, что вы здесь напечатаете, будет помещено между ячейками, к которым вы хотите присоединиться. Если вы введете запятую в качестве «разделителя», функция просто поставит запятую между всем содержимым, к которому вы хотите присоединиться.

Этот аргумент «разделитель» должен быть введен как текстовая строка, которая может быть любым вариантом символов (включая числа, которые будут обрабатываться как текст) или ссылкой на действительную текстовую строку в двойных кавычках. Этот аргумент также может быть пробелом («»), который объединит наш текст в одну непрерывную строку.

Второй аргумент — это обязательный «ignore_empty» , который, когда установлен в «ИСТИНА», игнорирует пустые ячейки в выбранном диапазоне. Это может быть важным моментом при использовании пробела («») в качестве разделителя, если вы не хотите лишних пробелов между соединяемым текстом из-за пустых ячеек в выбранном диапазоне.

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *