Excel

Как в excel присвоить имя ячейки: Как сделать ссылку на название листа excel?

Содержание

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

Если мы пишем текст в Excel, например в качестве описания к таблице или диаграмме, рано или поздно мы сталкиваемся с вопросом: «Как правильно сделать нумерованный список в Excel?». Способов создать нумерованный список или список с маркерами всего два — внутри ячейки или 1 строка = 1 ячейке. Внутри статьи подробнее о каждом.

1. Нумерованный список в Excel в одной ячейке

Довольно часто для описания по пунктам требуется список структурой — это значительно упрощает восприятие текста. Но если вариант один пункт — одна строка не подходит, т.е. все надо уместить в 1 ячейку (если, например, количество ячеек ограничено).

Маркер или номер в ячейке:

Самый простой и видимо единственный способ — вставить маркер или номер копированием.

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

Выберите вкладку Вставка- раздел Текст — Символ

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

Чтобы быстро вызвать именно этот символ «•» (т.н. маркер), зажмите Alt и наберите на клавиатуре чисел справа код символа — 0149.

Чтобы сделать перенос строки в одной ячейке используйте сочетание Alt + Enter

2. Нумерованный список в нескольких строках. Нумеровка строк в Эксель

Здесь уже помогут стандартные способы Excel.

Как пронумеровать строки в Эксель. Без формул:

Введите число 1 и 2 в соседние ячейки списка и наведите курсор на квадрат в нижней правой границе ячейке. Зажмите левую кнопку мыши и протяните вниз

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

Автоматическая нумерация строк в Excel

Для простого определения номера строки в ячейке есть функция =СТРОКА(). Соответственно можно вернуть и номер строки в таблице. Это очень удобно

3. Маркерованый список в нескольких строках

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

А именно:

Выделите нужные ячейки

Зайдите в меню Формат ячейки (Правой кнопкой мыши — Формат Ячеек)

Во вкладке Число, найдите пункт (все форматы)

В поле тип введите символ-маркер, пробел, знак собака @ («• @») — теперь во всех ячейках в перед текстом будет стоять маркер «•».

Тоже вполне удобно для оформления текста в Excel.

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

Пример файла из этой статьи.

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

(Visited 7 068 times, 1 visits today)

    Добрый день, уважаемый читатель!

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

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

     Ну что же, рассмотрим создание выпадающих списков и для чего же это нужно:

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

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

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

   А делать выпадающий список в ячейке будем в несколько этапов:

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

      2. Выделяем нужный нам диапазон и в меню выбираем пункт “Данные” — “Проверка данных”, в появившемся контекстном окне выбираем из указанного выбора пункт “Список”.

     3. В разблокированной ниже строке указываем диапазон данных, которые должны быть у нас в выпадающем списке. Нажимаем “Ок” и работа сделана.

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

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

    Не забудьте поблагодарить автора!

Прогресс человечества основывается на желании каждого человека жить не по средствам
Сэмюэль Батлер, философ

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

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

Способ 1 — горячие клавиши и раскрывающийся список в excel

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

Этот же пункт меню можно запустить сочетанием клавиш Alt+»Стрелка вниз» и программа автоматически предложит в выпадающем списке значения ячеек, которые вы ранее заполняли данными. На изображении ниже программа предложила 4 варианта заполнения (дублирующиеся данные Excel не показывает). Единственное условие работы данного инструмента — это между ячейкой, в которую вы вводите данные из списка и самим списком не должно быть пустых ячеек.

Использование горячих клавиш для раскрытия выпадающего списка данных

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

Выпадающий список может работать и в верхней части с данными, которые ниже ячейки

Способ 2 — самый удобный, простой и наиболее гибкий

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

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

    Создание набора данных для списка

  2. В окне «Создание имени» задайте имя для вашего списка (это имя дальше будет использоваться в формуле подстановки). Имя должно быть без пробелов и начинаться с буквы.

    Введите имя для набора данных

  3. Выделите ячейки (можно сразу несколько ячеек), в которых планируется создать выпадающий список. Во вкладке «ДАННЫЕ» вверху документа нажмите на «Проверка данных».

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

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

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

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

Кроме списка можно вводить данные вручную. Если введенные данные не совпадут с одним из данных — программа выдаст ошибку

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

Способ 3 — как в excel сделать выпадающий список с использованием ActiveX

Чтобы воспользоваться этим способом, необходимо чтобы у вас была включена вкладка «РАЗРАБОТЧИК». По умолчанию эта вкладка отсутствует. Чтобы ее включить:

  1. Нажмите на «Файл» в левом верхнем углу приложения.
  2. Выберите пункт «Параметры» и нажмите на него.
  3. В окне настройки параметров Excel во вкладке «Настроить ленту» поставьте галочку напротив вкладки «Разработчик».

Включение вкладки «РАЗРАБОТЧИК»

Теперь вы сможете воспользоваться инструментом «Поле со списком (Элемент ActiveX)». Во вкладке «РАЗРАБОТЧИК» нажмите на кнопку «Вставить» и найдите в элементах ActiveX кнопку «Поле со списком (Элемент ActiveX)». Нажмите на нее.

Нарисуйте данный объект в excel выпадающий список в ячейке, где вам необходим выпадающий список.

Теперь необходимо настроить данный элемент. Чтобы это сделать, необходимо включить «Режим конструктора» и нажать на кнопку «Свойства». У вас должно открыться окно свойств (Properties).

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

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

  1. ListFillRange — указывает диапазон ячеек, из которых будут браться значения для выпадающего списка. В моем примере я указал два столбца (A2:B7 — дальше покажу как это использовать). Если необходимо только одни значения указывается A2:A7.
  2. ListRows — количество данных в выпадающем списке. Элемент ActiveX отличается от первого способа тем, что можно указать большое количество данных.
  3. ColumnCount — указывает сколько столбцов данных указывать в выпадающем списке.

В строке ColumnCount я указал значение 2 и теперь в списке выпадающие данные выглядят вот так:

Как видите получился выпадающий список в excel с подстановкой данных из второго столбца с данными «Поставщик».

Excel: Имена ячеек и адресация

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

По-умолчанию используется стиль A1. Это когда по строкам используется алфавит, а по горизонтали - цифры. Например, D10 - это десятая строка в колонке D. Есть и стиль называемый R1C1, который наиболее полезен при вычислении позиции строки и столбца в макросах а также при отображении относительных ссылок. В стиле R1C1, после буквы "R" указывается номер строки ячейки, после буквы "C" - номер столбца.

При работе в Excel, стили именования ячеек переключаются в меню Сервис -> Параметры -> Общие -> Стиль ссылок, при реальном программировании наиболее удобно пользоваться не этими стилями, а именами ячеек. Тогда работа с вашей ячейкой похожа на роботу с обычной переменной. Что многим более привычно и удобно. Например для констант или полей форм.

Для того, чтобы дать имя ячейке наведите на неё курсор. Выберите меню Вставка -> Имя -> Присвоить. Появится диалоговое окно, куда надо ввести имя и нажать кнопочку OK.

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

=MyName+10

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

Узнать все имена можно здесь:

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

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

Автор Дмитрий Якушев На чтение 2 мин. Просмотров 1.5k.

Что делает макрос: Самый простой вариант автоматизации — добавить новый лист с присвоением ему конкретного имени.

Как макрос работает

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

Код макроса

Sub DobavitNoviiList()
'Шаг 1: Говорим Excel, что делать, если ошибка
On Error GoTo MyError
'Шаг 2: Добавляем лист
Sheets. Add
'Шаг 3: Присваиваем имя
ActiveSheet.Name = "Отчет"_
WorksheetFunction.Text(Now(),"yyyy")
'Шаг 4: Выходим
Exit Sub
'Шаг 5: Если произошла ошибка, сообщение пользователю
MyError:
MsgBox "Лист с таким именем уже есть!"
End Sub

Как работает макрос

  1. Вы знаете, что если присвоить новому листу имя, которое уже есть, то возникнет ошибка. Таким образом, на шаге 1, макрос говорит Excel немедленно перейти к строке, которая говорит MyError (на шаге 3), если есть ошибка.
  2. Для создания листа используем метод Add. По умолчанию, лист называется SheetХХ, где хх представляет число листа. Мы даем листу новое имя путем изменения свойства объекта ActiveSheet.Name в этом случае мы именуем рабочий лист «Отчёт и текущий год».
  3. Как и в рабочих книгах, каждый раз, когда вы добавляете новый лист с помощью VBA, он автоматически становится активным. Именно поэтому мы пишем ActiveSheet. Name.
  4. Обратите внимание, что на шаге 4 мы выходим из процедуры. Делаем так, чтобы он случайно не показал сообщение об ошибке.
  5. Данный шаг запускается, если имя нового листа совпадает с уже существующим в книге. С помощью сообщения уведомляем пользователя об этом. Опять же, этот шаг должен быть выполнен только в случае возникновения ошибки.

Как использовать

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

  1. Активируйте редактор Visual Basic, нажав ALT + F11.
  2. Щелкните правой кнопкой мыши имя проекта / рабочей книги в окне проекта.
  3. Выберите Insert➜Module.
  4. Введите или вставьте код во вновь созданном модуле.

Как зафиксировать ссылку в Excel? | Что важно знать о

Очень важно знать для быстрого расчета прогноза в MS Excel — Как в формуле зафиксировать ссылку на ячейку или диапазон?

 

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

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

Для этого в строке формул выделяете ссылку, которую хотите зафиксировать:

и нажимаете клавишу «F4». Ссылка станет со значками $, как на рисунке:

это означает, что если вы протяните формулу, то ссылка на ячейку $F$4 останется на месте, т.е. зафиксирована строка '4' и столбец 'F'. Если вы еще раз нажмёте клавишу F4, то ссылка станет F$4 — это означает, что зафиксирована строка 4, а столбец F будет перемещаться.

Если еще раз нажмете клавишу «F4», то ссылка станет $F4:

Это означает, что зафиксирован столбец F и он не будет перемещаться, когда вы будите протаскивать формулу, а ссылка на строку 4 будет двигаться.

Если ссылки имеют вид R1C1, то полностью зафиксированная ячейка будет иметь вид R4C6:

Если зафиксирована только строка (R), то ссылка будет R4C[-1]

Если зафиксирован только столбец (С), то ссылка будет иметь вид RC6

Для того, чтобы зафиксировать диапазон, необходимо его выделить в строке формул в Excel и нажать клавиши “F4”.

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

Присоединяйтесь к нам!

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

  • Novo Forecast Lite - автоматический расчет прогноза в Excel.
  • 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Зарегистрируйтесь и скачайте решения

Статья полезная? Поделитесь с друзьями

 

Как присвоить имя ячейке Excel с помощью VBA?



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

Я пытался

Dim r As Range
set r = Application.Caller

r.Name = "Unique"
excel vba
Поделиться Источник nimo     17 февраля 2010 в 12:30

4 ответа


  • excel/VBA как присвоить переменной длины массива целочисленную переменную

    В excel/VBA у меня есть этот код Dim colName as Variant Dim lengthToDispl as Integer colName = Array(A, B, C) lengthToDispl = colName. length Я получаю следующую ошибку 'Object Required'. Это относится к последней строке. Как присвоить длину массива переменной длины (содержащего строки) переменной...

  • Excel VBA для отображения гиперссылки в ячейке

    Я хотел бы написать свой собственный макрос / функцию в VBA для Excel, который вводит новый formula JIRA(ISSUE_ID) в Excel, так что я могу использовать =JIRA(ISSUE_ID) в ячейке и он отображает следующую ссылку (псевдо Markdown синтаксис) [ISSUE_ID](http://my.jira.com/browse/ISSUE_ID) в той же...



6

Следующий код устанавливает ячейку A1 с именем 'MyUniqueName':

Private Sub NameCell()

Dim rng As Range
Set rng = Range("A1")
rng.Name = "MyUniqueName"

End Sub

Это поможет?

EDIT

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

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

Public Function MyCustomCalc(Input1 As Integer, Input2 As Integer, Input3 As Integer) As Integer

MyCustomCalc = (Input1 + Input2) - Input3

End Function

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

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Left$(Target.Formula, 13) = "=MyCustomCalc" Then
    Target.Name = "MyUniqueName"
End If
End Sub

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

Как я уже сказал, вышеизложенное не очень хорошо, но это может дать вам начало. Я не мог найти способ встроить код в пользовательскую функцию и установить имя диапазона непосредственно, например, с помощью Application.Caller.Address или Application.Caller.Cells(1,1) и т. д. Я уверен, что есть способ, но боюсь, что я немного заржавел на VBA...

Поделиться Alex P     17 февраля 2010 в 12:52



0

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

Private Sub SortForContactsOutlookImport()

    Dim ThisCell As Object
    Dim NextCell As Object
    Dim RangeName As String

    Set ThisCell = ActiveCell
    Set NextCell = ThisCell.Offset(0, 1)

    Do
        If ThisCell.Value <> "" Then
            RangeName = ThisCell. Value
            ActiveWorkbook.Names.Add Name:=RangeName, RefersTo:=ThisCell
            Set ThisCell = NextCell
            Set NextCell = ThisCell.Offset(0, 1)
        End If

    Loop Until ThisCell.Value = "Web Page"

End Sub

Поделиться Tony Webster     11 мая 2019 в 06:11



0

Я использую эту субмарину без формальной обработки ошибок:

Sub NameAdd()

    Dim rng As Range
    Dim nameString, rangeString, sheetString As String

    On Error Resume Next

    rangeString = "A5:B8"
    nameString = "My_Name"
    sheetString = "Sheet1"

    Set rng = Worksheets(sheetString).Range(rangeString)

    ThisWorkbook.Names.Add name:=nameString, RefersTo:=rng

End Sub

Чтобы удалить имя:

Sub NameDelete()
    Dim nm As name

    For Each nm In ActiveWorkbook.Names
        If nm.name = "My_Name" Then nm.Delete
   Next

End Sub

Поделиться Julio Galindo     24 мая 2019 в 18:53


  • Имя файла внутри ячейки файла excel с использованием VBA

    Мне нужен VBA, где он обновляет name of the excel file внутри этого конкретного excel file. В папке находится 12 файлов. Путь к этой папке-D:\Amit. Название этих 12 файлов - кассовый отчет по состоянию на 11-05-2017 0000Hrs, начиная с полуночи (вот почему 0000Hrs), и он увеличивается на 2 часа,...

  • Сохранение с помощью VBA: путь и имя файла в разных ячейках

    Я ищу код, который сохранит результаты из вывода excel (CHART jpg) под заданным путем и именем файла, который находится под путем. Таким образом, адрес (путь) находится в ячейке W7. и имя файла NAME.pptx в ячейке W8. Иначе говоря, Пусть в ячейке W7 будет расположен путь к файлу и в ячейке W8 имя...



-1

Вы можете продолжить с этим Range("A1") = "Unique"

Поделиться Omkar Salunke     26 июня 2019 в 07:55


Похожие вопросы:


VBA присвоить значение ячейке внутри функции?

Есть ли вообще что-то, что я могу назначить значение/имя ячейке внутри функции ? Спасибо EDIT Извините за неясность, но вот мое требование. У меня есть определенная пользователем функция...


JS - присвоить ячейке скрытое значение

Есть ли способ присвоить скрытое значение ячейке таблицы? Я хотел бы присвоить ячейке таблицы определенное значение, целое число или строку, чтобы добавить и отредактировать это значение в...


Отображение изменения значения ячейки в соседней ячейке с помощью excel VBA

У меня есть лист excel, который отображает цену на определенные элементы в столбце, просматривая amazon API с помощью excel vba. Цена может меняться сверхурочно. Поэтому я пытаюсь отображать разницу...


excel/VBA как присвоить переменной длины массива целочисленную переменную

В excel/VBA у меня есть этот код Dim colName as Variant Dim lengthToDispl as Integer colName = Array(A, B, C) lengthToDispl = colName.length Я получаю следующую ошибку 'Object Required'. Это...


Excel VBA для отображения гиперссылки в ячейке

Я хотел бы написать свой собственный макрос / функцию в VBA для Excel, который вводит новый formula JIRA(ISSUE_ID) в Excel, так что я могу использовать =JIRA(ISSUE_ID) в ячейке и он отображает...


Имя файла внутри ячейки файла excel с использованием VBA

Мне нужен VBA, где он обновляет name of the excel file внутри этого конкретного excel file. В папке находится 12 файлов. Путь к этой папке-D:\Amit. Название этих 12 файлов - кассовый отчет по...


Сохранение с помощью VBA: путь и имя файла в разных ячейках

Я ищу код, который сохранит результаты из вывода excel (CHART jpg) под заданным путем и именем файла, который находится под путем. Таким образом, адрес (путь) находится в ячейке W7. и имя файла...


Как присвоить имя пустому заголовку столбца excel с помощью VBA?

У меня есть Sub , который выполняет vlookup для целого столбца в Excel. Option Explicit Sub Vlookup() ' performs Vlookup of all entries in sheet1 with keys from sheet2 Dim wbk1 As Workbook Set wbk1...


Назначьте формулу ячейке Excel с помощью VBA

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


Напишите excel formula в ячейке на VBA

Я хочу записать excel formula в ячейку с помощью кода VBA. У меня есть стол в sheets1 под названием Table1. В sheets2, ячейке A2 я хочу добавить формулу для вычитания первой строки и последней...

microsoft-excel - Использование VBA для именования поля имени ячейки в Excel

Я хотел бы выполнить итерацию по строкам на рабочем листе и присвоить полям имен одного столбца (столбец E) значение соответствующей строки в другом столбце (столбец B).

Я хотел бы сделать это таким образом, потому что принцип останется неизменным для добавления, которое будет внесено в этот сценарий для долгосрочного обслуживания. Например, если бы я делал это вручную, я бы активировал ячейку "E2", щелкнул поле с именем и набрал "Coil_Width", который является значением ячейки "B2". Это правильно добавляет имя в менеджер имен, и я могу динамически изменить значение, набрав что-то в строке формул для этой ячейки. Я пытался назначить через eCell.Свойство Name = (и т.д.), Но, похоже, оно не работает для ячеек так же, как для свойства range, потому что я получаю ошибку, определенную приложением (1003).

Также с использованием стандарта

ActiveWorkbook.Names.Add Name:=bCell.Value, RefersTo:= eCell.Address()

просто добавляет имя в диспетчер имен и устанавливает для RefersTo значение «$ E $ 2» или любую другую ячейку (я думаю, что это правильно), но значение также устанавливается на "E2" (и не может быть изменено даже при вводе вручную значение в строке формул для этой ячейки. Кроме того, поле имени для этой ячейки по-прежнему отображается как "E2".

Вот пример кода:

For Each bCell In thisWkSht.Range("B:B").Cells
'For Each bCell In thisWkSht.Range("B:B:) - this is the changed line
If bCell.Value = "" Then
    'Do Nothing
ElseIf bCell.Value = "Variables" Then
    'Do Nothing
    'This allows me to skip the first row without much hassle
Else
    Set eCell = bCell.Offset(0, 3).Cells 
    'Set eCell = bCell.Offset(0, 3) - this is the other changed line
    eCell.Name = bCell.Value ' This line now correctly assigns the value from my
                             ' B row and column to the name of my E row cell

End If
Next

Мои результаты показывают, что я могу правильно получить доступ к bcell.Значение свойства, но оно ломается во время bCell.Название.Название. Мне кажется, что я просто должен быть в состоянии сделать что-то вроде eCell.Имя = bCell.Значение или что-то подобное, но я еще не получил ничего, кроме ошибки, определенной приложением.

В настоящее время я придерживаюсь метода ячеек, потому что я не уверен, как обратиться к изменяющемуся диапазону выбора в операторе For Each; то есть я могу понять, как обращаться к моей ячейке bCell по всему диапазону («B:B»), но я не знаю, как настроить мою ячейку eCell таким образом, чтобы диапазон, на который она ссылалась, был на самом деле диапазоном смещения bCell на три колонны.

Я что-то упускаю из этих свойств?

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

  1. Программное обеспечение
  2. Microsoft Office
  3. Excel
  4. Как назвать ячейку или диапазон ячеек в Excel 2013

Грег Харви

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

Вы также можете использовать имена диапазонов для обозначения выделенных ячеек, которые вы хотите распечатать или использовать в других программах Office 2013, таких как Microsoft Word или Access. Лучше всего то, что после того, как вы дадите имя ячейке или диапазону ячеек, вы можете использовать это имя с функцией «Перейти», чтобы не только найти диапазон, но и выделить все его ячейки.

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

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

    Например, вместо 01Profit используйте Profit01.

  • Имена диапазонов не могут содержать пробелов.

    Вместо пробела используйте подчеркивание (Shift + дефис), чтобы связать части имени вместе. Например, вместо Profit 01 используйте Profit_01.

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

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

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

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

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

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

  3. Введите имя выбранной ячейки или диапазона ячеек в поле «Имя».

    При вводе имени диапазона необходимо соблюдать соглашения об именах в Excel.

  4. Нажмите Enter.

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

Вы также можете выполнить то же самое, выбрав «Главная» → «Найти и выделить» → «Перейти» или нажав F5 или Ctrl + G, чтобы открыть диалоговое окно «Перейти».Дважды щелкните имя нужного диапазона в списке «Перейти». Excel перемещает курсор ячейки непосредственно в указанную ячейку. Если вы выбираете диапазон ячеек, все ячейки в этом диапазоне также выбираются.

Об авторе книги

Грег Харви, доктор философии, - президент Mind Over Media, компании онлайн-медиа. Он написал все выпуски Excel для чайников, Excel All-in-One для чайников, и Excel Workbook для чайников. Грег - опытный педагог с самыми разными интересами.

Как найти ячейки, содержащие определенный текст, и вернуть определенный текст в Excel

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

Сценарий:

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

Общая формула:

Текст, который вы ищете, может быть точным или нечувствительным к регистру. Без учета регистра означает, что формула ищет AG может возвращать ag, Ag, AG или aG .

Формула без учета регистра:

find_text: текст для поиска

inside_text: найти в тексте

Формула с учетом регистра:

find_text: текст для поиска

inside_text: найти в тексте

Примечание:

Приведенные выше формулы вернут True или False.Используйте функцию ЕСЛИ с формулой, чтобы вернуть ДА или НЕТ. Используйте приведенную ниже формулу, чтобы получить требуемый формат результата.

Формула без учета регистра:

= ЕСЛИ (ЕЧИСЛО (ПОИСК (найти_текст, внутри_текста)), «значение_если_истина», значение_если_ ложь)

find_text: текст для поиска

inside_text: найти в тексте

Формула с учетом регистра:

= ЕСЛИ (ЕЧИСЛО (НАЙТИ (найти_текст, внутри_текста)), «значение_если_ истинно», значение_если_ ложь)

find_text: текст для поиска

inside_text: найти в тексте

Примечание: В вышеупомянутой формуле вы можете ввести такие значения, как ДА / НЕТ или Найдено / Не найдено.

Пример:

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

Найдите и выделите все пустые ячейки в ваших данных [Советы по Excel] »Chandoo.org

Реальная история:

В пятницу (17 апреля 2015 г.) я вылетел из Визага (мой город) в Хайдарабад, чтобы успеть на рейс в Сан-Франциско для участия в конференции.Поскольку у меня было 10 часов наложения между рейсами в Хайдарабаде, я зарегистрировался в зоне отдыха, чтобы посмотреть спортивные передачи, поесть, притворившись, что работаю на своем ноутбуке. В соседнем помещении сидел джентльмен, который работал в Excel. Когда я начал писать несколько писем, джентльмен, сидевший в соседнем месте, спросил меня, чем я занимаюсь на жизнь. Наш разговор проходил так.

Я: Я управляю компанией-разработчиком программного обеспечения
Он: О, значит, вы, должно быть, хорошо разбираетесь в компьютерах
Я: улыбается и съеживается от стереотипов
Он: Какая формула позволяет выбрать все пустые ячейки в моих данных Excel и выделить их в желтом цвете

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

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

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

Конец реальной истории.

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

Давайте посмотрим на данные:

Вот образец данных.

Следует иметь в виду одну важную вещь:

  • Эти данные не имеют таблицы.

Есть 3 мощных и простых метода для поиска и выделения пустых ячеек.

Метод 1: подход к выделению и выделению

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

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

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

Теперь, когда все данные выбраны,

  1. Нажмите F5 и выберите Special
  2. Выбрать заготовки. Щелкните ОК.
  3. Это выберет только пустые ячейки.
  4. Заливка желтым (или другим) цветом, щелкнув значок заливки и выбрав цвет
  5. Готово!

Вот быстрая демонстрация этого:

Метод 2: подход с использованием фильтра

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

В данном случае

  1. Выберите все данные, используя шаги метода 1.
  2. Нажмите CTRL + Shift + L, чтобы активировать фильтры
  3. Не снимайте выделения и фильтруйте столбец, в котором должны отображаться только пустые значения
  4. Залейте желтым цветом
  5. Готово!

Метод 3. Подход с условным форматированием

Оба метода 1 и 2 имеют недостаток. Если ваши данные изменяются , вы должны очистить и снова выделить.

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

Для настройки условного форматирования,

  1. Выбрать все данные
  2. Перейти на главную> Условное форматирование> Новое правило
  3. Щелкните «Форматировать только ячейки, содержащие»
  4. Измените параметр «Значение ячейки» на «Пробелы»
  5. Настройте нужное форматирование, нажав кнопку «Форматирование»
  6. Нажмите ОК, и все готово!

Это автоматически выделит все пустые ячейки вашим любимым цветом.

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

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

Предположим, вы хотите проверить наличие пробелов в столбце G, и ваша первая точка данных находится в G4.

  1. Выбрать все данные (только данные, без заголовков)
  2. Перейти на главную> Условное форматирование> Новое правило
  3. Выберите тип правила «Использовать формулу…»
  4. Введите формулу как = LEN ($ G4) = 0
  5. Настройте нужное форматирование
  6. Нажмите ОК, и все готово.

Подождите, что такое LEN ($ G4) = 0 ?

Формула LEN () сообщает нам, какова длина содержимого ячейки. Итак, если ячейка пуста, LEN (ячейка) будет 0.

$ G4 - это смешанный эталонный стиль . Таким образом, даже когда условное форматирование проверяет другие столбцы, оно все равно смотрит в столбец G, чтобы увидеть, действительно ли он пуст.

Связано: Введение в ссылки на ячейки Excel.

Бонусных подсказок:

Q) Как выделить пустые столбцы G или H?
A) = ИЛИ (LEN ($ G4) = 0, LEN ($ h5) = 0)

Q) Как выделить, если оба столбца G и H пусты?
A) = И (LEN ($ G4) = 0, LEN ($ h5) = 0)

Удалите по ним пустые ячейки.

Как вы поступаете с пустыми ячейками?

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

Расскажите мне свою пустую историю в комментариях.

Заполните пробелы в своих знаниях об Excel

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

Как подсчитать количество ячеек, не равных указанному значению в Excel

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

Рисунок 1. Пример подсчета ячеек, не равных в Excel

Общая формула

= СЧЁТЕСЛИ (диапазон, <> значение)

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

Эта формула основана на функции СЧЁТЕСЛИ. Нам нужно указать диапазон, в котором мы хотим подсчитать ячейки.Следующий аргумент - это значение, которое мы хотим игнорировать во время подсчета. СЧЁТЕСЛИ будет считать все остальные значения, кроме этого.

Сначала СЧЁТЕСЛИ подсчитывает ячейки в диапазоне, который удовлетворяет предоставленному нами условию. Мы используем не равно оператору (<>) для подсчета ячеек в диапазоне, который не равен этому значению. Это возвращает количество ячеек, отличных от этого значения.

Настройка данных

В следующем примере используется набор данных о проекте.В столбцах A и B указаны названия и статус проектов. Статусы - «завершено», «продолжается» и «остановлено».

Рис. 2. Пример набора данных

Для подсчета ячеек, кроме заполненных, нам нужно:

  • Перейти в ячейку E4 .
  • Назначьте формулу = СЧЁТЕСЛИ ($ B $ 2: $ B $ 8, "<> Завершено") для E4.
  • Нажмите Введите .

Рис. 3. Применение формулы

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

Банкноты

  1. СЧЁТЕСЛИ без учета регистра . Мы можем искать любой комбинации прописных и строчных букв.
  2. Мы можем использовать значение из ячейки как часть критерия. Для этого нам нужно использовать символ амперсанда (&). Чтобы решить предыдущий пример для проекта, который не остановлен, нам нужно присвоить формулу = СЧЁТЕСЛИ (B2: B8, "<>" & B4) для E5.

Рис. 4. Количество ячеек, не равных использованию ссылок на ячейки

Это покажет количество проектов, за исключением приостановленных в E5.

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

Как выбрать случайное имя из списка с помощью MS Excel

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

  • rand используется для генерации числа от 0 до 1.
  • randbetween генерирует целое число в заданном диапазоне

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

Однако Microsoft Excel не предоставляет прямой формулы для этого.

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

Дельный Совет: Ищете способ начать писать с новой строки в той же ячейке в MS Excel? Попробуйте клавиши Alt + Enter.

Метод 1

Шаг 1: Заполните список имен, который у вас есть, в любом столбце, не беспокоясь о порядке. В нашем примере мы использовали столбец A . Также обратите внимание на номера первой и последней строки. Здесь их 1 и 13.

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

= INDIRECT (CONCATENATE («A», RANDBETWEEN (1, 13)))

Где:

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

1 и 13 следует заменить на номера первой и последней строки соответственно

Шаг 3: Нажмите Введите , и вы сможете увидеть первое случайное имя.Если вам нужно новое случайное имя, просто нажмите F9 .

Метод 2

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

Шаг 2: Заблокируйте область (ячейки, заполненные именами) и дайте ей имя, как показано на схеме ниже. Обратите внимание на поле, в котором я написал NameList . Это имя я присвоил списку.

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

= ИНДЕКС (A1: A13, RANDBETWEEN (1, 13)) или

= INDEX (NameList, RANDBETWEEN (1, 13))

Где,

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

1 и 13 , следует заменить на номера первой и последней строки соответственно, а

NameList следует заменить на имя, которое вы дали своему списку на шаге 2

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

Преимущество использования последней формулы состоит в том, что список и формула не обязательно должны находиться на одном листе. Это означает, что список может находиться на листе 1, а формула - на листе 2. Таким образом, никто не сможет увидеть, из какого списка сгенерировано случайное имя.

Заключение

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

Верхнее изображение Кредит: Алан Кливер


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

Что такое, как использовать, типы и примеры

  • На главную
  • Тестирование

      • Назад
      • Гибкое тестирование
      • BugZilla
      • Cucumber
      • Тестирование базы данных
      • JTL Testing
      • Назад
      • JUnit
      • LoadRunner
      • Ручное тестирование
      • Мобильное тестирование
      • Mantis
      • Почтальон
      • QTP
      • Назад
      • Центр контроля качества
      • SAP30003 Центр контроля качества
      • SoapUI
      • Управление тестированием
      • TestLink
  • SAP

      • Назад
      • ABAP
      • APO
      • Начинающий
      • Basis
      • BODS
      • BI
      • BPC
      • CO
      • Назад
      • CRM
      • Crystal Reports
      • QM4
      • 9000 3000 HRM
      • Заработная плата
      • Назад
      • PI / PO
      • PP
      • SD
      • SAPUI5
      • Безопасность
      • Менеджер решений
      • Successfactors
      • SAP Tutorials
    • 4
    • 4
    • Apache
    • AngularJS
    • ASP.Net
    • C
    • C #
    • C ++
    • CodeIgniter
    • СУБД
    • JavaScript
    • Назад
    • Java
    • JSP
    • Kotlin
    • MY
    • Linux
    • SQL
    • js
    • Perl
    • Назад
    • PHP
    • PL / SQL
    • PostgreSQL
    • Python
    • ReactJS
    • Ruby & Rails
    • Scala
    • SQL Server
    • 0003 SQL
    • 9003 SQL
    • назад
    • UML
    • VB.Net
    • VBScript
    • Веб-службы
    • WPF
  • Обязательно учиться!

      • Назад
      • Бухгалтерский учет
      • Алгоритмы
      • Android
      • Блокчейн
      • Бизнес-аналитик
      • Создание веб-сайта
      • Облачные вычисления
      • COBOL
      • Системы резервного копирования
      • Встроенный компилятор
      Дизайн
  • Учебные пособия по Excel
  • Программирование на Go
  • IoT
  • ITIL
  • Jenkins
  • MIS
  • Сетевые подключения
  • Операционная система
    • Назад
    • Prep
    • 000
    • Salesforce
    • SEO
    • Разработка программного обеспечения
    • VBA
    900 04
  • Big Data

      • Назад
      • AWS
      • BigData
      • Cassandra
      • Cognos
      • Хранилище данных
      • DevOps
      • Backgy4
      • HBase4
    • MongoDB
    • NiFi
  • .

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

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