Excel. Как заставить прибавлять в одну ячейку определенные значения по условию?

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

Categories: Excel/ Работа с текстом

Предположим, у вас есть большой список ФИО. Необходимо разделить текст в Excel по столбцам, чтобы имя, отчество и фамилия были в разных ячейках. Как разделить текст в Excel по столбцам?

Итак, существует два способа разделить текст по столбцам.

1-й способ разделить текст по столбцам. Мастер текста

Выделите столбец с текстом который хотите разделить. Затем выберите в верхней панели Данные — Текст по столбцам. Появится окно Мастера текста (разбор).

Если хотите разделить текст по пробелам или другим символам выбирайте «с разделителями».

В открывшемся меню выбираем пробел или нужный символ.

Выбираем формат текста и нажимаете «готово». Текст разделен!

Если все данные имеют одинаковую длину, можно разделить по фиксированной ширине (см. картинку 2)

2-й способ. Разделить текст в Excel формулами. Функция ПСТР

Если необходимо разделить текст по столбцам с использованием формулы, воспользуйтесь функциями =ПСТР() и =НАЙТИ().

Для того чтобы найти Фамилию в ячейку В2 занесите формулу

=ПСТР(A2;1;НАЙТИ(" ";A2;1)-1) 

Где функция Найти будет находить кол-во символов до пробела, а функция =ПСТР() возвращать это кол-во символов.

Чтобы получить Имя формула усложняется (необходимо дать функциям знать, где начинается имя т.е. заканчивается 1-й пробел), поэтому введите в ячейку C3

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

Чтобы найти Отчество в ячейкуD3 занесите формулу

=ПСТР(A2;I2+1;50) получаем =ПСТР(A2;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)+1;50)

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

Пример. Текст по столбцам

Как сцепить ячейки Имя, Отчество, Фамилия

Для того чтобы сцепить данные в ячейках воспользуйтесь функцией = СЦЕПИТЬ () или структурой с использованием & (амперсант) — символа сцепки, например, внесите в ячейку D3 формулу

=B2&" "&C2

Пример. Текст по столбцам

Очень часто с этим вопросом, задают вопрос «Как столбцы сделать строками?»

Поделитесь нашей статьей в ваших соцсетях:

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

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

Рабочая книга Excel

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

Изображение № 1. Основные обозначения.

Объединение ячеек в Эксель

Форматирование объединенных областей в Excel:

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

Изображение № 2. Объединение ячеек.

1. Поиск нужной кнопки. В меню навигации, на вкладке «Главная», отыскать клавишу, помеченную на скриншоте № 2. и вывести выпадающий список. Выбрать компонент «Объединить и поместить в центре». Если соответствующая клавиша недоступна, то пользователям необходимо закрыть режим редактирования клавишей ВВОД (Enter).
2. Замечания. Для изменения положения текста в объеденном блоке используются свойства выравнивания, на вкладке «Главная».

Разделение ячеек Excel

1. Выбор объединенного блока (другие разделить не получится).
2. При выделении загорится клавиша объединения (помечена на скриншоте № 2), после нажатия на неё ячейки высвободятся и получат отдельный физический адрес. Столбцы и ячейки пересчитаются в автоматическом режиме.

Поиск ячейки Excel

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

Следуйте инструкции:

1. Вкладка «Главная», область «Редактирование» (в самом правом углу). Клавиша «Найти и выделить».
2. При нажатии на клавишу «Найти» раскроется окно ввода информации. Но перед этим немного настроек – «Параметры», затем «Формат», переход к группе «Выравнивание» и установка галки напротив поиска объединенных ячеек.
3. Информация выведется в специальном окошке.
4. Замечание: Если область «Формат» скрыта, значит, не нажаты «Параметры». Для поиска всех объединенных ячеек применяется функций «Найти все». Подробнее на скриншоте № 3.

Изображение № 3. Поиск объединенных ячеек

Содержимое ячеек Эксель

Краткий курс работы с вводимой информацией. Копирование, перемещение и размножение. Обо всем по порядку.

1. Ввод. Выделение интересующей области. Внесение данных.
2. Удаление. Стандартный вариант – применение клавиш Delete и Backspace. Способ для опытных пользователей – клавиша ластика в панели «Редактирование», там, где расположена функция «Найти».
3. Копирование. Лучший способ работать с данными, вооружиться «горячими клавишами». Выделенные блоки легко размножать, сначала зажав сочетание CTRL+C (копирование), а затем CTRL + V (вставка). Метод быстрый, работает практически во всех текстовых редакторах и браузерах. При неправильных действиях можно воспользоваться CTRL + Z, вернувшись на шаг назад.
4. Вырезание. И снова выделение, затем CTRL + X, а после CTRL + V (уже знакомая вставка). Безотказная комбинация.
5. Формат. Выделенные ячейки легко изменять и внешне, и внутренне. Целый список возможных команд открывается при нажатии правой кнопкой мыши.

Арифметические операции в Excel

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

• «+» – сложение.
• «-» – вычитание.
• «*» – умножение.
• «/» – деление.
• «^» – возведение числа в степень.
• «%» – процент.

Каждый знак вводится в формулах, начинающихся с равно.

Пример: =7+6

После нажатия клавиши ВВОД (Enter), данные подсчитываются, и выводится результат. Если число после проведения арифметической операции получилось не целым, а с громадным количеством цифр после запятой, то можно воспользоваться настройкой и уменьшить разрядность. Для этого достаточно на «Главной» в разделе «Число» найти указанную кнопку.

Формулы Excel

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

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

Для получения «Итого», к примеру, достаточно сложить данные по каждому наименованию за первую и вторую неделю. Вариант несложный, можно переписать несколько значений вручную. А как быть с полем «Сумма»? Тоже все руками? Как же систематизировать данные?

Изображение № 4. Работа с формулами

Ошибки в Excel

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

#Н/Д – данные не определены, нарушена последовательность ввода аргументов данных;

#ССЫЛКА! – адрес столбца или строки неверен;

#ПУСТО! – неправильное построение арифметической операции;

#ЧИСЛО! – чересчур маленькое или большое для вывода число;

#ЗНАЧ! – неподдерживаемый тип данных, если в одной ячейке текст, а в другой цифры;

#ДЕЛ/0! – недопустимая попытка деления на ноль;

#ИМЯ?  – нераспознаваемый адрес или название функции;

Горячие клавиши Excel

CTRL + SHIFT + «+» – вставка текущего времени;

CTRL + «;» – указание текущей даты. Автоматически форматируется по правилам Excel;

CTRL + A – выделение документа целиком или текущей заполненной области.

И уже изученные – CTRL + C (копировать); CTRL + V (вставить); CTRL + Z (вернуться на шаг назад).

По окончании урока вы сможете:

1. Дать определение, что такое «диапазон ячеек»
2. Перечислить способы выделения смежных ячеек
3. Выделить несмежные ячейки
4. Перемещать ячейки по листу
5. Копировать ячейки по листу
6. Удалять данные
7. Отменять и повторять действия, используя клавиатуру

Скачайте файл тут или используйте собственную таблицу

1. Диапазон ячеек

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

Понять и запомнить!

Диапазон – это несколько выделенных ячеек Несколько выделенных ячеек, стоящих рядом, – называются «смежные ячейки». Несколько выделенных ячеек, стоящих отдельно друг от друга, – называются «несмежные ячейки»

Диапазон может содержать:

1. две ячейки,
2. всю строку
3. весь столбец
4. несколько ячеек на разных строчках и столбцах
5. лист

Понять и запомнить!

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

2. Способы выделения смежных ячеек

1 способ (мышка):

1. Щелкните ЛМ на первой ячейке выделяемого диапазона.
2. Не отпуская ЛМ протяните до последней ячейки выделяемого диапазона.
3. Отпустите левую клавишу мыши.

2 способ (клавиатура и мышка):

1. Щёлкните ЛМ на первой выделяемой ячейке.
2. Нажмите клавишу Shift и не отпускайте ее.
3. Щёлкните мышкой на последней ячейке выделяемого диапазона.
4. Отпустите клавишу Shift,

3 способ (клавиатура и мышка):

1. Щёлкните ЛМ на первой выделяемой ячейке.
2. Нажмите клавишу Shift и не отпускайте ее.
3. Воспользовавшись клавишами перемещения курсором «→», «↓», «←», «↑», постепенно выделите весть диапазон ячеек.
4. Отпустите клавишу Shift.

4 способ (клавиатура и мышка) – мой любимый:

1. Щёлкните ЛМ на первой выделяемой ячейке.
2. Нажмите сочетание клавиш Shift + Ctrl и не отпускайте их.
3. Воспользовавшись клавишами перемещения курсора «→», «↓», «←», «↑», выделите весть диапазон ячеек.

и

1. Отпустите клавишу Shift.

Запомните эту подсказку:

Shift + Ctrl + ↓	Выделение всех данных в столбце, начиная от текущей ячейки
Shift + Ctrl + →	Выделение всех данных в строчке, начиная от текущей ячейки

Замечание

Я ещё не встречала человека, который бы использовал этот замечательный способ выделения диапазона ячеек. Обычно выделяют перетаскиванием мыши. Хорошо, если таблица маленькая. А если на пару сотен строк?

5 способ (Меню ленты Главная):

1. Щёлкните ЛМ на первой выделяемой ячейке.
2. Лента Главная → группа команд Редактирование → команда Найти и выделить → команда Выделить группу ячеек).

1. Команда «текущую область»

Результат:

Выделение строки – на заголовке строки (курсор в виде черной жирной стрелки находится на имени строки).

Выделение столбца – на заголовке столбца (курсор в виде черной жирной стрелки находится на имени столбца).

Выделение ячеек всего листа – на кнопке, находящейся на пересечении заголовков столбцов и строк.

3. Выделение несмежных ячеек

1. Щелкните ЛМ на первой выделяемой ячейке.
2. Нажмите клавишу Ctrl и не отпускайте ее.
3. Щелкайте мышкой на тех ячейках, которые нужно выделить.
4. Отпустите клавишу Ctrl.

4. Перемещение данных

Шаг 1. Выделить данные, которые перемещаются

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

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

5. Копирование данных

Шаг 1. Выделить ячейки, которые копируются

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

Шаг 3. Нажать на клавиатуре клавишу Ctrl и не отпускать ее

Шаг 4. Нажать ЛМ мыши и, не отпуская ее, переместить выделенный текст в новое место.

Шаг 5. Отпустить клавишу Ctrl.

Обратите внимание!

Рядом с курсором «стрелка» появился знак «+». Это означает операцию «Копирование»

6. Удаление данных

Чтобы удалить данные из ячейки (ячеек) надо выделить ячейку (ячейки) и нажать клавишу или Backspace:

7. Использование команд «Повторить» и «Отменить».

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

Но мне больше нравятся использовать сочетание клавиш.

Отменить:

Повторить:

Понять и запомнить!

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

Теперь вы сможете:

1. Дать определение, что такое «диапазон ячеек»
2. Перечислить способы выделения смежных ячеек
3. Выделить несмежные ячейки
4. Перемещать ячейки по листу
5. Копировать ячейки по листу
6. Удалять данные
7. Отменять и повторять действия, используя клавиатуру

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

Можно ли разбивать ячейки в Microsoft Excel?

Самая маленькая единица измерения в Экселе — ячейка. Она обладает условным номером, определяющим её отношение к строчке и столбцу, к примеру, D14 или A2.

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

Объединение

Чтобы разделить ячейку на две в Excel, проще всего объединить иные ячейки:

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

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

Как делить объединённые ячейки?

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

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

  Что делать, если лист или книга в Excel защищены паролем – как снять защиту

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

Разделить ячейки по диагонали

Формат ячеек

Разделить ячейки по диагонали с помощью формата ячеек можно следующим образом:

1. Кликните правой клавишей мышки по желаемому элементу и в выпавшем меню выбирайте «Формат ячеек…». Это можно сделать также с помощью одновременного нажатия «Ctrl+1».
2. В новом окошке переходите в раздел «Граница».
3. Рядом с размещённым посередине окном со словом «Надпись» внутри кликните на 1-ю из 2-х кнопок с изображением косой линии, и выберите подходящую. Здесь же возможно установить требующийся оттенок и вариант оформления линии. Затем нажмите «OK».
4. Элемент будет разделён диагонально косой чёрточкой. Но опять же, учитывайте, что Excel считает поделённую подобным методом ячейку как единую.

Вставка линии

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

1. Нужно перейти во вкладку «Вставка», затем в разделе инструментов «Иллюстрации» кликнуть по опции «Фигуры».
2. В выпавшем перечне в наборе «Линии» кликнуть по первой фигуре.
3. Осталось провести черту от одного угла ячейки к другому в нужном направлении.

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

Читайте также:

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

Объединить ячейки в Excel

Для начала откройте документ Excel. Дважды щелкните документ Excel, чтобы открыть его.

Выберите две или более соседних ячеек, которые вы хотите объединить. Например, если вы хотите объединить ячейки с A1 по С1, для этого нужно выделить ячейки с А1 по С1. Перейти во вкладку «Главная» где нужно будет найти кнопку «Объединить и поместить в центе». 

Клетки слиянии должны касаясь друг друга; например, можно объединить А1 с В1, но не А1 с С1 без слияния.

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

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

Примечание: Вы не можете разделить необъединенное клетки. Содержимое необъединенные ячейки можно только распределить на соседние столбцы.

Объединить текст из двух или более ячеек в одну ячейку

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

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

1. Выберите ячейку, где вы хотите разместить комбинированный текст.
2. В выбранную ячейку введите формулу со своими значениями “=А1&В1&С1” , “=А1&» «&В1&» «&С1” или =СЦЕПИТЬ(B3;» «;C3).
3. После ввода формулы нужно нажать на клавишу «Enter», для применения формулы. 

Объединить столбцы данных без пробелов между данными: =А1&В1&С1&…

Объединить столбцы данных с пробелами между данными: =А1&» «&В1&» «&С1&…

Объединить столбцы данных с пробелами между данными: =СЦЕПИТЬ(B3;» «;C3).

Видео