Как восстановить формулы в excel

Формулы сбиваются при использовании файла Excel на разных компьютерах

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

= ‘D:\ПРОЕКТЫ\Папка\заполнение документов\FillDocuments.xla’! Пропись_Число(D12)

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

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

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

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

Расположите файл надстройки FillDocuments.xla на всех компьютерах по одному пути (и убедитесь, что имя файла надстройки везде одинаково)
Например, можно расположить файл в корне диска С, чтобы путь к файлу был C:\FillDocuments.xla
Или можно разместить в общей сетевой папке, по пути типа \\server\надстройки\FillDocuments.xla

ВАЖНО: для перемещения файла надстройки, нужно выполнить следующее:

• отключить автозапуск программы в О ПРОГРАММЕ — ОПЦИИ
• закрыть Excel (или закрыть надстройку FillDocuments)
• переместить файл в новое расположение
• запустить надстройку из нового места
• включить автозапуск программы в О ПРОГРАММЕ — ОПЦИИ (если это нужно)

После этого, выполните описанное в одном из других способов, для восстановления формул.

Способ 2: вручную исправляем ссылки, через поиск и замену

Копируем из ячейки «лишний» путь к файлу, начиная с кавычки, и заканчивая восклицательным знаком:
‘D:\ПРОЕКТЫ\Папка\заполнение документов\FillDocuments.xla’!

Нажимаем Ctrl + H для вывода диалогового окна «Найти и заменить», в поле «Найти» вставляем скопированный путь,
а поле «Заменить на» оставляем пустым.
Осталось нажать кнопку Заменить все, и формулы приведутся к исходному варианту написания: =Пропись_Число(D12)

Способ 3: исправляем ссылки через меню надстройки FillDocuments

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

Нажимаем кнопку Настройки, идём на вкладку «Дополнительно», и включаем там опцию «Отображать подменю ДОПОЛНИТЕЛЬНО на панели инструментов программы»:

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

Способ 4: исправляем ссылки на файл штатными средствами Excel

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

Нажимаем кнопку Изменить, и в диалоговом окне указываем, где теперь расположен файл надстройки FillDocuments.xla

После того, как файл выбран, в графе Состояние будет написано ОК:

Теперь отстаётся запустить надстройку FillDocuments (если она до этого не была запущена), и Excel автоматически внесёт изменения в формулы во всём файле.

• Добавить комментарий
• 10987 просмотров

17 функций и 6 хитростей Excel, которые помогут упростить работу

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

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

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

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

Таблица 1. 17 полезных функций Excel

Позволяет складывать отдельные значения, диапазоны ячеек, ссылки на ячейки или данные всех этих трёх видов.

=СУММ(Число 1;Число 2;. Число n)

=СУММ(А1;B1;C1) — сумма значений в ячейках

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

=ПРОИЗВЕД(Число 1; Число 2;. ;Число n)

=ПРОИЗВЕД(А1;B1;C1) — произведение значений в ячейках

Считает среднее арифметическое числовых значений.

=СРЗНАЧ(Число 1; Число 2;. ;Число n)

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

У функции возможны два результата (ИСТИНА и ЛОЖЬ).

Функцию ЕСЛИ можно настроить сразу по нескольким критериям.

=ЕСЛИ(логическое выражение; [функция если истина]; [функция если ложь])

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

Суммирует значения, удовлетворяющие заданным условиям (например, все затраты из одной категории).

=СУММЕСЛИ(ячейки которые нужно проверить на условие; условие; какие ячейки складывать при удовлетворении условию)

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

= СЧЕТЕСЛИ(ячейки которые надо подсчитывать, критерий по которым ячейку принимать в расчёт)

Рассчитывает условное среднее значение.

=СРЗНАЧЕСЛИ(ячейки которые нужно проверить на условие; условие; для каких ячеек рассчитывать среднее при удовлетворении условию)

Возвращает наименьшее значение в списке аргументов.

=МИН(Число 1; Число 2;. ;Число n)

Возвращает максимальное значение в списке аргументов (функция обратная МИН).

=МАКС(Число 1; Число 2;. ;Число n)

Используется для получения минимального значения из заданного диапазона ячеек (возвращает k-ое наименьшее значение из массива данных).

В ячейках А1;A5 находятся числа 1;3;6;5;10.

=НАИМЕНЬШИЙ (A1;A5) при разных k:

k=1; результат =1 (первое наименьшее значение)

k=2; результат=2 (второе наименьшее значение)

k=3; результат=5 (третье наименьшее значение)

Позволяет выбрать значение по его относительному местоположению (возвращает k-ое по величине значение из множества данных).

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

В ячейках А1;A5 находятся числа 1;3;6;5;10.

= НАИБОЛЬШИЙ (A1;A5) при разных k:

k=1; результат = 10 (первое наибольшее значение)

k=2; результат = 6 (второе наибольшее значение)

k=3; результат = 5 (третье наибольшее значение)

Позволяет избавиться от всех лишних пробелов в заданных ячейках (кроме уместных одинарных).

Возвращает заданное количество знаков из указанной строки слева.

=ЛЕВСИМВ(адрес ячейки; количество знаков)

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

=ПРАВСИМВ(адрес ячейки; количество знаков)

Возвращает знаки из текстовой строки, начиная с указанной позиции.

=ПСТР(адрес ячейки; начальное число; число знаков)

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

Например, по номеру товара можно найти его цену или по идентификатору найти имя сотрудника.

=ВПР(искомое значение; таблица; номер столбца; тип совпадения)

Тип совпадения может быть приблизительным или точным. Для передачи точного значения в формуле нужно прописать 0 или ЛОЖЬ, для передачи приблизительных значений указывается 1 или ИСТИНА

Конкатенация

Позволяет объединить в одной ячейке данные из двух и более ячеек.

Самый простой способ — вставить между адресами ячеек амперсанд (&).

=ячейка 1&ячейка 2. &ячейка n (=A1&A2&A3)

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

1. Чем полезно умное форматирование

Чтобы систематизировать данные, привести таблицы в понятный и презентабельный вид можно использовать условное форматирование (рисунок 1). Это целый массив способов обработки данных.

Методы основаны на цветном выделении ячеек в зависимости от различных критериев:

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

Благодаря динамике, данные корректируются при каждом изменении.

Рисунок 1. Условное форматирование

2. Как создать умную таблицу

Список данных в Excel можно преобразовать в умную таблицу (рисунок 2), у которой есть масса полезных функций:

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

Рисунок 2. Умная таблица

3. Как визуализировать данные с помощью спарклайнов

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

Чтобы создать спарклайн, нажмите «Вставка» → группа «Спарклайны» → кнопка «График» или «Гистограмма» (рисунок 3).

Рисунок 3. Спарклайны

4. Как перенести большую формулу

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

Замените знак «=» на «!». Формула превратится в символьную строку, которая перемещается без изменений. После того как формула полностью вписана в нужную ячейку, поменяйте знак обратно на «=».

5. Как ускорить и упростить работу с помощью Power Query

Представьте, что вам нужно составлять еженедельный отчёт. Вы готовите таблицы в Excel. А исходные вы получаете в виде CSV-файлов. Нужно каждый раз искать в них только необходимую вам информацию, вставлять данные в Excel, обновляя сводные данные и графики. Всё можно сделать намного проще, воспользовавшись Power Query.

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

Надстройка умеет собирать данные из фалов почти 40 различных форматов (например, TXT, XLSX, HTML, CSV, JSON, XML). Помогает менять регистр на правильный, приводить цифры к числовому формату, заполнять пробелы, исправлять заголовки таблиц, разделять текстовые фрагменты на столбцы и склеивать их снова в единый текст, удаляет пустые столбцы и строки, выполняет многие другие полезные операции.

Power Query представлена в двух вариантах:

• отдельным модулем, доступным для скачивания с официального сайта Microsoft (для Excel 2010-13).
• как сервисная функция в составе редактора (рисунок 4).

В большинстве последних версий Excel надстройка находится на вкладке «Данные» → Получить и преобразовать.

Рисунок 4. Power Query

6. Как восстановить несохранённые файлы

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

• Excel 2010: «Файл» → «Последние» и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги».
• Excel 2013: «Файл» → «Сведения» → «Управление версиями» → «Восстановить несохранённые книги».
• Для последующих версий Excel: «Файл» → «Сведения» → «Управление книгой».

Здесь вы найдёте временные копии созданных, изменённых, но несохраненных книг.

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

• Как бизнесу работать в условиях санкций
• Судебная практика по 223-ФЗ: заказчик должен учитывать возможность участников подать заявку в срок
• Образовательные технологии: семь трендов, на которые стоит обратить внимание

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

Вы сможете выбрать только актуальные для вас темы.

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

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

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

Для чего предназначена строка формул в Excel?

Microsoft Excel – одна из самых полезных программ, которая позволяет пользователю выполнять больше 400 математических операций. Все они сгруппированы в 9 категорий:

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

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

Вводя в нее знак «=», вы словно «активируете» строку и говорите программе, что собираетесь ввести какие-то данные, формулы.

Как ввести формулу в строку?

Формулы можно вводить в ячейки вручную или с помощью строки формул. Записать формулу в ячейку, нужно начинать ее со знака «=». К примеру, нам необходимо ввести данные в нашу ячейку А1. Для этого выделяем ее, ставим знак «=» и вводим данные. Строка «придет в рабочее состояние» сама собой. На примере мы взяли ту же ячейку «А1», ввели знак «=» и нажали ввод, чтобы подсчитать сумму двух чисел.

Можно использовать мастер функций (кнопка fx):

1. Активируем необходимую ячейку и переходим к строке формул, находим там значок «fx», нажимаем.
2. Сразу же он предлагает выбрать функцию, которая необходима пользователю. Выбираем нужную опцию, нажимаем «ОК».
3. Дальше в диалоговом окне вводим значения в параметрах выбранной функции.

Что включает строка редактора формул Excel? На практике в данное поле можно:

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

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

• «#ССЫЛКА!». Говорит о том, что вы указали неправильную ссылку на одну ячейку или же на диапазон ячеек;
• «#ИМЯ?». Проверьте, верно ли вы ввели адрес ячейки и название функции;
• «#ДЕЛ/0!». Говорит о запрете деления на 0. Скорее всего, вы ввели в строку формул ячейку с «нулевым» значением;
• «#ЧИСЛО!». Свидетельствует о том, что значение аргумента функции уже не соответствует допустимой величине;
• «##########». Ширины вашей ячейки не хватает для того, чтобы отобразить полученное число. Вам необходимо расширить ее.

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

Что означает знак $ в строке формул Excel?

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

Знак «$» используется для того, чтобы оставить адреса на ссылки в параметрах формулы неизменными при ее копировании или заполнении. Абсолютная ссылка прописывается с двумя знаками «$»: перед буквой (заголовок столбца) и перед цифрой (заголовок строки). Вот так выглядит абсолютная ссылка: =$С$1.

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

Но если формула будет, смещается относительно столбцов (по горизонтали), то ссылка будет изменяться, так как она смешанная.

Использование смешанных ссылок позволяет вам варьировать формулы и значения.

Примечание. Чтобы каждый раз не переключать раскладку в поисках знака «$», при вводе адреса воспользуйтесь клавишей F4. Она служит переключателем типов ссылок. Если ее нажимать периодически, то символ «$» проставляется, автоматически делая ссылки абсолютными или смешанными.

Пропала строка формул в Excel

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

1. Выберите на панели вкладку под названием «Вид» и нажать на нее.
2. Проверьте, стоит ли галочка возле пункта «Строка формул». Если ее нет, ставим.

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

Еще один вариант. Переходим в настройки «Файл»-«Параметры»-«Дополнительно». В правом списке настроек находим раздел «Экран» там же устанавливаем галочку напротив опции «Показывать строку формул».

Большая строка формул

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

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

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Как восстановить формулы в excel

Доброго времени суток

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

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

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

Доброго времени суток

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

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

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

К сообщению приложен файл: 2821135.xlsx (31.9 Kb)
Сообщение отредактировал Евгений1990 — Воскресенье, 11.10.2020, 10:53

Сообщение Доброго времени суток

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

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

По примеру: При выставлении в ячейку D19(F19 и т.д.) отработанных часов заполняются необходимые значения в
других ячейках, в т.ч. и на следующий по таблице день, Предугадать какие ячейки будут подвержены изменениям невозможно. Ежемесячно формулы должны восстанавливаться. Но поскольку использует их человек, мало знакомый с excel — нужно чтоб они восстанавливались без моего участия. В лучшем из вариантов после удаления внесенных значений Автор — Евгений1990
Дата добавления — 10.10.2020 в 18:31