Light-electric.com

IT Журнал
9 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как изменить значение ячейки в excel

Групповое изменение числовых значений в EXCEL

Быстро сложим / разделим/ умножим числовые значения из диапазона ячеек на заданное пользователем число. Этот подход позволяет уменьшить или увеличить разрядность чисел в выбранном диапазоне, быстро выделить НДС и пр.

В ряде случаев необходимо изменить уже содержащиеся в таблице числовые значения. Например, имеется таблица стоимости товаров, цены которых указаны в рублях (столбец B ):

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

Красивой альтернативой является использование инструмента Специальная вставка ( Главная/ Буфер обмена/ Вставить/ Специальная вставка ). Покажем как быстро разделить на 1000 весь столбец с ценами, используя инструмент Специальная вставка .

  • в любой пустой ячейке вводим 1000 (например, в D2 ) и нажимаем ENTER ;
  • копируем в Буфер обмена значение ячейки D2 ;
  • выделяем диапазон с ценами ( В2:В10 );
  • идем в меню Главная/ Буфер обмена/ Вставить/ Специальная вставка ;
  • выбираем Разделить в разделе Операция ;
  • нажимаем ОК .

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

Этот же подход позволяет, к примеру, убрать НДС (18%) из цен. Для этого нужно в ячейку D2 ввести =100/118 , скопировать это значение в Буфер обмена , выбрать Умножить в разделе Операция инструмента Специальная вставка . Другой пример: изменим валюту цены – переведем рубли в доллары по курсу 30 руб./долл. Для этого нужно в ячейку D 2 ввести курс (30), скопировать это значение в Буфер обмена , выбрать Разделить в разделе Операция инструмента Специальная вставка .

В приведенных выше примерах предполагалось, что ячейки с ценами не содержат формул. Если цены в столбце B являются результатом вычисления формул (например =C1*200 ), то после использования инструмента Специальная вставка , формулы будут модифицированы, например так =(C1*200)/1000 (если выбирали Разделить , а в Буфере обмена содержалось число 1000).

Как отредактировать содержимое ячейки?

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

Чтобы удалить знаки, щелкните позицию после них и нажмите клавишу BACKSPACE, или выделите их и нажмите клавишу DELETE.

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

Чтобы заменить отдельные знаки, выделите их и введите новые.

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

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

Чтобы указать начало новой строки в определенном месте ячейки, щелкните это место и нажмите клавиши ALT+ВВОД.

Как очистить ячейку?

— Чтобы удалить содержимое, щелкните позицию после них и нажмите клавишу BACKSPACE, или выделите их и нажмите клавишу DELETE.

Как выполняется сортировка по возрастанию (убыванию)?

— Можно выполнять сортировку данных по тексту (от А к Я или от Я к А), числам (от наименьших к наибольшим или от наибольших к наименьшим), а также датам и времени (от старых к новым или от новых к старым) в нескольких столбцах. Можно также выполнять сортировку по настраиваемым спискам (таким как состоящий из элементов «Большой», «Средний» и «Маленький») или по формату, включая цвет ячеек, цвет шрифта, а также по значкам. Большинство сортировок применяются к столбцам, но возможно также применить сортировку к строкам.

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

Практическая работа №3

Какими способами можно осуществлять изменение размеров ячейки? Опишите их.

— Ширина столбца на листе может иметь любое значение от 0 до 255. Это значение соответствует числу знаков, которые могут быть отображены в ячейке, отформатированной с использованием стандартного шрифта (Стандартный шрифт. Стандартный шрифт для текста на листе. Этот шрифт установлен по умолчанию для обычного стиля ячеек.). Ширина столбца по умолчанию составляет 8,43 знака. Если ширина столбца равна 0, столбец будет скрыт.

Высоту строки можно задать в пределах от 0 до 409. Это значение соответствует высоте строки в пунктах (1 пункт соответствует приблизительно 1/72 дюйма). Высота строки по умолчанию составляет 12,75 пункта. Если высота строки равна 0, строка будет скрыта.

— Изменение ширины столбца с помощью мыши

Выполните одно из следующих действий.

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

Читать еще:  Замена знака в excel

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

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

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

— Установка заданной ширины столбца

1. Выберите столбец или столбцы, ширину которых следует изменить.

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

3. В группе Размер ячейки выберите пункт Ширина столбца.

4. Введите значение в поле Ширина столбцов.

Дата добавления: 2018-04-05 ; просмотров: 3978 ;

Перемещение ячеек относительно друг друга в Microsoft Excel

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

Перемещение ячеек

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

Способ 1: перемещение с помощью копирования

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

  1. Выделяем ячейку, которую следует переместить. Жмем на кнопку «Копировать». Она размещена на ленте во вкладке «Главная» в группе настроек «Буфер обмена».

Выделяем любой другой пустой элемент на листе. Жмем на кнопку «Вставить». Она находится в том же блоке инструментов на ленте, что и кнопка «Копировать», но в отличие от неё имеет гораздо более заметный вид из-за своих размеров.

Далее переходим ко второй ячейке, данные которой нужно переместить на место первой. Выделяем её и опять жмем на кнопку «Копировать».

Одно значение мы переместили, куда нам нужно. Теперь возвращаемся к тому значению, которое мы вставили в пустую ячейку. Выделяем его и жмем на кнопку «Копировать».

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

  • Итак, нужные данные мы поменяли местами. Теперь следует удалить содержимое транзитной ячейки. Выделяем её и щелкаем правой кнопкой мыши. В контекстном меню, которое активировалось после этих действий, переходим по пункту «Очистить содержимое».
  • Теперь транзитные данные удалены, а задача по перемещению ячеек полностью выполнена.

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

    Способ 2: перетаскивание

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

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

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

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

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

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

    1. Прежде всего, нужно включить у себя в программе режим работы с макросами и панель разработчика, если вы их до сих пор не активировали, так как по умолчанию они отключены.
    2. Далее переходим во вкладку «Разработчик». Выполняем щелчок по кнопке «Visual Basic», которая размещена на ленте в блоке инструментов «Код».

    Выполняется запуск редактора. В него нужно вставить следующий код:

    Sub ПеремещениеЯчеек()
    Dim ra As Range: Set ra = Selection
    msg1 = «Произведите выделение ДВУХ диапазонов идентичного размера»
    msg2 = «Произведите выделение двух диапазонов ИДЕНТИЧНОГО размера»
    If ra.Areas.Count 2 Then MsgBox msg1, vbCritical, «Проблема»: Exit Sub
    If ra.Areas(1).Count ra.Areas(2).Count Then MsgBox msg2, vbCritical, «Проблема»: Exit Sub
    Application.ScreenUpdating = False
    arr2 = ra.Areas(2).Value
    ra.Areas(2).Value = ra.Areas(1).Value
    ra.Areas(1).Value = arr2
    End Sub

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

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

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

    Открывается окно выбора макроса. Отмечаем нужный элемент и жмем на кнопку «Выполнить».

  • После этого действия макрос автоматически меняет содержимое выделенных ячеек местами.
  • Читать еще:  Excel замена символа в ячейке

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

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

    Отблагодарите автора, поделитесь статьей в социальных сетях.

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

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

    ‘————————————————————————————— ‘ Procedure : ChangeAnotherCell ‘ http://www.excel-vba.ru ‘ Purpose : Функция меняет значение указанной ячейки ‘ Аргументы: ‘ rCell — ячейка, в которой необходимо поменять значение. ‘ vVal — значение для записи в ячейку rCell. ‘ Произвольный текст или ссылка на ячейку. ‘————————————————————————————— Function ChangeAnotherCell(rCell As Range, vVal) As String Application.Volatile With rCell If .Value = Empty Then .Replace Empty, vVal, 1, , 0 Else .Replace .Value, vVal, 1, , 0 End If End With End Function

    Синтаксис функции:
    =ChangeAnotherCell( G1 ;»новое значение»)

    rCell( G1 ) — ячейка, в которой необходимо поменять значение. Должна отличаться от адреса ячейки, в которую занесена функция.

    vVal( «новое значение» ) — значение для записи в ячейку rCell. Может быть как произвольным текстом или числом, так и ссылкой на другую ячейку. Значение в ячейке rCell не удаляется после удаления функции ChangeAnotherCell.

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

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

    ‘————————————————————————————— ‘ Procedure : ChangeAnotherCell ‘ Purpose : Функция меняет значение и формат указанной ячейки ‘ Аргументы: ‘ rCell — ячейка, в которой необходимо поменять значение. ‘ vVal — значение для записи в ячейку rCell. ‘ Произвольный текст или ссылка на ячейку. ‘ bRepFormat — ИСТИНА — будет скопирован формат из rFormatRng. ‘ rFormatRng — ссылка на ячейку, формат которой необходимо копировать. ‘————————————————————————————— ‘ Function ChangeAnotherCell(rCell As Range, vVal, Optional bRepFormat As Boolean = False, Optional rFormatRng As Range = Nothing) As String Application.Volatile If bRepFormat Then If Not rFormatRng Is Nothing Then With Application.ReplaceFormat .Clear .Font.Bold = rFormatRng.Font.Bold .Font.ColorIndex = rFormatRng.Font.ColorIndex .Font.FontStyle = rFormatRng.Font.FontStyle .Borders.Color = rFormatRng.Borders.Color .Borders.Weight = rFormatRng.Borders.Weight If Val(Application.Version) >= 12 Then ‘версии старше 2003 не поддерживают копирование заливки .Font.Name = rFormatRng.Font.Name Else .Interior.ColorIndex = rFormatRng.Interior.ColorIndex End If End With End If End If With rCell If .Value = Empty Then .Replace Empty, vVal, 1, , 0, , , bRepFormat Else .Replace .Value, vVal, 1, , 0, , , bRepFormat End If End With Application.ReplaceFormat.Clear End Function

    Синтаксис функции:
    =ChangeAnotherCell( G1 ;»новое значение»;ИСТИНА; E14 )

    rCell( G1 ) — ячейка, в которой необходимо поменять значение. Должна отличаться от адреса ячейки, в которую занесена функция.

    vVal( «новое значение» ) — значение для записи в ячейку rCell. Может быть как произвольным текстом или числом, так и ссылкой на другую ячейку. Значение в ячейке rCell не удаляется после удаления функции ChangeAnotherCell.

    bRepFormat( ИСТИНА ) — если указан как ИСТИНА или 1, то так же меняется формат ячейки rCell. Формат копируется из ячейки, указанной аргументом rFormatRng. Поэтому в данном случае аргумент rFormatRng обязателен к указанию. Если ЛОЖЬ, 0 или не указан — формат не копируется.

    rFormatRng( E14 ) — ссылка на ячейку, формат которой необходимо копировать.

    Статья помогла? Поделись ссылкой с друзьями!

    Как поменять местами ячейки в Excel

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

    Читать еще:  Удалить пустые строки в столбце excel

    Процедура перемещения ячеек

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

    Метод 1: копирование

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

    1. Встаем в первую ячейку (выделяем ее), которую планируем переместить. Находясь в главной вкладке программы нажимаем на кнопку “Копировать” (группа инструментов “Буфер обмена”). Также можно просто нажать комбинацию клавиш Ctrl+C.
    2. Переходим в любую свободную ячейку на листе и нажимаем кнопку “Вставить” в той же вкладке и группе инструментов. Или можно снова воспользоваться горячими клавишами – Ctrl+V.
    3. Теперь выделяем вторую ячейку, с которой хотим поменять местами первую, и также копируем ее.
    4. Встаем в первую ячейку и жмем кнопку “Вставить” (или Ctrl+V).
    5. Теперь выделяем ячейку, в которую было скопировано значение из первой ячейки и копируем ее.
    6. Переходим во вторую ячейку, куда нужно вставить данные, и нажимаем соответствующую кнопку на ленте.
    7. Выбранные элементы успешно поменяны местами. Ячейка, в которой временно размещались скопированные данные, больше не нужна. Щелкаем по ней правой кнопкой мыши и в открывшемся меню выбираем команду “Удалить”.
    8. В зависимости от того, есть ли рядом с данной ячейкой заполненные элементы справа/снизу или нет, выбираем соответствующий вариант удаления и жмем кнопку OK.
    9. Вот и все, что нужно было сделать для того, чтобы поменять ячейки местами.

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

    Метод 2: перетаскивание

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

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

    Метод 3: использование макросов

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

    1. Для начала нужно убедиться в том, что в приложении активирован так называемый “режим разработчика” (по умолчанию выключен). Для этого:
      • переходим в меню “Файл” и выбираем в перечне слева пункт “Параметры”.
      • в параметрах программы щелкаем по подразделу “Настроить ленту”, в правой части ставим галочку напротив пункта “Разработчик” и жмем OK.
    2. Переключаемся во вкладку “Разработчик”, где жмем по значку “Visual Basic” (группа инструментов “Код”).
    3. В редакторе, нажав на кнопку “View Code”, вставляем в появившемся окне код ниже:
      Sub ПеремещениеЯчеек()
      Dim ra As Range: Set ra = Selection
      msg1 = «Произведите выделение ДВУХ диапазонов идентичного размера»
      msg2 = «Произведите выделение двух диапазонов ИДЕНТИЧНОГО размера»
      If ra.Areas.Count <> 2 Then MsgBox msg1, vbCritical, «Проблема»: Exit Sub
      If ra.Areas(1).Count <> ra.Areas(2).Count Then MsgBox msg2, vbCritical, «Проблема»: Exit Sub
      Application.ScreenUpdating = False
      arr2 = ra.Areas(2).Value
      ra.Areas(2).Value = ra.Areas(1).Value
      ra.Areas(1).Value = arr2
      End Sub
    4. Закрываем окно редактора, щелкнув привычную кнопку в виде крестика в верхнем правом углу.
    5. Зажав клавишу Ctrl на клавиатуре выделяем две ячейки или две области с одинаковым количество элементов, которые планируем поменять местами. Затем нажимаем кнопку “Макросы” (вкладка “Разработчик”, группа “Код”).
    6. Появится окно, в котором мы видим ранее созданный макрос. Выбираем его и щелкаем “Выполнить”.
    7. В результате работы макрос поменяет местами содержимое выделенных ячеек.

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

    Заключение

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

    Ссылка на основную публикацию
    ВсеИнструменты
    Adblock
    detector
    ×
    ×