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

Как в экселе связать ячейки


Как создать связь между ячейками в книге Excel? — MS Excel

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

  • В окне открытого листа выделите ячейку с исходным значением.
  • Используйте любой известный способ копирования (кнопка «Копировать» на вкладке «Главная», Ctrl+C и т. д.).
  • Выделите ячейку, которая будет связанна с исходной.
  • Перейдите к вкладке «Главная» и в группе «Буфер обмена» раскройте меню кнопки «Вставить».
  • В списке команд выберите пункт «Вставить связь» (рис. 4.4).
  • Закрепите результат щелчком по клавише Esc.
Примечание

Дальнейшее изменения значений в исходной ячейке будут автоматически отображаться в связанной ячейке.

Рис. 4.4. Вкладка «Главная». Меню кнопки «Вставить». Пункт «Вставить связь»

Выбор диапазона ячеек в Excel

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

В формулах диапазон записывается адресами двух ячеек, разделенных двоеточием. Левая верхняя и правая нижняя ячейки, входящие в диапазон, например A1: B3.

Взаимодействие с другими людьми

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

Диапазон включает:

  1. Несколько ячеек, выделенных блоком (B5: D8).
  2. Одна ячейка (A2: A2).
  3. Целая строка (18:18) или несколько строк (18:22).
  4. Весь столбец или несколько столбцов (F: F).
  5. Несколько несмежных диапазонов (B2: C7, E3: h4, F4: F7).
  6. Весь лист (1: 1048576) CTRL + A.

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

Взаимодействие с другими людьми

Работа с выделенными диапазонами ячеек MS Excel

Выделение диапазонов - одна из основных операций при работе с Excel. Использование диапазонов:

  • при заполнении данных;
  • при форматировании;
  • при очистке и удалении ячеек;
  • при создании диаграмм и диаграмм и др.

Способы выделения диапазонов:

  1. Чтобы выбрать диапазон, например A1: B3, вы должны навести курсор мыши на ячейку A1 и удерживать левую кнопку мыши, чтобы поместить курсор в ячейку B3.Казалось бы, нет ничего проще и этого достаточно для практических познаний. Но попробуйте выбрать таким образом блок B3: D12345.
  2. Щелкните сейчас ячейку A1, затем нажмите и удерживайте клавишу SHIFT, а после этого щелкните ячейку B3. Таким образом, был выделен блок A1: B3. Эту операцию распределения можно условно записать: A1, затем SHIFT + B3.
  3. Вы можете выбирать диапазоны с помощью клавиш со стрелками на клавиатуре. Щелкните по ячейке D3 и, удерживая SHIFT, трижды нажмите клавишу со стрелкой вправо, пока курсор не переместится в ячейку G3.У нас есть небольшая очередь. Теперь, все еще не отпуская SHIFT, четыре раза нажмите клавишу «стрелка вниз», пока курсор не переместится на G7. Таким образом, мы идентифицировали блок линейки D3: G7.
  4. Как выделить несмежный диапазон ячеек в Excel? Выделите мышью блок B3: D8. Нажмите клавишу F8, чтобы активировать специальный режим. В строке состояния появится сообщение: «Расширить выбор». А теперь выделите мышкой блок F2: K5. Как видите, в этом режиме у нас есть возможность выделить несколько страз.Чтобы вернуться к нормальной работе, снова нажмите F8.
  5. Как выделить в Excel большой диапазон ячеек? Нажмите клавишу F5 или CTRL + G. В появившемся окне в поле «Ссылка» введите адрес: B3: D12345 (или B3: D12345) и нажмите OK. Таким образом, вы легко захватили огромный ассортимент всего за пару кликов.
  6. В поле «Имя» (которое находится слева от строки формулы) необходимо указать диапазон ячеек: B3: D12345 (или B3: D12345) и нажать «Enter».

Есть 5 и 6 методов - это самое быстрое решение для выделения больших диапазонов.Небольшие диапазоны в пределах одного экрана лучше всего отмечать мышью.

Выбор диапазонов целых столбцов или строк

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

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

Выбор всего диапазона листов

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

Выбор несмежного диапазона

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

Чтобы выбрать их, просто удерживайте клавишу CTRL, а затем продолжайте обычное выделение. Также в этой ситуации режим будет особенно полезен после нажатия клавиши F8: «Расширить выделенный фрагмент».

.

Как объединить ячейки в Excel (простое пошаговое руководство)

Есть несколько способов объединить ячейки в Excel.

Одним из наиболее часто используемых способов является использование параметра Merge & Center на вкладке «Главная».

Проблема с использованием Merge & Center заключается в том, что он может объединять ячейки, но не текст внутри этих ячеек. Допустим, у нас есть набор данных, как показано ниже:

Если я выберу ячейки A1 и B1 и использую параметр «Объединить и центрировать», он сохранит текст из самой левой ячейки (в данном случае A1) и удалит текст из всех остальных ячеек.

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

Если вы продолжите и нажмете ОК, две ячейки объединятся, а текст останется только из самой левой ячейки. В приведенном выше примере он объединит A1 и B1 и покажет только текст John.

Правильное объединение ячеек в Excel

Если вы не хотите терять текст из-за объединения ячеек, используйте формулу СЦЕПИТЬ.Например, в приведенном выше случае введите следующую формулу в ячейку C1: = СЦЕПИТЬ (A1, "", B1)

Здесь мы объединяем ячейки A1 и B1 и используем пробел в качестве разделителя. Если вам не нужен разделитель, вы можете просто опустить его и использовать формулу = СЦЕПИТЬ (A1, B1).

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

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

Вы также можете использовать знак амперсанда для объединения текста. Например, вы также можете использовать = A1 & »« & B1

.

Преимущество отсутствия слияния ячеек в Excel

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

Альтернатива использованию Merge & Center

Если вы хотите объединить ячейки в разных столбцах в одну строку, вот альтернатива Merge & Center - опция Center Across Selection .

Вот как им пользоваться:

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

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

ПРИМЕЧАНИЕ. Для работы «От центра к поперечнику» убедитесь, что данные содержатся только в крайней левой ячейке.

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

.

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

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

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

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

Как заблокировать ячейки в Excel

Заблокировать все ячейки на листе Excel просто - вам просто нужно защитить лист.Поскольку атрибут Locked выбран для всех ячеек по умолчанию, защита листа автоматически блокирует ячейки.

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

Подробные инструкции по блокировке ячеек в Excel 2010, Excel 2013 и Excel 2016 приведены ниже.

1. Разблокируйте все ячейки на листе.

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

  • Нажмите Ctrl + A или нажмите кнопку Select All , чтобы выделить весь лист.
  • Нажмите Ctrl + 1, чтобы открыть диалоговое окно Формат ячеек (или щелкните правой кнопкой мыши любую из выбранных ячеек и выберите Форматировать ячейки из контекстного меню).
  • В диалоговом окне Формат ячеек перейдите на вкладку Защита , снимите флажок Заблокировано и нажмите ОК .

2. Выберите ячейки, диапазоны, столбцы или строки, которые нужно защитить.

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

Чтобы защитить столбцы в Excel, выполните одно из следующих действий:

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

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

Чтобы заблокировать все ячейки с формулами , перейдите на вкладку Home > Редактирование группы > Найдите и выберите > Перейти к специальному .В диалоговом окне Перейти к специальному установите переключатель Формулы и нажмите кнопку ОК. Подробные инструкции со снимками экрана см. В разделе Как заблокировать и скрыть формулы в Excel.

3. Заблокируйте выбранные ячейки.

Выбрав необходимые ячейки, нажмите Ctrl + 1, чтобы открыть диалоговое окно Формат ячеек (или щелкните правой кнопкой мыши выбранные ячейки и выберите Форматировать ячейки ), перейдите на вкладку Защита и установите флажок Заблокировано .

4. Защитите лист.

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

На вкладке Review в группе Changes нажмите кнопку Protect Sheet . Или щелкните правой кнопкой мыши вкладку листа и выберите Защитить лист… в контекстном меню.

Вам будет предложено ввести пароль (необязательно) и выбрать действия, которые вы хотите разрешить пользователям выполнять.Сделайте это и нажмите ОК. Вы можете найти подробные инструкции со скриншотами в этом руководстве: Как защитить лист в Excel.

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

Как разблокировать ячейки в Excel (снять защиту с листа)

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

Для получения дополнительной информации см. Как снять защиту с листа Excel.

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

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

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

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

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

  1. Выберите ячейки или диапазоны, которые нужно разблокировать паролем, когда лист защищен.
  2. Перейдите на вкладку Review > Changes group и щелкните Allow Users to Edit Ranges .

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

  3. В диалоговом окне Разрешить пользователям редактировать диапазоны нажмите кнопку Новый… , чтобы добавить новый диапазон:
  4. В диалоговом окне New Range выполните следующие действия:
    • В поле Название введите значащее имя диапазона вместо значения по умолчанию Диапазон1 (необязательно).
    • В поле Относится к ячейкам введите ссылку на ячейку или диапазон. По умолчанию включены текущие выбранные ячейки или диапазон (ы).
    • В поле Пароль диапазона введите пароль. Или вы можете оставить это поле пустым, чтобы позволить всем редактировать диапазон без пароля.
    • Щелкните кнопку ОК.

    Наконечник. Помимо или вместо разблокировки указанного диапазона паролем, вы можете предоставить определенным пользователям разрешения на редактирование диапазона без пароля .Для этого нажмите кнопку Permissions… в нижнем левом углу диалогового окна New Range и следуйте этим инструкциям (шаги 3–5).
  5. Появится окно Подтвердите пароль и предложит вам повторно ввести пароль. Сделайте это и нажмите ОК .
  6. Новый диапазон будет указан в диалоговом окне Разрешить пользователям редактировать диапазоны . Если вы хотите добавить еще несколько диапазонов, повторите шаги 2–5.
  7. Нажмите кнопку Защитить лист на кнопке окна, чтобы активировать защиту листа.
  8. В окне Protect Sheet введите пароль для снятия защиты с листа, установите флажки рядом с действиями, которые вы хотите разрешить, и нажмите OK . Наконечник. Рекомендуется защитить лист паролем, отличным от пароля, который вы использовали для разблокировки диапазона (ов).
  9. В окне подтверждения пароля еще раз введите пароль и нажмите OK. Это оно!

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

Разрешить определенным пользователям редактировать выбранные ячейки без пароля

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

Примечание. Эта функция работает в Windows XP или более поздней версии, и ваш компьютер должен находиться в домене.

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

  1. Перейдите на вкладку Review > Changes group и щелкните Allow Users to Edit Ranges .

    Примечание. Если Разрешить пользователям редактировать диапазоны неактивен, нажмите кнопку Снять защиту листа , чтобы снять защиту рабочего листа.

  2. В окне Разрешить пользователям редактировать диапазоны выберите диапазон, для которого вы хотите изменить разрешения, и нажмите кнопку Разрешения… .

    Наконечник. Кнопка Permissions… также доступна при создании нового диапазона, разблокированного паролем.

  3. Откроется окно Permissions , и вы нажмете кнопку Добавить… .
  4. В поле Введите имена объектов для выбора введите имена пользователей, которым вы хотите разрешить редактирование диапазона.

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

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

    Excel проверил мое имя и применил необходимый формат:

  5. Когда вы ввели и подтвердили имена всех пользователей, которым вы хотите предоставить разрешения на редактирование выбранного диапазона, нажмите кнопку ОК.
  6. В разделе Группа или имена пользователей укажите тип разрешения для каждого пользователя ( Разрешить или Запретить ) и нажмите кнопку ОК, чтобы сохранить изменения и закрыть диалоговое окно.

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

Как заблокировать ячейки в Excel, кроме ячеек ввода

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

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

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

Ожидается, что пользователи будут вводить свои данные в ячейки B2: B9, а формула в B11 вычисляет баланс на основе ввода пользователя. Итак, наша цель - заблокировать все ячейки на этом листе Excel, включая ячейку формулы и описания полей, и оставить разблокированными только ячейки ввода (B3: B9). Для этого выполните следующие действия.

  1. На вкладке Home в группе Styles найдите стиль Input , щелкните его правой кнопкой мыши и выберите Modify… .
  2. По умолчанию стиль Excel Input включает информацию о шрифте, цветах границы и заливки, но не о состоянии защиты ячеек. Чтобы добавить его, просто установите флажок Защита :

    Совет. Если вы хотите только разблокировать входные ячейки без изменения форматирования ячеек , снимите все флажки в диалоговом окне Style , кроме поля Protection .

  3. Как вы можете видеть на скриншоте выше, Защита теперь включена в стиль Input , но для него установлено значение Locked , а нам нужно разблокировать входные ячейки .Чтобы изменить это, нажмите кнопку Format … в правом верхнем углу окна Style .
  4. Откроется диалоговое окно Format Cells , вы переключитесь на вкладку Protection , снимите отметку с поля Locked и нажмите OK:
  5. Диалоговое окно Style обновится, чтобы указать статус No Protection , как показано ниже, и вы щелкните OK :
  6. А теперь выберите ячейки ввода на листе и нажмите кнопку стиля Вход на ленте.Выбранные ячейки будут отформатированы и разблокированы одновременно:
  7. Как вы помните, блокировка ячеек в Excel не действует, пока не будет включена защита листа. Итак, последнее, что вам осталось сделать, это перейти на вкладку Review > Changes group и нажать кнопку Protect Sheet .

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

Как найти и выделить заблокированные / разблокированные ячейки на листе

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

Чтобы определить состояние защиты ячейки, введите слово «защита» в первый аргумент формулы ЯЧЕЙКИ и адрес ячейки во второй аргумент.Например:

= ЯЧЕЙКА ("защита", A1)

Если A1 заблокирован, приведенная выше формула возвращает 1 (ИСТИНА), а если она разблокирована, формула возвращает 0 (ЛОЖЬ), как показано на снимке экрана ниже (формулы находятся в ячейках B1 и B2):

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

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

  • Чтобы выделить заблокированные ячейки: = ЯЧЕЙКА ("защита", A1) = 1
  • Чтобы выделить разблокированные ячейки: = ЯЧЕЙКА ("защита", A1) = 0

Где A1 - крайняя левая ячейка диапазона, на который распространяется ваше правило условного форматирования.

В качестве примера я создал небольшую таблицу и заблокировал ячейки B2: D2, содержащие формулы СУММ.На следующем снимке экрана показано правило, выделяющее эти заблокированные ячейки:

Примечание. Функция условного форматирования отключена на защищенном листе. Поэтому обязательно отключите защиту рабочего листа перед созданием правила ( Вкладка «Просмотр »> Изменения, группа > Снять защиту листа ).

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

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

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

.

Смотрите также

Scroll To Top