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

Обновлено: 07.01.2025

Информация о сайте

Инструменты и настройки

Excel Windows
и
Excel Macintosh

Вопросы и решения

Работа и общение

Работа форума и сайта

Функции листа Excel

= Мир MS Excel/Привязка значений к названиям ячеек - Мир MS Excel

Войти через uID

Здравствуйте! Скажите пожалуйста! Мучаюсь достаточно простым вопросом.

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

Т.е. выглядит это примерно следующим образом:
В ячейку A1 пишем "Апельсин" и в ячейке B1 сразу же получаем значение "100"

И еще один момент: если уже таблица запонена словами апельсин, нужно, что бы после выполнения либо этого макроса или применения таких настроек. вся таблица где есть слово "Апельсин" получила в соседних с ним ячейках указное значение - в данном случае 100. Спасибо!

Здравствуйте! Скажите пожалуйста! Мучаюсь достаточно простым вопросом.

Зависимость одной ячейки от значения другой ячейки
Дорого времени суток! Не могу найти решение своей задачи, суть задачи в следующем: есть.

Зависимость цвета одной ячейки от другой
Здравствуйте! Мне нужно чтобы у меня в Excel файле цвет одной ячейки = был другой ячейке. .

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

Зависимость значения одной ячейки от другой совмещенно со списками
Добрый день! Прошу помочь с решением вопроса. Имеется созданный мной файл Транспортной накладной, в.

уточните пож-та вопрос:
что конкретно нужно сделать?
создать формулу, чтобы в зависимости от изменения одной ячейки менялось значение другой? Здраствуйте. Нужно сдлеать так что бы при нажатие на ячейку в Excel выподало окно с выбором вариантов к примеру A .B .C . а в другой ячейки в зависимости от выбранного варианта применялась формула , к любому числу акое туда впишешь. Заранее спасибо Конечно выложу.
Если значение ячейки А1=К то значение Ячейки B2*10 иначе B2*20/ Ну вот к примеру. Помогите пожалуйста )))

Вопрос по Exel: в одном столбце таблицы номера телефонов, в соседнем столбце соответствующие этим номерам фамилии. Как сделать так, чтобы при появлении в произвольно выбранной ячейке Х номер телефона из столбца 1, в другой произвольно выбранной ячейке Y появлялась фамилия, соответствующая (построчно) выбранному номеру телефона?

Заранее благодарен супер мозгам!
Валерий

1234567 Иванов если здесь написать 1234567
8769432 Петров то здесь должно появиться Иванов
1326578 Сидоров
7786549 Козлов
5454679 Маков
324521 Раздольский
3344507 Михеев
2139832 Комов
2218790 Лапин

Добавлено через 17 минут
Может я неправильно объясняю, но нужно, чтобы Exel выполнил следующий процесс: в ячейке Х записываю число (номер тлф). Программа запрашивает заранее введенную базу, в которой каждая строчка с номером тлф соответствует определенной фамилии. Ищет совпадающий номер тлф, и выдает в ДРУГУЮ ячейку фамилию, соответствующую записанному в первой ячейке.

А может я?
Читайте справку по ВПР() до тех пор, пока у Вас не получится правильный результат.

Читайте справку по ВПР() до тех пор, пока у Вас не получится правильный результат.[/QUOTE]

Делал в Exel пока не получилось. За помощь в выборе формулы и за оперативные ответы - спасибо. Действительно очень выручили. За то, что заставили потратить полчаса на то, что Вы могли бы мне написать за 1 мин., а именно формулу - (((
Хотя тогда бы я вряд ли запомнил бы принцип.

Информация о сайте

Инструменты и настройки

Excel Windows
и
Excel Macintosh

Вопросы и решения

Работа и общение

Работа форума и сайта

Функции листа Excel

= Мир MS Excel/Привязка значений к названиям ячеек - Мир MS Excel

Войти через uID

Здравствуйте! Скажите пожалуйста! Мучаюсь достаточно простым вопросом.

У вас есть таблица с несколькими столбцами текстовых данных, а вам нужно объединить такие данные в одну строку, а если таких строк тысячи? Конечно не стоит объединять текст в Excel вручную, даже если у вас маленькие объемы. Ведь есть специальная функция СЦЕПИТЬ в Excel, она сделает это за вас, почти моментально. Чаще всего требуется объединить ФИО в одну ячейку.

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

Функция СЦЕПИТЬ в Excel. Как объединить Фамилию, Имя и Отчество в одну строку

Итак, нам дан список сотрудников, нужно объединить каждое ФИО в отдельную ячейку.

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

Кстати, чтобы вставить любые текстовые данные заключите их в кавычки!

В итоге получается:

Запишем в столбец D такие формулы:

По мне так этот способ даже удобнее.

Сцепить текст и число

Если к объединенному ФИО нужно добавить например число, то это можно сделать аналогично тексту, добавьте столбец с числом

Но здесь есть 1 нюанс — в Эксель вы зачастую округляете число форматом числа. Т.е. само число остается в неизменном виде, например с четырьмя знаками после запятой.

Добавляем в формулу нужное округление

Теперь все красиво!

Сцепить текст и дату

Неплохо было бы добавить дату рождения к сотрудникам. Но формула посчитает дату как число. Используем вот такую хитрость — форматируем числовое значение даты в вид Дата функцией ТЕКСТ

Разделить текст по столбцам

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

Пример как сцепить в Excel по всем формулам прикладываю

Как сцепить диапазон ячеек? Функция СЦЕП()

В Excel 2016 появилась возможность сцепить несколько ячеек выделив диапазон, а не каждую ячейку по отдельности. Это функция СЦЕП — у нее только один аргумент, просто выберите диапазон ячеек, которые надо объединить.

Поиск повторений в больших таблицах данных при помощи объединения текста

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

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

В отдельном столбце запишем формулу

Как видите мы не используем никакие доп. символы, ни запятые, ни пробелы. А функции СЖПРОБЕЛЫ удаляют все пробелы в начале и конце значения каждой ячейки. Так мы исключим человеческий фактор при наборе данных — люди часто случайно ставят пробел в конце слова.

Теперь подсветим все дубликаты при помощи условного форматирования.

Красным выделились все повторяющиеся строки:

Бывает очень полезно!

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

Связывание ячеек | Справочные статьи Smartsheet

Блок поломан или отсутствует. Либо не найдено его содержимое, либо необходимо включить его модуль.

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

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

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

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

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

Создание связи

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

ПРИМЕЧАНИЯ.

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

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

Удаление связей

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

СОВЕТ. Чтобы удалить входящую связь, можно также нажать клавишу [BACKSPACE] после выбора ячейки со связью. Появится всплывающее окно с запросом подтверждения. При удалении строк со связанными конечными ячейками также удаляются ссылки из исходной таблицы.

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

Создание связей с помощью функции «Специальная вставка»

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

Чтобы создать связь с помощью функции «Специальная вставка», выполните следующие действия.

Типы ячеек, не допускающие связывание

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

даты окончания;
предшественники;
сводные ячейки в родительских строках («Дата начала», «Дата окончания», «% выполнено»);
даты начала с зависимостью.

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

Связи ячеек нельзя создать в столбцах «Вложения» и «Обсуждения».

Ячейки с входящими связями также нельзя изменить в следующих ситуациях:

из опубликованной таблицы;
из запроса изменения;
в мобильной версии Smartsheet;
в версии Smartsheet для планшетов;
в отчете;
в форме «Изменить строку».

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

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

Как разорвать связи в Excel

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

К сожалению, если книга-источник была удалена/перемещена или переименована, то связь нарушится. Также связь будет потеряна если вы переместите конечный файл (содержащий ссылку). Если вы передадите только конечный файл по почте, то получатель тоже не сможет обновить связи.

Как разорвать связь

Перейдите на вкладку Данные.
Выберите команду Изменить связи в разделе Подключения.
Нажмите Разорвать связь.

ВАЖНО! При разрыве связи все формулы ссылающиеся на книгу-источник будут преобразованы в значения! Отмена данной операции невозможна!

Как разорвать связь со всеми книгами

Для удобства, можно воспользоваться макросом, который разорвет связи со всеми книгами. Макрос входит в состав надстройки VBA-Excel. Чтобы им воспользоваться необходимо:

Код на VBA

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

Как разорваться связи только в выделенном диапазоне

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

Условное форматирование (2 урок)

Условное форматирование по значению другой ячейки

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

Для этого мы рассмотрим пример УФ (условного форматирования) по значению другой ячейки. Для этого используем имеющийся у нас набор случайных чисел в диапазоне A1: A15.

А в ячейке B3 ставим к примеру число «25», которое и послужит основанием для создания условий форматирования.

Выделяем диапазон, в котором хотим видеть результат нашего анализа, а именно A1: A15. В открывшемся окне инструмента «условное форматирование» на панели инструментов, выбираем «правило выделения ячеек» — «больше»

Затем один раз кликаем левой кнопкой мыши на исходное число «25», на основании которого делаем анализ. То есть ячейку B3 и ссылка автоматически вставляется в левую часть открытого окна «больше»

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

Сравнение двух баз данных

Усложняем задачу. У нас имеется две разных базы с данными. Нам необходимо найти в них одинаковые значения. Для примера используем данные диапазона A1: A15. А в диапазон B1: B15 забиваем ряд случайных чисел

Выделяем исходный диапазон чисел A1: A15. Причем выделяем сверху – вниз (это имеет значение, об этом я скажу чуть ниже). В открывшемся окне инструмента «условное форматирование» на панели инструментов, выбираем «правило выделения ячеек» — «равно»

и в левой части выпавшего окна вставляем относительную ссылку в диапазоне B1: B15. А точнее, в нашем случае ячейка В1.

Для тех, кто не знает, что такое «относительная ссылка» я сделаю пояснение чуть позже. А пока делаем так же как в предыдущем примере. То есть кликаем один раз в ячейке B1. По умолчанию в окне ставится значение абсолютной ссылки. Удаляем во вставленной ссылке в левой части открытого окна «равно» знак «$» перед номером ячейки и номером столбца.. Было так «$B$1»

Ссылка наша стала относительной. Можно правда оставить ссылку смешанной. Но не буду Вас запутывать, делайте пока как показано в примере.

Ссылки в Excel

А теперь немного пояснения. Ссылка в Excel это указание адреса на ячейку (диапазон) зашифрованное в формулу. Ссылки в Excel могут быть относительными, абсолютными и смешанными. Относительная ссылка – это значение формулы с изменяемым адресом относительно нового места. То есть к примеру, у нас в ячейке A16 стоит вот такая формула для подсчета суммы в диапазоне A1:A15

А нам хотелось бы применить такие же параметры для диапазона B1:B15. В этом случае мы просто копируем формулу из A16 и вставляем в B16.

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

Абсолютная ссылка

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

Теперь мы поняли A1 – это относительная ссылка, а $A$1 – это абсолютная ссылка. Я думаю вы уже догадались, что такое смешанная ссылка. Это когда что-то одно, или адрес строки или адрес столбца являются относительными, а что-то абсолютными. Применение этих ссылок это, так же отдельная тема.

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

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

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

Сравнительный анализ

Если мы выделили диапазон снизу-вверх, а ссылку привязали к ячейке В1, никакого анализа мы не получим. Проверить правильность заданного нами правила очень просто. Выделяем любую ячейку диапазона с условным форматированием. Далее заходим в меню инструмента, находим «Управление правилами» и кликаем.

Если правило задано нами правильно, то в окне «диспетчера правил условного форматирования» мы видим какая ссылка привязана к нашему диапазону

Это проверочное действие. Ну а то что функция у нас работает правильно, мы видим по выделенной другим цветом информации в диапазоне A1: A15. Следовательно, запомнили ссылку всегда ставим напротив активной ячейки, поэкспериментируйте и Вы поймете. Следите пожалуйста за этим правилом при использовании данной функции условного форматирования. И так подведем итог.

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

Ну а на следующем уроке мы будем изучать «условное форматирование нескольких условий»
С уважением

Спасибо Вам, что поделились статьей в социальных сетях!

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

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

Я offsetRelative функцию offsetRelative чтобы использовать Application.Caller для определения ячейки / диапазона, где находится кнопка, и получить номер строки из этого. Затем вместо жесткого кодирования в диапазоне, таком как «AE3», мы можем объединить столбец «AE» с номером строки:

Для выполнения разных действий с каждой кнопки вы можете назначить им все те же подпрограммы и написать одну подпрограмму, которая использует ActiveSheet.Buttons(Application.Caller).TopLeftCell для определения ячейки, в которой находится кнопка, и решает, что делать основанный на адресе ячейки.

Вы можете обращаться к ячейкам относительно известной ячейки с помощью .Offset(x, y) или, например, .Row вместо использования Sheet.Range , чтобы избежать динамического вычисления диапазонов, если каждый раздел, в котором находятся кнопки, путь.

Еще одна вещь, которую вы можете попробовать, – это кодирование номера строки в названии кнопки. Таким образом, вы можете установить .

11 – это первая позиция символа, это зависит от длины вашего префикса, которая в этом примере была «Button_Row» [10 символов].

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