The Riemann zeta function $\zeta(s)$ at complex $s$ has the statistical physics interpretation of a partition function at complex temperature. This has no direct physical meaning in general, but for certain models it does. A notable example is the Ising model, where the real and imaginary temperature axes are related by a transformation from an hexagonal to a triangular lattice.
Quite generally, the zeroes of the partition function in the complex plane fall on lines rather than in areas. For ferromagnetic models this is the content of the Yang-Lee theorem. It is therefore natural to expect the Riemann hypothesis to hold, although the Yang-Lee theorem does not cover this case.
An overview of the older literature on complex temperature partition functions is:
"Location of zeros in the complex temperature plane: Absence of Lee-Yang theorem", W. van Saarloos and D. A Kurtze, J. Phys. A: Math. Gen. 17 (1984) 1301-1311.
A more recent paper is
"Complex-temperature partition function zeros of the Potts model on the honeycomb and kagome ́ lattices", H. Feldmann, R. Shrock, and S.-H. Tsai, Phys. Rev. E 57, 1335 (1998).
There are many more papers, it is a quite active field of study.
A very recent paper is http://arxiv.org/pdf/1110.0942
The zeta function of a variety $X$ over a finite field is a priori defined to be a point counting function, i.e. it is the following product over the closed points of $X$ (thought of as a scheme):
$$\zeta_X(s) = \prod_{x}(1 - | \kappa(x)|^{-s})^{-1},$$
where $\kappa(x)$ is the residue field of $x$ and $|\kappa(x)|$ denotes its order. (This is motivated by analogy with the Riemann zeta function, which is what we get if we apply the same definition with $X$ replaced by Spec $\mathbb Z$.)
Now this will be a Dirichlet series involving only powers of $p^{-s}$ (if $p$ is the char. of the finite field), and so replacing $p^{-s}$ by $T$, we obtain a power series in $T$, whose
log can be reinterpreted in the usual way as a generating function counting the number of points of $X$ with values in the various extensions of $\mathbb F_p$.
Now one can count these points by the Lefschetz fixed point formula (applied to the $\ell$-adic cohomology), and this gives the alternating product of char. polys. of Frobenius that you write down in your question.
Of course, one could write down their product, rather than their alternating product, but the resulting power series would not have any particular interpretation; in particular, it wouldn't be related to counting points of $X$ in the same way that the zeta function is.
Milne's definition of the $\zeta$-function directly in terms of $\ell$-adic cohomology is to some extent putting the cart before the horse; as Stopple notes, it is a reasonable definition only because of the back story about counting points and so on.
Nevertheless, if you want to take the definition in terms of cohomology as the basic one, then you can ask yourself: how should you define such a quantity if you want it to behave well under chopping up varieties (which is what motives essentially are --- pieces of varieties cut out by correspondences).
The basic quantity that is defined in terms of cohomology and which is additive with respect to cutting up spaces is the Euler characteristic. And for this additivity to hold, it is crucial that involve an alternating sum, with the sign being dictated by the cohomogical degree. The reason is that the behaviour of cohomology under chopping up and/or gluing is given by the excision and Mayer--Vietoris long exact sequences, and it is the alternating sum of the dimensions which is additive in exact sequences.
Viewed cohomologically, the zeta function is like an enhanced, multiplicative version of the Euler characteristic, and like the Euler characteristic, for it to be multiplicative with respect to cutting up varieties, we must form it via an alternating product.
In conclusion: I think that the "deep reason" that you are looking for is the yoga of Euler characteristics.
Best Answer
It was a classical problem going back to Mengoli to find a closed expression for the sum of inverse squares. This was solved by Euler, who saw more generally how to evaluate $\zeta(2k)$ at the positive even integers. Later, Euler "computed" the values of $\zeta(s)$ at negative integers as well and conjectured the functional equation of the zeta function. Euler also saw the connection with prime numbers and used the Euler factorization for estimating the number of primes up to $x$.
Most of Euler's results were made rigorous by Dirichlet (his proof of the infinitude of primes in arithmetic progression was built on Euler's results) and Riemann (who interpreted $\zeta(s)$ as a function on the complex plane, proved the functional equation, and indicated how the number of primes is connected with zeroes of the zeta function). There are many more names that should be mentioned (Kummer, Dedekind, Mertens, Landau, ...).
In any case, it was Euler who stumbled upon the zeta function more or less by accident, and he already recognized its importance.