ГлавнаяРазноеF4 в excel не работает

Почему не работает формула ВПР (VLOOKUP) в Excel — Решение. F4 в excel не работает


Скрытие значений и индикаторов ошибок в ячейках

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

Формулы могут возвращать ошибки по многим причинам. Например, формула =1/0 возвращает ошибку #ДЕЛ/0!, поскольку деление на 0 недопустимо. Предусмотрены следующие значения ошибок: #ДЕЛ/0!, #Н/Д, #ИМЯ?, #ПУСТО!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!.

Преобразование ошибки в нулевое значение и использование формата для скрытия значения

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

Создание образца ошибки

  1. Откройте чистый лист или создайте новый.

  2. Введите 3 в ячейку B1, 0 — в ячейку C1 и формулу =B1/C1 — в ячейку A1.В ячейке A1 отобразится значение ошибки #ДЕЛ/0!.

  3. Выделите ячейку A1 и нажмите клавишу F2, чтобы изменить формулу.

  4. После знака равенства (=) введите ЕСЛИОШИБКА с открывающей круглой скобкой:ЕСЛИОШИБКА(

  5. Переместите курсор в конец формулы.

  6. Введите ,0), т. е. запятую с нулем и закрывающей круглой скобкой. Формула =B1/C1 примет вид =ЕСЛИОШИБКА(B1/C1,0).

  7. Нажмите клавишу ВВОД, чтобы завершить редактирование формулы. Теперь в ячейке вместо ошибки #ДЕЛ/0! должно отображаться значение 0.

Применение условного формата

  1. Выделите ячейку с ошибкой и на вкладке Главная нажмите кнопку Условное форматирование.

  2. Выберите команду Создать правило.

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

  4. Убедитесь, что в разделе Форматировать только ячейки, для которых выполняется следующее условие в первом списке выбран пункт Значение ячейки, а во втором — равно. Затем в текстовом поле справа введите значение 0.

  5. Нажмите кнопку Формат.

  6. На вкладке Число в списке Числовые форматы выберите пункт (все форматы).

  7. В поле Тип введите ;;; (три точки с запятой) и нажмите кнопку ОК. Нажмите кнопку ОК еще раз. Значение 0 в ячейке исчезнет. Это связано с тем, что пользовательский формат ;;; предписывает скрывать любые числа в ячейке. Однако фактическое значение (0) по-прежнему хранится в ячейке.

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

  1. Выделите диапазон ячеек, содержащих значение ошибки.

  2. На вкладке Главная в группе Стили щелкните стрелку рядом с командой Условное форматирование и выберите пункт Управление правилами. Появится диалоговое окно Диспетчер правил условного форматирования.

  3. Выберите команду Создать правило. Откроется диалоговое окно Создание правила форматирования.

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

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

  6. Нажмите кнопку Формат и откройте вкладку Шрифт.

  7. Щелкните стрелку, чтобы открыть список Цвет, и в разделе Цвета темы выберите белый цвет.

Возможны ситуации, когда необходимо, чтобы вместо значений ошибок в ячейках отображалась текстовая строка, например "#Н/Д", прочерк или "НД". Сделать это можно с помощью функций ЕСЛИОШИБКА и НД, как показано в примере ниже.

Описание функции

ЕСЛИОШИБКА   . С помощью этой функции можно определить, содержит ли ячейка или возвращает ли формула ошибку.

НД    Эта функция возвращает в ячейке строку "#Н/Д". Синтаксис функции: =НД().

  1. Щелкните отчет сводной таблицы. Появится вкладка Работа со сводными таблицами.

  2. Excel 2016 и Excel 2013: на вкладке Анализ в группе Сводная таблица щелкните стрелку рядом с командой Параметры и выберите пункт Параметры.

    Excel 2010 и Excel 2007: на вкладке Параметры в группе Сводная таблица щелкните стрелку рядом с командой Параметры и выберите пункт Параметры.

  3. Откройте вкладку Разметка и формат и выполните одно или оба указанных ниже действия.

    • Измените способ отображения ошибок.    В группе Формат установите флажок Для ошибок отображать. Введите в поле значение, которое должно отображаться вместо ошибок. Чтобы в пустых полях отображались ошибки, удалите из поля весь текст.

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

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

Ячейка с ошибкой в формуле

  1. В Excel 2016, Excel 2013 и Excel 2010: выберите Файл > Параметры >Формулы.

    В Excel 2007 нажмите кнопку Microsoft Office  и выберите пункты Параметры Excel > Формулы.

  2. В разделе Контроль ошибок снимите флажок Включить фоновый поиск ошибок.

См. также

Функция ЕСЛИОШИБКА

Функция НД

support.office.com

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

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

Доллар в MS Excel - для чего?

Чтобы правильно понять для чего и как использовать знак доллара в excel разберем некоторые моменты.

Первое. Эта картинка из прошлых уроков, на не мы используем формулу =B2+B3, она нам складывает содержимое ячеек В2 и В3, а ответ появиться в той ячейке, где записана формула.

Второе. Эта картинка тоже из прошлых уроков по автозаполнению. Здесь мы автоматически пронумеровали 7 строк. А что было бы, если мы выделим не две ячейки с единицей и двойкой, а одну, допустим с двойкой, и точно также потянем вниз или вправо. Попробуйте! В итоге в тех ячейках, на которые мы протянули появилась цифра 2 (см. рисунок ниже.)

Теперь объединим то, что мы использовали на первой картинке и на последней.

То, что в ячейку С5 мы ввели формулу для подсчета суммы вверхних ячеек это понятно, но как только мы потянули ячейку С5 вправо она также аналогично начала складывать соседние ячейки.

Т.е. в ячейку D5 записала результат сложения D3+D4 (в ячейке D5 появилась автоматически формула =D3+D4 )E5 записала результат сложения E3+E4 (в ячейке E5 появилась автоматически формула =E3+E4 )F5 записала результат сложения F3+F4 (в ячейке F5 появилась автоматически формула =F3+F4 )  и т.д.

Объясняю. Что общего у ячеек в которых записан ответ? Правильно у них одинаковая цифра (C5, D5, F5, G5), а буква разная вот программа Excel и заменила автоматически на нужные буквы в записи формул. Ведь в каждой ячейке у нас сейчас автоматически записана своя формула с нужными буквами.

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

Применение знака доллара

И так, вот Вам задание с применением знака $ доллара, о котором я говорил в самом начале/

Смотрите внимательно картинку, кликните по ней чтобы ее увеличить.

Скачать документ Excel с примером использования знака доллара

Если бы не ввели знак доллара, то буква H менялась так как менялись в прошлом примере. Но здесь нам надо, чтобы каждая ячейка складывалась с одной и тойже. т.е. когда мы потямем в право ячейку E12 буква H в ее формуле не должна меняться вот мы и поставили перед ней $ этот знак. 

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

Жми Добавляйся в группу в ВК и Одноклассниках!

tim-plus.ru

Проблемы с совместимостью листов - Excel

Число столбцов или строк в книге превышает поддерживаемое выбранным форматом файла. Данные, находящиеся вне первых 65 536 строк и 256 столбцов, не будут сохранены. Ссылки на данные из этого диапазона в формулах будут возвращать значение #ССЫЛКА!.

Что это означает.    Начиная с Excel 2007, размер листа составляет 1 048 576 строк в высоту и 16 384 столбцов в ширину, а в Excel 97–2003 всего 65 536 строк и 256 столбцов. Данные в ячейках за пределами этого диапазона строк и столбцов теряются в Excel 97–2003.

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

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

Что это означает.    Сценарий на листе ссылается на ячейку вне диапазона строк и столбцов Excel 97–2003 (65 536 строк на 256 столбцов) и не будет доступен после сохранения книги в формате более ранней версии Excel.

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

На вкладке Данные в группе Работа с данными выберите Анализ "что если" > Диспетчер сценариев. В поле Сценарии найдите сценарий, который вызывает проблему с совместимостью, и измените ссылку.

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

Что это означает.    Начиная с Excel 2007, вы можете создавать пользовательские международные форматы календарей, такие как еврейский лунный, японский лунный, китайский лунный, календарь эры Сака, китайский зодиакальный, корейский зодиакальный, японский (буддистский) лунный и корейский лунный. Однако эти форматы календарей не поддерживаются в Excel 97–2003.

Что необходимо сделать.    Во избежание потери функциональности необходимо изменить формат календаря на соответствующий языку (или национальным настройкам), который поддерживается в Excel 97–2003.

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

Что это означает.    Начиная с Excel 2007, вы можете использовать календари, отличные от западного, например тайский (буддистский) или арабский (по хиджре). В Excel 97–2003 эти типы календарей могут быть заменены только на григорианский.

Что необходимо сделать.    Во избежание потери функциональности необходимо изменить формат календаря на соответствующий языку (или национальным настройкам), который поддерживается в Excel 97–2003.

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

Что это означает.    Начиная с Excel 2007, общее количество доступных блоков ячеек ограничено доступной памятью. Однако в Excel 97–2003 общее количество доступных блоков ячеек ограничено 64 000 в одном экземпляре Excel.

Блок ячеек включает 16 строк листа. Если все строки листа содержат данные, в этом листе будет 4096 блоков ячеек, и в одном экземпляре Excel можно будет использовать только 16 таких листов (независимо от количества открытых книг в Excel).

Что необходимо сделать.    Чтобы гарантировать, что в книге не превышен предел в 64 000 блоков ячеек и ее можно открыть в Excel 97–2003, после сохранения книги в формате Excel 97–2003 следует работать в Excel 2007 и более поздних версиях в режиме совместимости. В нем Excel отслеживает блоки ячеек в активной книге.

Одна или несколько ячеек книги содержат спарклайны. Спарклайны не будут сохранены.

Что это означает.    В Excel 97–2010 спарклайны не отображаются на листе.

Однако все спарклайны остаются доступными в книге и применяются при повторном открытии книги в Excel 2010 и более поздних версиях.

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

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

Что это означает.    Возможности, которые использовались в этой книге, недоступны в версиях до Excel 2007 (Excel 97–2003), поэтому они будут потеряны.

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

Этот файл изначально содержал функции, не распознанные данной версией приложения Excel. Эти функции не сохраняются при сохранении OpenXML-файла в формате XLSB (и наоборот).

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

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

support.office.com

Отключить F1/F9 в Excel | XLTools – Надстройки Excel, нужные ежедневно

Функциональные клавиши в Excel порой незаменимы. Но всегда велика вероятность промахнуться и вмесит часто используемых кнопок F2 или ESC по ошибке нажать F1. Компьютер начинает долго грузить Справку Excel, а закрыть эту область задач совсем непросто.

Случайное нажатие клавиши F9 не менее досадно. Excel начинает пересчитывать все листы во всех открытых книгах. Нередко это заканчивается «зависанием» программы и попытками восстановления файлов. Некоторые профессионалы Excel прибегают к крайним мерам и физически снимают клавиши F1/F9 с клавиатуры.

С надстройкой «Отключить F1/F9», вы избавите себя от этих проблем:

  • Отключение клавиш F1/F9 для всех книг Excel за секунду
  • При необходимости лёгкое включение обратно
  • Другие горячие клавиши остаются работающими
  • Нет необходимости каждый раз писать макрос VBA

 

Добавить «Отключить F1/F9» в Excel 2016, 2013, 2010, 2007

Подходит для: Microsoft Excel 2016 – 2007, desktop Office 365 (32-бит и 64-бит).

Скачать надстройку XLTools

 

Как работать с надстройкой:

 

Как работает надстройка XLTools «Отключить F1/F9»

  • Отключить клавиши F1 и F9 можно одновременно или по отдельности.
  • В отключенном состоянии, клавиши F1/F9 остаются отключенными для всех открытых книг.
  • Если клавиши не включены обратно, F1/F9 остаются отключенными во всех последующих сессиях Excel.

Другими словами, даже если вы перезапустите Excel или откроете другие книги, F1/F9 останутся отключенными до тех пор, пока вы их не включите.

 

Как отключить F1 (Справка Excel) в Excel

Когда функциональная клавиша F1 отключена, это не дает Справке Excel загружаться:

  1. Откройте панель XLTools > Настройки > Вкладка «Горячие клавиши».
  2. Поставьте флажок в поле «Отключить F1 (Справка Excel)» > Готово.

Чтобы снова включить F1, повторите эти шаги и снимите флажок «Отключить F1 (Справка Excel)».

 

Как отключить F9 (Вычисление всех листов) в Excel

Когда отключена функциональная клавиша F9, это не дает Excel производить пересчёт всех вычислений на всех листах:

  1. Откройте панель XLTools > Настройки > Вкладка «Горячие клавиши».
  2. Поставьте флажок в поле «Отключить F9 (Вычисление всех листов)» > Готово.

Чтобы снова включить F9, повторите эти шаги и снимите флажок «Отключить F9 (Вычисление всех листов)».

 

Влияет ли отключение функциональных клавиш F1 и/или F9 в Excel на другие горячие клавиши

Нет, не влияет. Надстройка «Отключить F1/F9» регулирует исключительно клавиши F1 и F9. Другие горячие клавиши и их сочетания с F1/F9 остаются действующими, напр.:

  • При отключенной F9, можно по-прежнему использовать сочетание Shift + F9 для вычисления только активного листа.
  • При отключенной F1, можно по-прежнему использовать сочетание Ctrl + F1 для отображения/скрытия ленты инструментов.
  • При отключенных F1/F9 в Excel, клавиши F1/F9 остаются работающими в MS Word и других приложениях Microsoft или Windows.
Появились вопросы или предложения? Оставьте комментарий ниже.

xltools.net

Почему не работает формула ВПР (VLOOKUP) в Excel — Решение

Функция ВПР самая популярная и часто используемая функция. Знание данной функции очень поможет вам в вашей работе. Описание работы функции ВПР с примером вы можете посмотреть в специальной статье, а в данном уроке мы рассмотрим примеры, когда функция ВПР не работает или выдает ошибки.

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

Столбец поиска искомого значения не является крайним левым.

Самая распространенная ошибка, когда ВПР не работает. Давайте рассмотрим тот же пример, что мы рассматривали при описании функции ВПР. У нас есть таблица заказов и Прайс лист.

Обратите внимание, что в данном примере таблица Прайс лист отличается. Названия товаров теперь находятся не в первом столбце, а в третьем, после цены товара.

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

Решение

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

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

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

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

3. Можно использовать комбинацию функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), как более гибкую альтернативу для ВПР.

Функция ВПР возвращает ошибку #Н/Д (#N/A)

#Н/Д (#N/A) означает «Нет данных» («not available»). При протягивании формулы ВПР у вас появляется #Н/Д. Вопрос даже не в том, что ВПР не работает, а почему появляются данные значения.

Решение

1. Не закреплен диапазон таблицы

Если при протягивании формулы ВПР у вас первое значение подтянулось а в остальных отобразилось #Н/Д то скорее всего вы не закрепили диапазон таблицы с помощью $ и при протягивании у вас таблица начала съезжать вниз. Еще раз прочитайте внимательно описание функции ВПР с примером.

2. Искомое значение и значение в просматриваемом диапазоне не совпадает

На рисунке выше есть #Н/Д два раза. Один раз напротив товара «Савок» и другой напротив слова «Стол».

В первом случае понятно — товара «Савок» у нас нет в прайс листе, отсюда и ошибка.

Во втором случае непонятно, товар «Стол» у нас есть, но все равно Н/Д#. Как правило ошибка заключается в написании слов и форматах либо в таблице заказов либо на странице Прайс лист.

В нашем случае у нас после слова "Стол " стоит пробел, поэтому данное слово не находится в прайс листе. Для начала проверьте содержимое ячеек на невидимые пробелы. Особенно часто они встречаются в конце строки. Кликните на ячейку и переместитесь в конец формулы. Удалите лишние пробелы, если они есть.

В данном примере можно так же использовать функцию СЖПРОБЕЛЫ

=ВПР(СЖПРОБЕЛЫ(A3);$F$2:$I$16;3;0)

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

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

Еще более редкий случай, это когда в слове может быть вместо русских слов латинские и наоборот. Например A и А это разные буквы разных алфавитов, поэтому и слова будут разными для ВПР. Чтобы проверить, просто сделайте в отдельном столбце ссылку одной ячейки на другую =A4=F6 в нашем примере эта формула вернет «Ложь» так как эти ячейки не равны (в одном слове есть лишний пробел), аналогичная ситуация будет если вместо русской буквы «С» будет английская буква «C».

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

Функция ВПР возвращает ошибку #ЗНАЧ! (#VALUE!)

Обычно Excel сообщает об ошибке #VALUE! (#ЗНАЧ!), когда значение, использованное в формуле, не подходит по типу данных. Если брать функцию ВПР, то обычно стоит рассмотреть две основные причины ошибки #ЗНАЧ!.

1. Искомое значение больше 255 символов.

В функции ВПР есть ограничение на длину искомого значения – оно не должно быть более 255 символов. Если вы столкнулись с такой проблемой, то рекомендуем использовать для этих целей все ту же связку ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH).

2. Неправильно указан полный путь на другую книгу.

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

3. Аргумент номер_столбца в функции ВПР меньше 1

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

Другие важные особенности работы функции ВПР

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

1. ВПР не чувствительна к регистру

Функция ВПР не чувствительна  к регистру и для нее все символы нижнего и верхнего регистра будут одинаковые. То есть, слово «Стол», «СТОЛ» и «стол» для функции ВПР будут одинаковыми.

Если вам необходимо учитывать регистр при использовании ВПР, то используйте другую функцию Excel, наприме (ПРОСМОТР, СУММПРОИЗВ, ИНДЕКС и ПОИСКПОЗ) в сочетании с СОВПАД, которая различает регистр и возвращается ИСТИНУ или ЛОЖЬ при совпадении или не совпадении с искомым значением.

2. ВПР возвращает первое найденное значение

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

3. Работа функции ВПР и добавлении или удалении столбца

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

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

Для решения этой проблемы можно использовать функцию ПОИСКПОЗ для поиска номера столбца. В качестве искомого значения мы можем указать текст «Цена» и искать в строке — шапке таблице Прайс лист. Теперь если добавлять столбцы в таблицу Прайс лист, то функция ПОИСКПОЗ все равно найдет ячейку со словом «Цена» и вернет на номер позиции данной ячейки.

 

sirexcel.ru

Не удается копировать и вставлять данные на листе

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

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

Решение:    Если вам удается вставить большой объем данных, убедитесь в том, что отформатировать ячейку для ячейки в столбце совпадает формат данных, которые вы хотите вставить и вставьте один столбец данных одновременно.

Изменение формата ячейки столбца

  1. Щелкните заголовок столбца (A, B, C и т. д.) для столбца, который вы хотите изменить.

  2. На вкладке Главная щелкните меню Числовой формат.

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

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

Решение:    Вставьте большее количество столбцов и попробуйте вставить его еще раз.

Вставить новые столбцы

  1. Щелкните заголовок столбца (A, B, C и т. д.) в столбце справа от которого требуется добавить столбец.

  2. На вкладке Главная нажмите кнопку Вставить.

    Появится столбец, что вы установили смен вправо, а новый столбец.

  3. Повторите шаг 2, пока не будут вставлены количество столбцов, которые должны.

Причина: Области копирования и области вставки не одного размера и формы.

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

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

  2. На вкладке " Главная " нажмите кнопку Вставить.

Решение:    Выделите диапазон ячеек для области вставки, которая является одного размера и формы как скопированные данные.

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

  2. На вкладке " Главная " нажмите кнопку Вставить.

support.office.com

Восстановление поврежденной книги - Excel

Восстановление поврежденной книги вручную

  1. На вкладке Файл выберите команду Открыть.

  2. Щелкните нужное место, где находится электронную таблицу в Excel 2013 или Excel 2016 и нажмите кнопку Обзор.

  3. В диалоговом окне Открытие выберите поврежденную книгу, которую нужно открыть.

  4. Щелкните стрелку рядом с кнопкой Открыть и выберите команду Открыть и восстановить.

  5. Выполните одно из указанных ниже действий.

    • Чтобы восстановить максимально возможное количество данных книги, нажмите кнопку Восстановить.

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

Восстановление данных из поврежденной книги

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

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

  • Чтобы восстановить данные, когда книга открыта в приложении Excel, выполните одно из перечисленных ниже действий.

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

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

      1. На вкладке Файл выберите команду Открыть.

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

      3. Нажмите кнопку Да, чтобы снова открыть книгу.

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

    • Сохранение книги в формате SYLK (символьной ссылки).    Сохранив книгу в формате SYLK, вы сможете отфильтровать поврежденные элементы. Формат SYLK обычно используется для устранения ошибок принтера.

      Для сохранения книги в формате SYLK сделайте следующее.

      1. Откройте вкладку Файл и выберите команду Сохранить как.

      2. В списке Тип файла выберите пункт SYLK (Symbolic Link), а затем нажмите кнопку Сохранить.

        Примечание: При использовании формата SYLK сохраняется только активный лист книги.

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

      4. Если появилось сообщение о том, что книга может содержать возможности, несовместимые с форматом SYLK, нажмите кнопку Да.

      5. На вкладке Файл выберите команду Открыть.

      6. Выберите сохраненный SLK-файл, а затем нажмите кнопку Открыть.

        Примечание: Чтобы увидеть SLK-файл, вам может понадобиться выбрать вариант Все файлы или Файлы SYLK в списке Тип файлов.

      7. На вкладке Файл выберите команду Сохранить как.

      8. В поле Тип файла выберите Книга Excel.

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

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

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

    • Настройка пересчета вручную в Excel.     Чтобы открыть книгу, попробуйте отключить автоматический пересчет. Книга не будет пересчитываться и поэтому может открыться.

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

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

    • На вкладке Файл нажмите кнопку Создать.

    • В разделе Доступные шаблоны выберите вариант Пустая книга.

      1. На вкладке Файл нажмите кнопку Параметры.

      2. В категории Формулы в группе Параметры вычислений установите переключатель в положение Вручную.

      3. Нажмите кнопку ОК.

      4. На вкладке Файл выберите команду Открыть.

      5. Выберите поврежденную книгу и нажмите кнопку Открыть.

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

      Для этого выполните указанные ниже действия.

      1. На вкладке Файл выберите команду Открыть.

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

      3. Откройте вкладку Файл и выберите пункт Создать.

      4. В разделе Доступные шаблоны выберите вариант Пустая книга.

      5. В ячейке A1 новой книги введите =имя_файла!A1, где имя_файла — это имя поврежденной книги, которое вы скопировали в действии 2, а затем нажмите клавишу ВВОД.

        Совет: Достаточно ввести только имя книги без расширения файла.

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

      7. Если появится диалоговое окно Выбор листа, выберите нужный лист, а затем нажмите кнопку ОК.

      8. Выберите ячейку A1.

      9. На вкладке Главная в группе Буфер обмена выберите Копировать.

      10. Выделите область приблизительно такого же размера (начиная с ячейки A1), что и диапазон ячеек, содержащих данные в поврежденной книге.

      11. На вкладке Главная в группе Буфер обмена нажмите кнопку Вставить.

      12. Пока диапазон ячеек выбран, на вкладке Главная в группе Буфер обмена снова нажмите кнопку Копировать.

      13. На вкладке Главная в группе Буфер обмена щелкните стрелку под кнопкой Вставить, затем в группе Вставить значения выберите Значения.

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

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

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

      1. Введите на листе модуля следующий код макроса:

        Sub GetChartValues() Dim NumberOfRows As Integer Dim X As Object Counter = 2 ' Calculate the number of rows of data. NumberOfRows = UBound(ActiveChart.SeriesCollection(1).Values) Worksheets("ChartData").Cells(1, 1) = "X Values" ' Write x-axis values to worksheet. With Worksheets("ChartData") .Range(.Cells(2, 1), _ .Cells(NumberOfRows + 1, 1)) = _ Application.Transpose(ActiveChart.SeriesCollection(1).XValues) End With ' Loop through all series in the chart and write their values to ' the worksheet. For Each X In ActiveChart.SeriesCollection Worksheets("ChartData").Cells(1, Counter) = X.Name With Worksheets("ChartData") .Range(.Cells(2, Counter), _ .Cells(NumberOfRows + 1, Counter)) = _ Application.Transpose(X.Values) End With Counter = Counter + 1 Next End Sub

      2. Вставьте новый лист в книгу и переименуйте его в ChartData.

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

      4. Примечание: Ее можно внедрить в лист или поместить на отдельный лист диаграммы.

      5. Запустите макрос GetChartValues.

      6. Данные из диаграммы будут помещены на лист ChartData.

Автоматическое сохранение резервной копии книги

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

  1. На вкладке Файл выберите команду Сохранить как.

  2. Щелкните нужное место, где находится электронную таблицу в Excel 2013 или Excel 2016 и нажмите кнопку Обзор.

  3. Щелкните стрелку вниз рядом с элементом Сервис, который находится около кнопки Сохранить, и выберите пункт Общие параметры.

  4. Установите флажок Всегда создавать резервную копию.

Дополнительные сведения о сохранении и восстановлении более ранних версий книги, а также о восстановлении новых книг (которые еще не были сохранены) см. в статье Восстановление файлов Office.

Автоматическое создание файла восстановления через определенные промежутки времени

Функция периодического создания файла для восстановления создает резервную копию книги на случай, если ее оригинал будет случайно удален или поврежден.

  1. На вкладке Файл нажмите кнопку Параметры.

  2. В категории Сохранение в группе Сохранение книг установите флажок Автосохранение каждые и укажите количество минут.

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

  4. Убедитесь, что флажок Отключить автовосстановление только для этой книги снят.

Дополнительные сведения о сохранении и восстановлении более ранних версий книги, а также о восстановлении новых книг (которые еще не были сохранены) см. в статье Восстановление файлов Office.

Восстановление поврежденной книги вручную

  1. Нажмите кнопку Microsoft Office и выберите Открыть.

  2. В диалоговом окне Открытие выберите поврежденную книгу, которую нужно открыть.

  3. Щелкните стрелку рядом с кнопкой Открыть и выберите команду Открыть и восстановить.

  4. Выполните одно из указанных ниже действий.

    • Чтобы восстановить максимально возможное количество данных книги, нажмите кнопку Восстановить.

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

Восстановление данных из поврежденной книги

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

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

  • Чтобы восстановить данные, когда книга открыта в приложении Excel, выполните одно из перечисленных ниже действий.

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

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

      1. Нажмите кнопку Microsoft Office и выберите Открыть.

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

      3. Нажмите кнопку Да, чтобы снова открыть книгу.

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

    • Сохранение книги в формате SYLK (символьной ссылки).    Сохранив книгу в формате SYLK, вы сможете отфильтровать поврежденные элементы. Формат SYLK обычно используется для устранения ошибок принтера.

      Для сохранения книги в формате SYLK сделайте следующее.

      1. Нажмите кнопку Microsoft Office , а затем щелкните стрелку рядом с кнопкой Сохранить как.

      2. В списке Тип файла выберите пункт SYLK (Symbolic Link), а затем нажмите кнопку Сохранить.

        Примечание: При использовании формата SYLK сохраняется только активный лист книги.

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

      4. Если отображается сообщение о том, что книга может содержать возможности, несовместимые с форматом SYLK, нажмите кнопку Да.

      5. Нажмите кнопку Microsoft Office и выберите Сохранить.

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

      7. Нажмите кнопку Microsoft Office и выберите Открыть.

      8. Выберите сохраненный SLK-файл, а затем нажмите кнопку Открыть.

        Примечание: Чтобы увидеть SLK-файл, может понадобиться выбрать пункт Все файлы или Файлы SYLK в списке Тип файлов.

      9. Нажмите кнопку Microsoft Office , а затем щелкните стрелку рядом с кнопкой Сохранить как.

      10. В поле Тип файла выберите Книга Excel.

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

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

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

    • Настройка пересчета вручную в Excel.     Чтобы открыть книгу, попробуйте отключить автоматический пересчет. Книга не будет пересчитываться и поэтому может открыться.

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

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

        1. Нажмите кнопку Microsoft Office  , а затем — Создать.

        2. В разделе Новый пустой щелкните значок Новый документ.

      2. Нажмите кнопку Microsoft Office , а затем — Параметры Excel.

      3. В категории Формулы в группе Вычисления установите переключатель в положение Вручную.

      4. Нажмите кнопку ОК.

      5. Нажмите кнопку Microsoft Office и выберите Открыть.

      6. Выделите поврежденную книгу, а затем нажмите кнопку Открыть.

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

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

      1. Нажмите кнопку Microsoft Office и выберите Открыть.

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

      3. Нажмите кнопку Microsoft Office  , а затем — Создать.

      4. В разделе Новый пустой щелкните значок Новый документ.

      5. В ячейке A1 новой книги введите=имя_файла!A1, где имя_файла — это имя поврежденной книги, а затем нажмите клавишу ВВОД.

        Совет: Необходимо ввести только имя книги — вводить расширение не нужно.

      6. Щелкните книгу, а затем нажмите кнопку ОК.

      7. Если появится диалоговое окно Выбор листа, выберите нужный лист, а затем нажмите кнопку ОК.

      8. Выберите ячейку A1.

      9. На вкладке Главная в группе Буфер обмена нажмите кнопку Буфер обмена.

        Сочетание клавиш: можно также нажать клавиши CTRL+C.

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

      11. На вкладке Главная в группе Буфер обмена нажмите кнопку Вставить.

      12. Пока диапазон ячеек выбран, на вкладке Главная в группе Буфер обмена снова нажмите кнопку Копировать.

      13. На вкладке Главная в группе Буфер обмена щелкните стрелку под кнопкой Вставить и выберите команду Специальная вставка.

      14. В разделе Вставить выберите параметр Значения, а затем нажмите кнопку ОК.

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

    • Открытие поврежденной книги в Microsoft Office Word или Microsoft WordPad.    Если у вас установлен конвертер Microsoft Office Excel, возможно, у вас получится открыть книгу в Word. Если файл откроется в приложении Word, вы сможете восстановить данные. Этот способ нельзя использовать для листов модулей, диалогов, диаграмм и макросов, а также для внедренных диаграмм. Вы не сможете восстановить формулы в ячейках. Восстановить удастся только результаты формул, которые отображаются в ячейках поврежденной книги.

      Книгу Excel также можно открыть в WordPad. Если книга откроется, вы сможете восстановить код Microsoft Visual Basic в модулях и модулях классов. Чтобы найти код, выполните поиск по слову Sub или Function.

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

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

      1. Введите на листе модуля следующий код макроса:

        Введите на листе модуля следующий код макроса:

        Sub GetChartValues() Dim NumberOfRows As Integer Dim X As Object Counter = 2 ' Calculate the number of rows of data. NumberOfRows = UBound(ActiveChart.SeriesCollection(1).Values) Worksheets("ChartData").Cells(1, 1) = "X Values" ' Write x-axis values to worksheet. With Worksheets("ChartData") .Range(.Cells(2, 1), _ .Cells(NumberOfRows + 1, 1)) = _ Application.Transpose(ActiveChart.SeriesCollection(1).XValues) End With ' Loop through all series in the chart and write their values to ' the worksheet. For Each X In ActiveChart.SeriesCollection Worksheets("ChartData").Cells(1, Counter) = X.Name With Worksheets("ChartData") .Range(.Cells(2, Counter), _ .Cells(NumberOfRows + 1, Counter)) = _ Application.Transpose(X.Values) End With Counter = Counter + 1 Next End Sub

      2. Вставьте новый лист в книгу и переименуйте его в ChartData.

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

        Примечание: Эту диаграмму можно внедрить в лист или поместить на отдельный лист диаграммы.

      4. Запустите макрос GetChartValues97.

        Данные из диаграммы будут помещены на лист ChartData.

Автоматическое сохранение резервной копии книги

  1. Нажмите кнопку Microsoft Office , а затем щелкните стрелку рядом с кнопкой Сохранить как.

  2. Нажмите стрелку рядом с кнопкой Сервис, а затем выберите команду Общие параметры.

  3. Установите флажок Всегда создавать резервную копию.

Автоматическое создание файла восстановления через определенные промежутки времени

  1. Нажмите кнопку Microsoft Office , а затем — Параметры Excel.

  2. В категории Сохранить в разделе Сохранять резервные копии книг установите флажок Автосохранение каждые, а затем введите количество минут.

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

  4. Убедитесь, что флажок Отключить автовосстановление только для этой книги снят.

support.office.com